Exporting 150.000 records - To much for FM?

[norbert]

I have to export about 150.000 records in FM (for further calculations in Arcview and SPSS). I tried several export formats, but I wated for hours before cancelling the process. I guess it will take a few days!

Any ideas how to speed up exporting?

Do I really have to use another software (MS Access)

______

Some Inormation about the database: about 30 fields,

10 x num

10 x Calculation

1 x summary

10 calc using summary/Break options

________

FM PRO 9 on PC 2GHZ and PB 1,67 with 2GB RAM

[normanicus]

Just eliminate the calculation fields from your export. Actually, it is only unstored ones which take all the time. In some rare cases (it has happened to me) this can lead to the data in a file created from such an export to be false.

Unfortunately, eliminating the calculations fields has become more difficult with recent versions of FM. Previously, the sort order chosen in 'Define fields' was retained in when showing the fields in the 'Export' dialogue. As calculations fields were at the bottom when sorting by 'Type' one only needed to locate the last data field in define fields and then, once in the export process, omit all those after that one.

It would be great if there were an option to export 'data fields only' in the export process. As it stands you have to go through and omit those calculations one by one. An alternative would be to rename all calculations with preceding letters that would make that show up in a block during export (just thought of that one).

[norbert]

well...

but i need the caculated results in the exported file?

[comment]

Perhaps these calculations could be optimized. And do you really need to repeat the summary information for each record?

Previously, the sort order chosen in 'Define fields' was retained in when showing the fields in the 'Export' dialogue.

If you select 'Current Table' instead of the default 'Current Layout', you will see the fields in the same order as defined in 'Manage Datebase'.

[norbert]

the summary field is used for calculating subsummary/breaks (after sorting)depending on another field

I dont see a workaround so far

[normanicus]

Might I ask where the exported data is to be used? If it is to be picked up by another application then the calculations could be recreated there.

All the time is taken as FM calculates all the unstored calculations during the export presumably to make sure they are current. I don't know if it would be quicker to use a script to populate non-calculated fields with the calculation results but I suspect it would. You would then have the necessary values in those fields.

I don't know what programming experience you have but yet another approach, notably if this export is to be done regularly, is to export the raw data and redo the calculation in another application and re-export from there. Excel is one possibility and does not need much programming skill. I would use Python which is pretty simple to learn.

[David Jondreau]

You could try to see how long it will actually take by timing the export on a small set (using Filemaker's clock) and extrapolating. And you could see how long it would take for FM to index those calculations as stored calcs and export THAT.

[norbert]

to bring this thread to an end:

1st. In fact, FM is not capable of big databases ( containing 100.000s of records in combination with calculations).

2nd Using EXCEL might be a solution, but filtering and sorting big databases in excel is also very slow as well

However, calculations can be done very quickly in excel,

3rd. I solved the problem with some scripting in SPSS. SPSS calculates values once. If you have to update your fields you have to recalculte the values again. The good thing is:

SPSS is incredible fast in doing such calculations. The calculation of 50 fields for 5.000.000 records took about 10 min.

PS thank you all for your input and help

[Genx]

1st. In fact, FM is not capable of big databases ( containing 100.000s of records in combination with calculations).

Databases contain data - not calculations.

I solved the problem with some scripting in SPSS

Glad you found a tool more suitable for the task.