Ahhh… Excel. A tool we, as FileMaker developers, love to hate. Why do you need Excel when we have FileMaker? For many of us, starting in Excel or Google Sheets — and realizing how limited or complicated they were — is exactly how we got started in FileMaker.
And yet, our customers love to make us use it.
You can, of course, export Excel sheets from FileMaker natively with built-in tools. However, the problem we always run into is that FileMaker naming conventions tend to be very different from how our customers like to have their data displayed. For some reason, they don’t like to see z_sum_totalInventory_num, and would prefer it to be just Total Inventory.
Possible Approaches
Before we dive in, it’s worth noting that there are a few different ways to tackle this problem:
- XSLT Stylesheet — A great method, but requires fiddling with XSLT stylesheets,
which can be a bit tricky. - JavaScript Library — Great for more complex functionality, and exports natively
to the xlsx format. But the setup is more involved, and it requires a bit of familiarity with JavaScript. - Plugins — You can do this with both the 360Works Plugin and the MonkeyBread plugin,
but now you’re paying for a plugin, maintaining the installation of that plugin, and dealing with
the extra complexity of the plugin code.
For most situations though, if all you need is clean headers on a CSV export, the approach we’re covering today is the simplest of the bunch.
The Data File Script Steps
In FileMaker 18, FileMaker released the “Data File” set of script steps: Create Data File, Open Data File, Write to Data File, and more. They were designed to make text file creation and manipulation possible directly from within FileMaker. Using these built-in tools, we can easily create a “header” row and prepend it to a standard CSV file.
Note: This solution will export only in CSV format. If your client absolutely requires it to be in native Excel format, you’ll need our javascript library approach. See our blog post here.
Here is a sample file for you to to use for this exercise. It has one table — Employee — with 500 records, and the field names follow standard FileMaker naming conventions. Great for FileMaker, but not great for readable headers in a spreadsheet.
Step 1: Build the Header
The first thing we do in our script is create the header row and store it in a variable:
Plaintext
Set Variable [ $csvHeader ; Value:
Substitute(
List(
“First Name”;
“Last Name”;
“Company / Organization”;
“Street”;
“City”;
“County / Region”;
“State / Province”;
“ZIP Code”;
“Telephone”;
“Fax”;
“Email Address”;
“Website”;
“Salary”
), ¶; “,”
)
]
A couple of things worth noting here. First, the headers are much more readable — “First Name” instead of “Name_First”. Second, we’re using a List() function wrapped in a Substitute(). This keeps it clean and makes it easy to reorder or edit individual headers later.
Step 2: Prepare the Found Set
Next, we prepare the found set — all 500 records, sorted by last name and first name. Standard FileMaker stuff.
Show All Records
Sort Records [ Restore ; With dialog: Off ]
Step 3: Set the File Path and Export
Set the export location:
Set Variable [ $filepath ; Value: Get(DesktopPath) & "EmployeeList.csv" ]Warning: If you ever need to run this on a server (PSOS or scheduled script),Get(DesktopPath) won’t work — use Get(TemporaryPath) instead.
Check if the file already exists, and delete it if so:
If [ $fileExists ]
Delete File [ Target file: “$filePath” ]
End If
Then export the base CSV:
Warning: Make sure to export as UTF-8. Also make sure the export order matches your custom header columns exactly, or the columns will be misaligned in Excel.
Export Records [ With dialog: Off ; Create folders: Off ; "$filepath" ; Unicode (UTF-8) ]Step 4: Read the Exported File
Read the exported file contents back into a variable:
Open Data File [ “$filePath” ; Target: $fileID ]
Read from Data File [ File ID: $fileID ; Target: $csvContents ;
Read as: UTF-8 ]
Step 5: Combine and Write Back
Now the magic happens. Combine the header and body into one variable, then write it back to the file:
Warning: Just like the export step, make sure to specify UTF-8 when writing back to the file — mixing encodings will corrupt your data.
Set Variable [ $headerAndData ; Value: List ( $csvHeader ; $csvContents ) ]
Set Data File Position [ File ID: $fileID ; New position: 0 ]
Write to Data File [ File ID: $fileID ; Data source:
$headerAndData ; Write as: UTF-8 ; Append line feed ]
Close Data File [ File ID: $fileID ]
And that’s really it. A few extra script steps, and now your customers get a clean, readable spreadsheet instead of something that looks like it was named by a developer.
Need help? If you’d like help implementing this in your own solution, or have any other FileMaker challenges you’re trying to solve, we’d love to hear from you.
