When importing data, it is useful to use variables to reference the file you are importing instead of using a full path that needs to be maintained. This allows you to better maintain your solution, and also the flexibility of referencing a dynamically named file in your scripted import.
One of the disadvantages of this is that you lose the ability to line up fields in the import dialog. Of course, since the variable is set only when you run the script, FileMaker has no knowledge of where the file is in order to line up the fields.
Here is a tip to have your cake and eat it too.
Just for example, let’s say we are importing a comma separated value file. Create a copy (perhaps populate with sample data) of the file you want to import and name it something like “Sample_Import_Mapping.csv” and put it somewhere that makes sense – somewhere that you keep project notes or other documentation.
Click the button to select the file to import. After the variable used to reference your file path, click the button to add a file…then select your copy of the file to import with sample data. It will add another line to this dialog…so when the first file reference isn’t found, the next listed reference will be found.
Now when you click the button to specify the import order, you will get the proper list of fields to line up. You even get the option to use the first row as field names for CSV files.
When your script is running, the variable will be populated and will import from there. If you really want to be safe, you can put these in a folder and change the name after you do the mapping so FileMaker no longer has a valid path to your sample. Then you can error trap for the variable path being valid.
So you effectively have a way to be able to line up fields as you like, but still reference the import file with a variable.
But wait there’s more!
BONUS: When working with Excel files, have the option of importing from a specific sheet within the workbook. You will notice that if you manually select a file, you will be prompted to select the sheet if there is more than one sheet in the excel file. That second parameter, the one for sheet, is not programable with a variable. You have to select if from a valid excel file…even though you can see it being set from the script.
Using the above method, you can add a secondary path to a copy of the spreadsheet used for mapping AND select the correct sheet. We will save our file above as an xlsx document and add a second sheet.
Now we adjust the file references and change the file type…
When we exit this dialog, it will ask us what sheet to import from!
Notice in the script, the second parameter…specifying the worksheet to import from.
Save your script, optionally move/rename your mapping sample and run the script. Your data will get imported from the excel file (based on your variable) from the correct worksheet!
Nice? Please leave a comment if this helps you or if you have other suggestions.
Nice trick! Thank you so much! This got me around a jam with the automated importing of XLSX files on a daily basis where the name of the file changes based upon the date.
I’ve known the trick to get the field mapping lined up using a variable filename for years, but your bonus tip to get the script to import the desired worksheet was very helpful. Thanks for sharing!