Complicated Excel Import Script?

[MSteven]

I am new to FileMaker (using the trial version) but have done some db programming over the past (DB III+, Paradox for Win, etc.)

I am trying to set up a script to automatically import some excel files (that will be updated daily) that have some unique attributes:

1) I do not need every column (field) to be imported

2) the first row of actual data begins on row 5 (ie, don't want to import first 4 rows with erroneous data)

3) There will be 19 sub files, each with a unique file name and I would like to somehow add the filename as a new field with the filename in that new field for each record, so that when I combine all 19 files into a final single file, records with the same key will combine/join together to include all fields (this will show me all the different files that a particular record originally existed in). I noticed that FM doesn't have a Bool field, so if there is a workaround to achiving some type of on/off toggle for each field of a record to indicate if it was included in one of the original 19 files.

I have done a basic search of the forums but haven't found any topics that seem to address my questions.

Thanks

P.S. If you would like a sample of one of the excel files I will try and post one here

Edited September 8, 2005 by Guest

[MSteven]

This must be more difficult than I thought ...

maybe I should break down each question into a separate topic?

Thanks

[Fenton]

1) Just don't line up that column to a field in the Import dialog

2) FileMaker can import a named range from Excel. But if they're not named, you'd have to import all rows (with data), then delete the 1st 4 records (rows). After an Import, the imported (or matched) records are the found set, so it's pretty easy to delete the 1st 4 records.

3) FileMaker does not get the file name when importing Excel files. On a Mac, I'd use AppleScript to get the file name. On PCs, I'd recommend a plug-in like Troi File, with makes it pretty easy to get the file name.

I don't know command line on PC, but maybe you could get the file names in the folder using the free Abstrakt Shell plug-in, http://www.abstrakt.com/shell.html

I'm not sure if it works on Windows FileMaker 7 (they have a question mark on compatibility).

There's also the Send Event step and command line, which could write them to a text file, which you could then get.

Then Replace the name into a field; that does the whole found set with one command.

You would also want a date field that was set to auto-enter the creation date.

[MSteven]

1) Just don't line up that column to a field in the Import dialog

2) FileMaker can import a named range from Excel. But if they're not named, you'd have to import all rows (with data), then delete the 1st 4 records (rows). After an Import, the imported (or matched) records are the found set, so it's pretty easy to delete the 1st 4 records.

3) FileMaker does not get the file name when importing Excel files. On a Mac, I'd use AppleScript to get the file name. On PCs, I'd recommend a plug-in like Troi File, with makes it pretty easy to get the file name.

I don't know command line on PC, but maybe you could get the file names in the folder using the free Abstrakt Shell plug-in, http://www.abstrakt.com/shell.html

I'm not sure if it works on Windows FileMaker 7 (they have a question mark on compatibility).

There's also the Send Event step and command line, which could write them to a text file, which you could then get.

Then Replace the name into a field; that does the whole found set with one command.

You would also want a date field that was set to auto-enter the creation date.

Well I won't be able to use the import dialog since I want to script this and have it execute automatically. Is there a function/command, etc. that can be used to add a field on the fly? If so, what is it?

Also, can you give me an example of what you mean by "replacing the name into a field".

I have never used FileMaker before and am not sure about some of the terms you are using and their proper syntax. Maybe you could point me to some examples showing how this all might be scripted.

Thanks for your help

[comment]

Well I won't be able to use the import dialog since I want to script this

Yes, you will - the same import dialogs are available when you create your script.

If you are new to Filemaker, I'd suggest you try doing this step by step. First, get yourself a sample Excel source, and experiment importing it manually, learning the various options. When you get comfortable with that, script the action and move to step 2.

[MSteven]

Yes, you will - the same import dialogs are available when you create your script.

If you are new to Filemaker, I'd suggest you try doing this step by step. First, get yourself a sample Excel source, and experiment importing it manually, learning the various options. When you get comfortable with that, script the action and move to step 2.

Maybe I am misunderstood ... I have used the Import Dialog and understand that I can select specific fields that way. However, I am trying to create a script that does this automatically, without any user intervention (ie, using the Import Dialog).

Am I missing something here? If I go through the process of using the Import Dialog, will that create the script that I need to do it autmatically from that point on?

Thanks

[comment]

Go to Scriptmaker, select the Import Records step and see the options there.

What you have outlined is not too complex, but neither is it a beginner's project. Do not expect to find your way without reading the help file, at least.