Can I Import Multiple Excel Files with One Script?

August 22, 200322 yr

Hi!!

I need to import 9 Excel Timesheet Files Weekly into FM to do a cleanup, validation and spit out reports on delinquent employees. I've got the meat and potatoes of the program done, but I was wondering if I had to import the files manually each week.

As far as I can see, the Import script only allows you to choose one file at a time. I have a folder named "Timesheets" that holds the Excel files named with the Employee Init and Week No. (AGD-0334, DAT-0334, etc)

The week numbers will change obviously each week. Can't I tell FMP to import all files in that folder at one time, or at least all files with the 'Week Number' field in the file name?

None of my reference books go beyond single-file imports. Thanks in advance.

August 22, 200322 yr

You don't say which operating system, so I'll assume Windows. You want to get the Troi File Plug-in.

It has a "list folder" command to get the names of files in a folder. It can also rename and move files.

The basic technique is to import a "fixed" named file (one of your Excel files, with a special name), to set up a FileMaker Import script step with the correct order and file path. Then move that file out of the "files to import" folder.

Using the plug-in's commands, you set the names of the files in the folder into a global field. Then parse out the first line into another global, put it together in a calculation with the folder path, to make a complete file path. (Then remove the line from the "file names" global field now, or before doing the next one.)

This path will now match what you set up in the Import step, so it will work.

You loop through the files, one at a time, renaming each to the fixed name, importing it, then changing its name back to what it was (or anything else). You can also move it at this time into a "Done" folder, if you want.