Import filenames of .csv files

[Earle]

I want to import multiple .csv files into one Filemaker database. During the import, I want capture the file name of the .csv and place it into an empty Filemaker field for every record related to the .csv.

The goal is to identify from which .csv any record originated after I am finished importing all of the .csv's.

Thanks in advance for all of your help!

Earle

[Fenton]

The big question is, "Is it the same structure each time, the same columns?" If so, then you can capture the name of the file, set it into a script Variable, in FileMaker path syntax, then use that as the path for the Import.

There are a couple of ways to get the file path. If this is on a Mac you can use AppleScript to choose the file. Then translate that path to a FileMaker syntax one; which is mostly just Substituting the ":" with "/", and prefixing with "filemac:/". The basic AppleScript commands would be:

set Mac_path to choose file without invisibles

tell application "Finder" to set file_name to name of Mac_path

set path_txt to Mac_path as Unicode text

You'd then set file_name and path_txt into globals field in order to have access to them from within FileMaker. Then set the path into a script Variable, and use the name to Replace into your column after import.

The Import would use the FileMaker syntax file path in the Variable as the file path, so that it could proceed without a dialog. This is why the structure of the .csv file must always be the same columns.

Personally I would have a separate table for file paths and names, with an auto-enter serial ID. So the process would create a record in that file, get the new ID, and replace that into the csv columns instead, to tie them together. Because, the file name may not be unique, the file path may be long, and in any case a File_Names table would be easier to access later.

An alternate method, which is cross-platform and a bit easy would be to first import the file (with dialog) into a container field, [x] As reference only. Then use GetAsText to get the file path from the multiple lines (it will be the last one). The advantage of this method is that the path is already in FileMaker syntax.

Now that you've got the path, proceed with the real Import without dialog (as above).

[Earle]

Fenton,

Thanks for your detailed response. The .CSV files will have the exact same formatting each time, i.e. the columns will be the same and the data under the columns will be the same.

These imports will eventually be exported back to a .CSV after the appropriate filename has been placed into each record. After that, I'll never need that particular database again. I'll reuse the FileMaker file again and again, but the .CSV files will change daily.

So I guess I am trying say that I don't need a lot of "record-keeping" for the .CSVs. The only reason I am using FileMaker at all is two fold:

1. Excel sucks. -) More specifically, Excel removes leading zeros upon import and my .CSV files have ZIP codes with leading zeros.

2. Excel doesn't have any way that I know of that makes it easy to combine multiple .CSVs and add their file names to their respective records.

As my profile states, I am a FileMaker novice and my Applescript-ing isn't much better, so bear with me for one moment longer...

Your post suggests that I can use a script within FileMaker in an extra field that would grab the filename. Do have to run the corresponding Applescript before I import the files into FileMaker?

Thanks for all of your help. You have gone above the call of duty already. I'd buy you a beer, or another beverage of your choice, if I didn't live across the country :-)

Earle