Recently, we had a client request to export their FileMaker solution into CSV files with field names. The solution had a sizable number of tables, though doable manually, it inspired me to look for a more automated method. Much to my dismay, a script or module that fit our needs did not present itself. But I learned quite a bit about FileMaker exporting in the process.
At first, I wondered how hard it would be to write a script myself to automatically loop through all the tables in a file and export all records. I quickly ran into a number of roadblocks to this, such as:
Where do I get a list of all table names in the file?
I need a layout with context to the table to export, how do I export tables without layouts?
How do I assign fields to export without the export dialogue?
How do I add field names to the file?
The task looked more and more hopeless the more I looked for solutions; exporting to an excel file adds field names, but you cannot export to excel with the export records script step and the save as excel script step only exports fields on the current layout. While it seems there’s no good method to use the exporting script steps in a modular script, I did find out that Merge files are a good alternative to CSV that do include field names.
Thanks to FileMaker 18’s new features, a simple solution presented itself with the new data file script steps. We can easily create and write as many data files as we need, as long as we can gather the data to put in them. I then set out to find solutions to the other roadblocks, starting with a list of all tables in the file, so how about the TableNames function? This seems plausible, however, it returns a list of all table occurrences in our file, exporting from each occurrence would work, but it would result in redundant files from occurrences based on the same base table. Some searching lead me to a wonderful solution in the form of a custom function found here. It makes use of an ExecuteSQL function and searches one of two tables I wasn’t aware could be queried, “FileMaker_Tables” and “FileMaker_Fields”.
This post on Databuzz gives a more in depth look at this, but in short, these tables store all the schema table and field data of your file. With this we now have two additional pieces to our script, a list of all tables we need to iterate through, as well as the ability to get a list of all fields in any given table. Finally, with an ExecuteSQL function we can query all fields in any table regardless of context,we now have all the data required to get our script running. There is one last issue to address, however, and that is commas and returns in our data. If we just pull the data as is, any commas or returns in any given field will skew the alignment of our CSV file. Adding quotation marks around our delimiters as well as at the start and end of the whole dataset clears this problem but also adds the issue of quotes in your data. I solved this by performing a substitute on my dataset to remove all quotes from the data, depending on the data you are working with this might not be an ideal solution.
You can freely explore and use the script by downloading the demo file below. The script can be run from the scripts menu or by adding it to a button. It can be run immediately after importing into another file without any changes.
If you have any questions about this topic or have issues implementing the functions in this post, leave a comment below or contact us.
I also see an issue where this method fails to export all fields.
I did some testing with that file and found a couple of cases where potential issues arose – If the FMP table name has a space in it, the SQL command wasn’t handling it correctly, and the export doesn’t get interpreted as a proper CSV file if the FM table has container data. I have a newer version of the demo file with some corrections. If you’re interested I can send you a copy.
I’m not sure any of our team have used this technique in FM19 yet. One of us will give it a try and reproduce your results and post our findings back here. Thanks for your input!
I used the script in this demo file with FMP19 and it often has issues building the headers and data to write into the files. It also caused some weird issues with the test file where layouts would have no layout parts. The tables the layouts were based on were the ones with no data in the export files. This was repeatable and each run had the same issue for random tables each time. It seemed to occur more often in tables with more data. FMP needed a restart for things to work properly again. While this is an interesting idea I think using eSQL in this way might not be the most reliable thing to do.
I tried to download the sample file and it seems that it downloads a wrong file. The file I downloaded from the provided link is called “PBS Demo – Key Relationship.fmp12”. Is this a correct file?
Hello! Thank you for bringing that to our attention. We’ve updated the link and it should be taking you to the correct file now. Please give it another try. It will be titled PBS Demo – Modular Export.fmp12.
The link works now, great job!
I downloaded this file and I’m attempting to view the scripts and custom functions to see the specifics and see if it would work for what I’m trying to do. However it looks like I have limited access to the file. Is there a login I should be using?
Hi Paul, I just downloaded the demo file as a typical person browsing the blog and the only active account in that file is a full access account. If you keep having difficulty, please email email@example.com and we can email you a copy directly that we’ve tested.
I had to do something very similar a few years ago, and I wrote it up in July 2014.
I did it using a virtual table, which had ramifications in complexity, and Karstyn McCoy commented that 360Works ScriptMaster could have exported it to a file pretty easily too (see comment at the end).
Thanks Peter, I went back and skimmed your post. Good stuff – we’re big fans of virtual lists for reporting and complex exporting at Portage Bay.
Your email address will not be published. Required fields are marked *
Save my name, email, and website in this browser for the next time I comment.