Export All Data From FileMaker Pro: FREE DEMO!

**IMPROVEMENTS HAVE BEEN MADE TO THE ORIGINAL DEMO FILE. PLEASE DOWNLOAD THE CURRENT VERSION, MODULAR EXPORT v1.2, AT THE END OF THIS POST**

 

More Bulletproof and More Useful

In FileMaker Pro 19.4, Claris introduced the new FileMaker_BaseTableFields table to the metadata tables accessible via the ODBC interface and the Execute SQL function. 

Rather than containing one record for each field in each table occurrence, the new table contains just one field for each base table that exists on the relationship graph.

We’ve taken that update as an opportunity to again improve our modular export script. This is a single script that can be copied into any FileMaker file to perform a full CSV export of the data in all tables in the file.

The new BaseTableFields table allows us to do the SQL query for the fields in a table with a bit more efficiency than before. 

We’ve made a couple of additional changes to make the Modular Export script more bulletproof and more useful:

  • Two additional text files are now saved to the export folder containing a full list of base tables and base fields for the file.

  • The script now concludes with a dialog indicating that the export is done and where the files can be found.

 

Table Names & Fields Available in the Three Metadata Tables

These table names and fields are available in the three metadata tables:

FileMaker_Tables

FileMaker_Tables includes the following columns:

1. TableName

2. TableID

3. BaseTableName

4. BaseFileName

5. ModCount

FileMaker_Fields

FileMaker_Fields includes the following columns:

1. TableName

2. FieldName

3. FieldType (the SQL data type, not the FileMaker data type)

4. FieldID

5. FieldClass (Normal, Summary, Calculated)

6. FieldReps

7. ModCount

FileMaker_BaseTableFields

FileMaker_BaseTableFields includes the following columns:

1. BaseTableName

2. FieldName

3. FieldType – The SQL data type of the field.

4. FieldId

5. FieldClass (Normal, Summary, Calculated)

6. FieldReps

7. ModCount – The total number of times changes to this base table’s definition have been committed.

Things to Watch Out For

There are a couple of things to watch out for when using this script.

  • It will not work properly in a file that has table occurrence names that are not SQL friendly – for example, having a period in the TO name.

  • Also, the script is based on the existence of tables on the relationship graph. If you have data in tables that have no table occurrence on the graph, it will not be exported.

And keep in mind that the limitations of this script that are documented in our original blog article in our original blog post still apply as well.

Contact Us, Plus Join Our Office Hours

If you have any questions about this or other FileMaker topics, send us a note below. We’d also be delighted for you to catch our Office Hours every first and third Thursdays where we can talk “in person” and dive into various FileMaker development aspects that you’d like to know more about. 

Download the Modular Export v1.2 demo and let us know what you think!

For history, you can see the original post from 9/27/19 here.

And the update from 7/3/21 here.

2 thoughts on “Export All Data From FileMaker Pro: FREE DEMO!”

  1. Thanks for the demo file Modular Export v1.2.
    Some more improvements:
    -robust for special TO naming (for example starting with special character or space in name): ExecuteSQL (
    “SELECT “& $Headers &” FROM \”” & $TOName & “\””; “<>” ; “<>” )
    -robust for CR in text fields…make it similar to native export in .csv format by substituting CR with char(11)=VT=Vertical Tab (before your substitute step of <> and <>): Substitute ( $SQL ; “¶” ; Char(11) )

Leave a Reply

Your email address will not be published. Required fields are marked *