Is it the same set of fields for each import or do you need to specify different fields each time that you import?
Import records can preserve the field mapping so that the fields imported are the same each time that the script runs. The trick, when setting up the script to use a file reference that includes variables is to set up TWO file references. Keep the one that you have as line 1, but then use Add File to add a reference to a file for line two. You'll only use this reference while setting up the script step. When you run the script, the reference on line 1 will be used as long as it's a valid file reference. After you have the script set up and working correctly, remove or rename the file referenced in line 2 (the actual file, don't modify your script), to make sure that it can't be imported from by accident.
For more on $Path variables, see this thread: Exploring the use of a $Path Variable in Scripts
Ahhh! Awesome. Thanks.
So yes, the columns are the same each day with each import. I just noticed that it is not really the fields on the left when doing the import field options but rather the column header from the Excel file.
Uh-oh. Spoke to soon. I'm going to download the file you linked but is there a short answer to how do I get the Username of the computer rather than the Filemaker username? My Get(Username) gets the filemaker username.
Not that I know of, But the Get ( DesktopPath ) and Get ( DocumentsPath ) functions will produce the complete paths to those folders.
The names that you need will be included in that text so you can parse out the portion that you need--including both account user name and system drive letter.
Ah. Nice. Good thinking. I'll just take "desktop/" from Get(DesktopPath).
Okay... ran into a problem with the script. Please feel free to suggest a new subject/post.
My XLS file has multiple tabs. When I do what we discuss, it imports the first tab. Any suggestions?
And what result do you need for the import? To import from all tabs or just a different tab?
Each "tab" is a "worksheet" and you can specify the worksheet from which you want to import the data.
Also, if you can use Excel to create a "named range" to specify the data that you want to import, you can specify this as one of the options for importing from Excel.
Each report (XLS file) has 3-6 tabs. Column headers are all the same but tab one is Fiscal Month, tab two Fiscal week, tab three is fiscal date. There are some additional tabs in some reports but the one that I need is the third tab.
It previously worked when I was importing from only my user folder. At that time, it gave me an option on which tab/worksheet to use. Now it does not choose the correct worksheet. While I have filemac:$path/Downloads/filename.xls AND file:../Downloads/filename.xls, it allows to choose a worksheet. When I only have filemac:$path/Downloads/filename.xls, it does not allow to add a worksheet.
EDIT: If both files are present, the "update matching" does not appear to function properly. End up with duplicates.
While I have filemac:$path/Downloads/filename.xls AND file:../Downloads/filename.xls, it allows to choose a worksheet. When I only have filemac:$path/Downloads/filename.xls, it does not allow to add a worksheet.
Please explain what you mean by this. Are you removing the second file reference from the script step or moving/renaming the file?
If both files are present, the "update matching" does not appear to function properly. End up with duplicates.
Sorry, but that makes no sense. the script step will only open one of the two files. The presence of the second file won't affect how data is imported into the first file.
But "Update matching" has to be set up correctly and you need the right records in your target table's found set or you'll get duplicates. This usually means that your script needs to do a Show All Records before importing to make sure that your target table has all possible records in the found set so that the "Update matching records in FOUND SET" option can work correctly.
I think you hit the nail on the head with the "Show All Records" script comment. I will try this.