Need info about import scripts

[AKira5284]

Howdy All,

I'm fairly new to Filemaker, I was asked by our company to choose and learn a new database program in order to replace older software.

I was wondering if it were possible to create a script that would import data into FMP from an excel spreadsheet. Furthermore, would it be possible to make a script that would do it for multiple excel spreadsheets if they were all in the same folder?

Thanks,

Akira

[Fitch]

Look at the Import Records script step. You can import a folder of text files. AFAIK that won't work for Excel files. However, you can specify the data source as a file rather than a folder and this will work with Excel files. Go figure. With this approach, you would put one script step in your script for each file. So you have to know in advance which files will be in the folder. Alternatively, you could export from excel as text, and then you could just have a one-line script that imports the whole folder.

[Fenton]

Import Folder (of text files) and Import File are two different commands. Import folder is designed so you can import the entire contents of a file, graphic or text as one thing, into one field (with a few other fields for the name and path).

Importing a single file is importing delimited values, spreadsheet or delimited text, with rows and columns going into records and fields.

So importing a folder of Excel files is much the same as importing any other delimited files. You need to get a list of the files, then one by one switch the name to a fixed name, Import, rename/move that file, go on to the next, repeat.

On a Mac it's easiest to use AppleScript. On a PC I imagine you could use command line, but I'd use Troi File. It can get a list of files in a specified folder.

If you're an Excel expert I image you could alternatively write a macro to produce one concatenated file from the files in a folder.

These Excel SS have to be pretty simple, just a header row and data. Anything else is going to be kind of a mess. FileMaker supports targeting worksheets and named ranges, so you may be able to bring in more complex SS using that. But automating multiple files would require each to have the same worksheets and named ranges.

[Fitch]

Thanks for the correction; I forgot about the way Import Records works with a folder; I've actually never had occasion to use it. I wouldn't call it two different commands, though. It's one command that behaves differently depending on the specified data source.

I agree with your other points. Troi File would be useful for this, if you don't know the file names in advance.

But again, to keep it really simple: if you know the names and locations of the files in advance, you would just have a script with a separate script step for each file; it wouldn't even matter what folder the files are in.