1 Reply Latest reply on Feb 25, 2015 5:15 PM by FentonJones

    How to import a folder of multiple excel files into a database?

    NB

      Title

      How to import a folder of multiple excel files into a database?

      Post

      I have hundreds of excel files with data and my FileMaker database with corresponding fields that I have been importing one by one using Import record ---> import file, selecting the individual excel file and match the names up with fields on the database records and import the excel file data one by one. This is time consuming so I am looking for a way to import a folder of excel files at once and do the same thing automated and much faster. As far as I know, FileMaker pro can only import folders of .txt and image files. Does anyone know how to import folders of muliple excel files? I think you can write a script within FileMaker to loop through files and import them, but I am having trouble with this because I cannot seem to set my variables and paths right. I couldn't figure it out using free Mac plug-ins either (unless anyone can assist with that). Any help is appreciated, thank you!

        • 1. Re: How to import a folder of multiple excel files into a database?
          FentonJones

          FileMaker can Import an Excel file pretty well. It can Import normally, or else via a "Group" (if you have one "defined" in the Excel file/sheet (it can Import from different sheets,  but each likely would need its own Import, as their columns would likely be different).

          The Import can handle importing multiple files from a known Folder. FileMaker can tell where its own folder is, especially if this is all happening on a local computer; my example file expects that ( I do not know how it would work on a server; perhaps OK, but I cannot test right now). You use two lines in the Import, to specify what file to Import from. First, give it a known file, to set up the Import columns (or group).

          Then also give the Import (above) another possible file to Import from. [ FileMaker can handle several possible file paths; it will choose the 1st one in the list which it sees is there. Hence using a fixed file, in order to setup the Import, and a Variable path also works. In my method, each file is moved after the Import, so solve any "duplicate" imports. I feel the "move" is a good idea anyway, so you know which have been imported. [ Another method is to remove the file you used to "setup the Import"; then there is no "known file"; but then you'd need to get/rename one, if you needed to change the Import fields, etc. (as the original setup file would not be there.]

          That second file path to Import from would be set via a Variable; which you'd set, via the FileMaker script step, before the above happens. A Perform AppleScript would get a list of all Excel files in the known Folder. So you'd just need to pull them out, one at a time, to set the Variable, and just adding 1 to the number of lines. It would also know when to stop after the last.

          Hopefully you can figure this out from the file. A critical layout is one for a critical table (named "Globals" in my FileMaker file). which has only 1 record. It is used to hold calculation fields and some text fields. The calculations figure out what Folder you've got the FileMaker file in. It then expects a folder named "Excel_files" in the SAME folder, as well as one named "Done" (which is where it "moves" each file to after its Import). [ An AS could create the "Done"; but you've got to create the "Excel_files" for the Excel files; so create the "Done" next to it.]

          The Globals table's "AS" layout has the fields needed. AppleScript is easier to write its commands if it can just go to a layout where required fields are. In this case, it makes sense to do so. Useful to look at also.

          This file is also using Perform AppleScript, to do a couple of things. 

          https://dl.dropboxusercontent.com/u/84482636/Import_Excel_12.zip


          P.S. I cannot really "read" very well. So hopefully the above makes some sense. There are a few pieces, but each is fairly simple (by itself :-)

          [ The data in my Excel files are NOT real. They were especially made, to be used in testing or showing. Get your own (some free) from this source. (He also has many Custorm Functions {1628 !}, from many developers.) 

          http://www.briandunning.com/sample-data/