3 Replies Latest reply on Jun 6, 2015 12:12 PM by FentonJones

    importing from more than 1 Excell at once!

    HelderSantos_1

      Title

      importing from more than 1 Excell at once!

      Post

      Hi,
      i am facing new task, customer has more than 50 site, each site sends 3 times a day excells reports. He import one by one(hard).
      My question Is: is there any way that he can run a script to import all files running at once a script?

        • 1. Re: importing from more than 1 Excell at once!
          FentonJones

          An Import is a single script step. So, yes, a single script could run several Import script steps. But there's several other questions involved, when you want to Import from multiple files.

           

          1. Do all the Excel files have the same "structure", i.e., columns names, etc.? (number of rows likely does not matter).

           

          2. Do all the Excel files have the same file name?

           

          If 1. above is true, but 2. is false (and the files are in the same folder/directory) it may be possible to do them all, using only 1 Import script step, by setting a Variable into the name of each, within a Loop, then use the Variable as the file name in the Import.

           

          You would however need the name of each file. How would you know what the file names are? What operating system, Mac or Windows? I am mostly a Mac person, and know some AppleScript (which can easily get the file names from a folder). It is likely not that hard to do on Windows, though you might want a plug-in to do so.

           

          If both 1. and 2. above is true, then where are the files? It could still be done, if you knew where. But it would involve either "multiple Import script steps", or "moving each file, one at a time, to a known folder/directory, then moving it back, to where it came from".

           

          If 1. is false, i.e., the Excel files do not even have the same structure, then it would require multiple Import steps, each set up properly for each different structure; and, going to a different Layout first, if they're not even for the same table.

           

          Hopefully some of the above makes sense. Basically, we (any of us) need some better info to really answer.

           

          [ There are also questions about what to do with the files afterwards, and/or how to avoid importing the same data more than once. It helps if there is a unique TimeStamp (or similar), as well as knowing where it came from; or something. If would be best if each Excel row/line has an absolutely unique value; then the Import would use that to stop an Import; but this does not seem likely (from my experience). 

           

          It is also possible, if you're on a Mac, or using a plug-in, to read the name of a file before running an Import, which could have such a unique data/time and a name of where it came from. If you stored these values in your FileMaker database, the script could look, before running an Import on that file (again). 

           

          My preferred method is to put files into an "import" folder/directory, then moving it out to a "done" folder/directory". That solves much of the "duplicate" problem; but people may still send you the same file twice :-|

          • 2. Re: importing from more than 1 Excell at once!
            HelderSantos_1

             Dear Fenton,
            answering your questions:
            1 - Yes, all Excel files have the same "structure".
            2 - No, each file name have unique name.

            Running on Windows, there is a shared folder on network, where all sites drops them file. The file have old and new data. I can suggest to change the structure of the excel file if needed.

            • 3. Re: importing from more than 1 Excell at once!
              FentonJones

              First, I recommend having FileMaker Pro Advanced, in order to be able to test a script for this importing. You will need to get a list of files, then go thru them one at a time (using a Loop), doing an Import, then "moving" that one into another folder/directory, then continuing to the next file. I don't think anyone could get this to work correctly the 1st time. FileMaker Pro Advanced would at least let you see where it went wrong sooner (using the folder/file paths).

              Secondly, I'm afraid I no longer know what/how to do this on Windows.* You would likely need a plug-in; I believe a free one is available. You need two functions:
              1. Get the full file paths of files in a folder/directory
              2. Move a specific file into another folder/directory

              If you are reading this post, and are on a Mac, you could look at this, which has a small file, and the folders I used, and shows the Perform AppleScript (PA) script steps (2) used. This file will ONLY run on a Mac. You can however read view it, and read most of the script steps. It is on this post:
              How to import a folder of multiple excel files into a database?
              of just the file (also) here:
              https://dl.dropboxusercontent.com/u/84482636/Import_Excel_12.zip

              There a few script steps used which were partly for the PA used, they used PA [ Native AppleScript ], which is simple to write, but fussy about having a record and any specified field on the current layout. You will likely be able to avoid some of that, if using a plug-in. But those steps don't hurt anything; I think you'll guess which they are. This is the script, without the AS steps running; just comments saying where.

              Enter Browse Mode
              Go to Layout [ “AS” (Globals) ]
              If [ Get(TotalRecordCount) = 0 ]
                  # Current Record requires at least 1 record.
                  New Record/Request
              End If
              If [ Get (FoundCount) = 0 ]
                  Show All Records
              End If
              #
              Freeze Window
              Set Field [ Globals::theFiles; "" ]
              Set Field [ Globals::theFile; "" ]
              # Get the Files of the Folder, put into a global field, using Plug-in [ was: Perform AppleScript ]
              Commit Records/Requests
              #
              If [ not IsEmpty(Globals::theFiles) ]
                  Loop
                      Set Field [ Globals::theFile; GetValue ( Globals::theFiles; 1 ) ]
                      Set Field [ Globals::theFiles; RightValues ( Globals::theFiles; ValueCount (Globals::theFiles) - 1) ]
                      Set Variable [ $filepath; Value:"filemac:/" & Substitute ( Globals::theFile; ":"; "/" ) ]  {"filewin:" for you}
                      #
                      Go to Layout [ “People” (People) ]
                      Show All Records  [ only required if the Import is using "matching" (so it can see all records to match against) ]
                      # Import, with Add New
                      Import Records [ "Import.xlsx"; Worksheet: "Sheet1"; Target: “People”; Method: Add; Character Set: “UTF-8”; ] [ No dialog; Data contains column names ]
                      Go to Layout [ “AS” (Globals) ]
                      #
                      # Move File to "Done" Folder [ was: Perform AppleScript*]
                      Exit Loop If [ IsEmpty(Globals::theFiles) ]
                  End Loop
              Else
                  Show Custom Dialog [ Title: "Message"; Message: "There were no files in the Excel_Files folder. They may be done already."; Default Button: “OK”, Commit: “Yes” ]
              End If
              Go to Layout [ original layout ]

              The "Import", Specify data source: "File…" lets you say where the file is. It can allow MORE THAN ONE, as another line. Such as:
              file:Excel_Files/Import.xlsx
              $filepath

              The 1st line is using a (local in my case) path; file is "Import.xlsx", much like any of Excel files you'd want to Import. It is used only to "setup" the Import; as you cannot set up the fields, etc., unless the file exists. It does not matter after that, as it is the Variable, in the 2nd line, which the script runs one, if the 1st is missing [ which it will be, as you don't want to Import it again ]. But you would need to rename/put that original file into its original place/path if you ever want to redo the fields imported, etc..

               

              P.S. I did not use "Matching" on the Import (as I didn't have a unique field to match in those small Excel files). It is possible to do "matching" if you have such in your Excel files. Though it would not be necessary if you "move" each imported file into a "Done" folder/directory. You would need access to such a folder, likely at the same level as the files' folder.


              * I used to know more, about Windows, and much else. I had a stroke 4 years ago, which caused some brain damage, mostly to do with words, reading especially. So I removed extraneous data from my computer; Windows was one of them. It seemed one OS was enough to deal with :-|