Have you ever wanted to export a single field and maintain all the characters in that field? This article explores the possibility with XML Export and the use of a simple XSLT. But first we’ll explain the good, the bad and the ugly of some different standard ways to export TEXT out of FileMaker.
Using this sample text in one field (and one record) we will make different exports and review the results:
Export Field Contents
This is a script step that does what it says (see the Help Topic on this). It also may be a contextual menu item, accessible by right+clicking (or Ctrl-clicking on the Mac) to see the menu. All text in the specified field is exported to a single file (without the text formatting, of course). Note: since this is a single-field, special purpose export, the options are limited compared to other FileMaker exports.
FileMaker does not give you the option to select an “Output file character set” in the Specify Field Order dialog, as with other export formats, and the resulting text file will be encoded as UTF-16 (Little-Endian).
Let’s view the freshly-exported file in BBEdit (on the PC, you might try NotePad++ or UltraEdit). Line endings (invisible carriage returns) have been displayed for clarity, and the character set is displayed at the bottom of the window.
You get returns-in-field and tabs-in-field exported ‘as is’. In addition, invisible characters called BOM (Byte Order Mark) have been added to the start of the document in the export process by FileMaker. These are not displayed (invisible even in BBEdit), but may cause problems for applications that are NOT expecting them.
For these reasons (UTF-16 and BOM), this kind of export may not work for you. Remember that the text-formatting (bold, colors, etc.) will be removed from all TEXT exports.
Export Records
This command is a menu item and a script step that has several options for your text export. Select File > Export Records. Most of these exports allow you to select the Output file character set when you Specify export order, although there may be slightly different character sets for the various export types.
Each of the exports has a specific default extension, but may be changed to “.txt” or any extension that you deem appropriate. Make the change in the script to export (or manually) when specifying a file name to export. I like to set variables to the filename and path for export. These can be dynamically generated, so that the name of the exported file is unique (append the date and time, for example).
Tab-Separated Text (also called tab-delimited), will export a tab character (ASCII 09, url-encoded %09) between fields and carriage return (ASCII 13, url-encoded %0D) after every record/row in your export. The default extension is ‘.tab’. Specify this Type in the Export Records to File dialog.
The results are not so bad, except this format will convert every Horizontal TAB character (ASCII 09) in the field into a space (ASCII 32, url-encoded %20), because it needs to use the TAB for between fields.
The return-in-field is also converted, but to a character that is often not understood by other systems: the Vertical Tab (ASCII 11, url-encoded %0B). This is shown in BBEdit as the red upside down question mark. Note the return at the end of the single record, placed there by FileMaker in the export.
In some applications the VT may be shown as a small rectangle. Another character that is often seen as a small rectangle, is the LF (Line Feed, ASCII 10, url-encoded %0A). The GS (Group Separator, ASCII 29, url-encoded %1D) is the character used when exporting from a repeating field.
Comma-Separated Text will place double-quotes (“”) around text and separate each field with a comma. The default extension for this Type is ‘.csv’. Specify this Type in the Export Records to File dialog.
Numbers may or may not have double quotes, unless the field is NUMBER type. Any quotes within the field will be escaped by doubling them in addition to the quotes around the entire field. The return-in-field and tab-in-field are also not preserved, but are converted as with Tab-Separated Text exports.
Merge text is exactly like the Comma-Separated text, but includes a top row with the field names. The default extension is ‘.mer’, but I often change this to ‘.csv’, so that I get the column names in the first row of a spreadsheet. Specify this Type in the Export Records to File dialog.
The top row of field names is also comma-separated (but not quoted), if more than one field is exported.
HTML Table is just another text export that puts HTML tags around your fields and records. The extension is ‘.htm’ by default, but may changed to ‘.html’, if you prefer. Specify this Type in the Export Records to File dialog.
This give us this text. Note: the carriage returns appear to be preserved by being converted to the HTML tag ”
“:
This is how most browsers will render the HTML:
Export Records with XML
OK, I get it, the examples above may have problems. So try XML (eXtensible Markup Language) and you don’t even need to learn a lot about it.
Raw XML export is just the XML without the XSLT. The default extension is ‘.xml’. Specify this Type in the Export Records to File dialog.
As soon as you click Save, you will get the following dialog. Select FMPXMLRESULT grammar, if it is not already selected. Ignore the “Use XSL style sheet” for now.
What you get will be similar to this. Notice that the returns-in-field are preserved.
Open the file in various browsers and some will apply a “default” style sheet to “pretty print” the XML. This does not show the characters as all preserved, but they are.
Export with XSLT is where the “magic” happens. XSLT stands for eXensible Stylesheet Language Transformation. Already in the name we know something is going to be “transformed!” The stylesheet is a valid XML document with special XSLT and XPATH tags that will read the source (your export) XML and push out XML, TEXT or HTML (all just text, really!)
That’s it, that’s all there is. Take out the comments “” and there are just 8 lines. This has been pretty-formatted, but the extra space could be removed, too. This XSLT will work with FileMaker 6-12, as long as you only want the result from one record, one field.
"fm:ROW[1]" is our one (first) Row/record "fm:COL[1]/fm:DATA" is the one (first) column/field
“xls:copy-of” is where most of the magic happens. It simply returns whatever was in the field. The other magical key is the “xsl:output” near the top. It has an attribute “method” that we set to “text”.
This is what we get, as shown in BBEdit with returns and tabs shown. And it is exactly what we want for one record, one field. Yes, even the tab character is preserved.
Putting it to use
You can export plain text for a variety of solutions that need “Export Field Contents”, but with UTF-8. A couple of articles on this site refer to exporting and this method using XML with XSLT could be perfect!
Remember that the extension can be changed on these text-type exports. XML can be ‘.xml’, ‘.htm’, ‘.txt’. You could even create Comma-Separated Text or Tab-Separated Text with an XML export, if you have the XSLT that makes the transformation for you. This gives you more flexibility to add different delimiters as required.
If the XSLT does not meet your needs, try a FileMaker plug-in or one of the other methods, described in several articles.
• Exporting Data to iCal, Outlook, Google Calendar, etc. – Kevin Frank
• Fixed Width for EDI and Other Reporting – Beverly Voth
If you have any of the characters in your field that XML normally uses, these may have different results upon export. Test for these carefully: “<“, “>”, “&” and perhaps the quotes characters. The “xsl:copy-of” should preserve them ‘as is’ because we are using the text output method in the XSLT. Compare this to the raw XML export. Many characters get “encoded” so that they don’t conflict with the XML formatting.
Try each of the exports with a Repeating field and related fields. Those are entirely different and not the focus of this article.
Download the demo file and XSLT here
Download single field tab demo here
UPDATE 29 JUL 2016, beverly
Several have found that Windows FileMaker has a problem with this method (XML/XSLT) and is exporting an extra carriage return between “lines”. This is an alternative method of using One Field Several Records and Export (as tab delimited). Using the single field or variable, loop to push the ‘lines’ (returns in field) to a temporary table and Export.
USING BASE64 & Export Field Contents
(Update 29 MAR 2017, beverly)
(untested) The following code may help with the export to preserve what is in a single field (or variable). The BASE64 functions (two) were introduced with FMP 13.0 and can be used to “insert” the text of the field into a container field. Export Field Contents does not change the text of a file in a container field. Export Field Contents has been around since FMP 6.0!
Set Field [ table::yourContainer
; Base64Decode ( Base64Encode ( $anyText )
; "filename.txt" ) ]
Export Field Contents [ table::yourContainer ]
Thanks to the FileMaker Community Forum for this tip! You can use any filename and extension needed. Remember that this is untested on different OS versions (& platforms), so YMMV.
Using TextEncode() & Export Field Contents
(Update 09 May 2017, beverly)
See the update for FM 16!
https://filemakerhacks.com/2017/05/09/export-field-contents-update-for-fm-16/