Import 3.600.000 records quick

[Henke]

Hello!

I get a CSV file from our main frame every night containing prices. They cant see what has changed and only send that, so they send all each night. I need to be up to date and import all to our Filemaker 10 Server Advanced table every night.

I need to find a quick way of doing it. So far the best I have found is to first delete all records in the table and then import the total file. But still it takes many ours ...

Any ideas of how to do it is very appreciated!

/Henrik

[TheTominator]

I.

One way to speed up your current method is to switch to using

(1) Show all records.

(2) Import - update existing records in found set - add remaining data as new records

(3) Show omitted

(4) Delete found set

This will bypass the lengthy stage of deleting all records before importing and having to create new records. The current records will be recycled which will save noticeable time.

II.

A second suggestion is to do the preliminary work outside of FileMaker using a dedicated program. Keep a copy of the previous .csv import file and compare it to the new .csv file. Have the custom program spit out a file containing only the lines containing changes. Import this tiny .csv file using "update matching records in found set" and "add remaining data as new records."

Ideally it would be a speedy complied language code, but even a Perl script will be 100x faster at this prep work than FileMaker's import engine.

III.

A third suggestion is to configure an ODBC connection and have FileMaker talk directly to the "mainframe" containing your master copy of the data. Add the appropriate shadow tables or do a direct ODBC import if you can determine what SQL to use (e.g. examining modification dates on records).

Edited June 13, 2010 by Guest