Import via a variable

[jdu98a]

I have a script that imports records from an Excel file via a variable based on the contents of a container field. I am doing it this way because after the import I use other information about the file name and path for other things. Having the file reference held in a container field allows me to just parse whatever text information I need from it without the user needing to go find the file again.

Ex: ContainerField (global) - contains a reference only to file called "contacts.xls"

Variable "$$File" is set to:

Middle ( ContainerField ; Position ( ContainerField ; Case ( Abs ( Get ( SystemPlatform ) ) = 1; "filemac:/"; Abs ( Get ( SystemPlatform ) ) = 2; "filewin:/"; "" ) ; 1 ; 1 ); 10000 )

This returns: (in the case of a windows machine)

filewin:/C:/Documents and Settings/User/My Documents/contacts.xls

Then an Import script step imports "$$File"

This works perfectly some of the time. However, it seems to only work for certain files. Most of the time I get Error 715: "Specified Excel worksheet or named range is missing." Any ideas?

[bcooney]

I also use this technique. However, I set my $importpath =

GetValue ( GetAsText ( Container); ValueCount ( Container) ).

Perhaps calculating the path for Win vs Mac isn't necessary, and proves to be too specific, as the full path is usually the second value of the container.

Also, not that this matters, but why $$var and not $var?

Is the path valid when it fails? Permission issue?

[jdu98a]

Well, I like how you got the filepath better than my method. It is much simpler. However, it did not fix the issue.

I learned something new though. The import only seems to fail when I try to do it with no dialog. There are three difference file formats that the user needs to be able to import with this one script. Based on which of these files is being imported there are three different field orders that the database uses to get the data in appropriate fields. When I do the imports 'with' dialog I can see that the database is choosing the correct import and everything lines up nicely. But, when I turn off dialog I get the error 715. It would seem that if the wrong import order was being used I would simply get records with data scrambled up in the wrong fields. I'm at a loss as to what is going on here.

Also, I am using $$var just to leave my options open. I may want to call upon the variable again in another script such as a simple "reimport" script.

[Fenton]

I recently had the task of importing multiple Excel files in a folder, via an automated routine which gathered the file paths. The Import ran into problems when the Worksheet names were not the same. In other words, even if the columns are the same, FileMaker expects the worksheet name to be the same.

[jdu98a]

Well, this does seem to be related to my problem. The different files do have different names. I wonder is this issue would go away if I gave the different files different variable values. Instead of "$$File" being used every time, maybe I could have it change the variable for each import. Even if it just alternated between "$$Filea" and $$Fileb", could that would make Filemaker reevaluate what it expects from the filename?

[Fenton]

I used the same Variable name (a local variable, not a global, tho I don't know what difference that would make; but if you're setting in the script there is no need to use a global). What screwed it up was not that the files had different names, but that the internal worksheets had different names. If they were all the same it worked fine.

[bcooney]

How are you branching to the correct Import Records script step? You mention three different file formats and import orders. Do you use the file name to branch to the correct script step?

[jdu98a]

The button that starts the import process prompts the user to first select the file type from a drop-down before selecting the file itself. There are three file types, but no two files have the same name.

It would not be a total tragedy to have the actual import done with dialog, but I would really prefer it not to.