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.
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" is our one (first) Row/record "fm:COL/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!
41 thoughts on “An In-Depth Look at “Export Field Contents””
Wow, Beverly! Very nice. Thanks for that!
Thanks for doing this Beverly. You make it seem so easy. I will see if I can make this work and get away from using the ScriptMaster plugin as described in my posting.
Still hoping encoding to UTF-8 becomes an option in Export Field Contents.
> 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… The top row of
> field names is also comma-separated (but not quoted)…
I like the “call the export a CSV when it’s really a MER” trick… there was one occasion when I *did* need the field names to be wrapped in quotes also, and I was exporting from a utility table so I used quotes in the field names themselves.
Or I create an XSLT with the field names (in quotes) as part of the export for “.csv” (just another text export). :)
But, yes, I have made this kind of export work by quoting the field names IN the define fields, too. Since this kind of field name may break the other ways I work with FMP data ( ODBC & web publishing, for example ), I’m more apt to create an “export” table for use with this method and preserve the field names unquoted for the rest of the solution.
I’ve also made a first row (by sorting) with the field names – just export as CSV, and you have the quoted header row. Now with ExecuteSQL(), we may be able to create an “exportable” version – Calculation with the first row as we need and the rest from the ExecuteSQL(). We have many possibilities, right?
I truly wish we had greater control of the export dialog to change things like the delimiter and, of course, the “Output file character set”! Until then Kevin gets to write some great blogs about what-else-can-we-do-with-what-we-have. I thank him for allowing me to share a little of his space.
Again, thanks for this great followup. I tried the XSLT route in my solution, but I am getting an error message.
“SAXParseException: invalid document structure (Occurred in entity ‘Users/dougalder/Documents/FMTimeline/onefield_export.xsl’, at line 2, column 1.)”
I get this once for each of my exports, an HTML file and a JSON file. Any suggestions?
I can send you the file directly if you contact me via my web site (www.hbase.net).
Remember that the “Export Field Contents” (from a text field) script step is the reason for creating the XSLT in the first place. If you just use the text of the XSLT in a field and “EFC” it, then you run into the problem we are trying to prevent! The XSLT in the demo has been inserted (as text) into a text field (for reference) AND (as document/file) into a container field. The EFC script step in the demo to get the XSLT for use with your export is calling the Container field.
I’ve chosen the path to the database “Get ( FilePath )”, but any other path that FileMaker can use ( desktop, documents, temp folders or folders in those locations ) is also valid. Just remember that the XSLT and the result XML can be in different folders/directories, but I prefer to have them in the same location, for convenience.
Great write-up! Thank you so much for sharing this very useful tutorial!
Is there a “like” button in wordpress ? :)
I am struggling with the one field export because I have tried a variety of xslt files to transform my data but in every case when I open it with Sublime Text (I use Windows) I find that a blank line has been inserted in between each line of the xml.
I have used your demo file. Inserted my 214 lines of xml data in your text field (which is a HMRC xml file) and then used the Export xml with xslt.
When I open it again in Sublime Text the 213 lines have become 425 lines.
A sample of the HMRC File is:
your 4 digit vendor ID
your product name
version number of your product
Any help you can give me would be much appreciated.
What’s actually in the field before you export? Have you tried to COPY the field and paste into Sublime Text? If you are getting the same results, then Sublime Text is converting your returns-in-field into a double return (probably). The XSLT is only a COPY of the field contents. You may need to do some pre- & post- processing (use a character for “return” that is not otherwise used, then open with Sublime Text and convert the character to the return. See what you get. If the end location is meant for Sublime Text, then you need to adjust it or your field contents.
When you open the same text in NotePad or WordPad or other text editor, what do you see? What if you open the text in a browser?
I have tried two versions of the field to export from Filemaker. A simple text field using your bvoth_export_field_contents file and additional calculation field which I added to the file which adds a carriage return after every element in the file. That certainly doesn’t work In both cases it is about 213 lines of data from an xml template supplied by HMRC as being their expected input.
I have just tried a copy and paste from Filemaker to Sublime Text and while it is not quite perfect enough for HMRC it does not include any blank lines. In fact, and I should have tried this before, if I export it using your utility, and do not attempt to save it as xml before importing it into the HMRC test engine it almost works, just a single error.
If I open both the exported text file or the copied text file in a browser it also looks fine. Likewise in Wordpad. It is only if I open it in Sublime Text in its text format that it has the extra lines. I have also tried to open it in Altova XML Spy which modestly describes itself as the world’s best-selling XML editor and the industry standard XML development environment for modeling, editing, transforming, and debugging XML-related technologies I also get the added lines.
I think my workaround, given my rather limited understanding of XLST will be to upload the text file to HMRC.
Thank you for your prompt first response, it is much appreciated and helps to focus my thoughts.
Remember that the article is about trying to export as TEXT (not as XML). The XSLT transforms the xml in the export and returns TEXT. Hopefully, you have what you want now. Perhaps you need to export as XML and apply a different stylesheet (XSLT) that retains the XML?
I had a similar issue recently with the extra blank lines and eventually came up with a twist on an XSLT file. The particular fix was to turn off indenting in the output method.
Ben was the xsl:output method attribute as ‘xml’, ‘text’ or ‘html’? And on what platform? version? and what version of FM used (if any)?
html, windows 7, Filemaker 12.
Ah! method=”html”. Yes, I include an indent=”yes” for that and method=”xml”, but not for method=”text” as in the article. Thanks for the comment, Ben!
xsl:output method="" indent=""
Hello Beverly, thank you for your excellent analisys.
My problem using your example is the duplication of CR LF control chars, for each row of the text included in my FileMaker Database.
I use FileMaker Advanced 12.0v3 in Windows 7 environment.
I have a table with only one global field that is used to save a text of several rows and I need to create a text file with the exact content of the database field.
The actual XLS file is the following:
I know that this is a problem related to Windows version of FileMaker: I tested the same solution on OSX and there aren’t no duplication of CR LF. Is there a workaround to bypass this problem?
Thanks in advance.
Hello, your “example” did not survive the blog post. Can you send it directly? I would also be interested in the script you use to create the text in the field. Perhaps this is making a “duplicate” and not the export? Or if this is a bug, can you cite the source of where you heard this to be true?
Perhaps the CRLF is not the correct character and Excel is converting it to CR CR or LF LF (thus “duplicating” the line feed or new row in this case)? I do many export to XML with XSLT transforming to XLS (Excel). This works on both platforms.
P.S. I’ve replied privately, so you can send me the file directly.
Thank you so much for this, you saved me numerous hours of cries.
The xml to xslt trick is just genius.
This seemed to work well while testing, but the export time seems to get exponentially longer, the larger the field gets.
125 KB took 1:24 (minutes:seconds)
150 KB took 2:43
200 KB took 6:42
300 KB took 20:02!
I’m trying to export a 5MB field.
…(using FileMaker 13 Advanced against a file on FileMaker 13 Server).
Regardless of version, a LARGE field is going to take more time. Have you tested a compare with one-record-one-field export as Tab-delimited? While we aren’t testing what gets exported with this (ignoring conversion of tabs and returns) – we want to see the comparison for time of tab-delimited vs. the xml/xslt export. Also test with comma-separted export of the one-record-one-field.
The article’s purpose is to compare *what* gets exported using the various methods and a “work-around” if you need UTF-8. Note that Plug-ins, AppleScript and VB Script usage can also give you exactly what is in a field. It may be worth a test to see if any of these methods improve (or not) the time needed to get the data in a field to a file.
Other exports’ execute time only increase linearly as the field gets larger.
The XSLT export execute time increases exponentially. I found another stylesheet that seemed to execute a little faster, but the exponential increase was still a problem:
080KB, 00:07 (min:sec)
Since I can’t think of a good reason text processing time should increase so much if there’s several times the amount of memory as field size, I went ahead and reported it as an issue: http://forums.filemaker.com/posts/18ff9fb4a1
My workaround was to split the field into multiple records and modify the stylesheet. I would expect this method to be slower, but, since the 5MB field seems to never finish, it turned out to be infinitely faster.
Thanks for checking on the other exports and posting your solution, Eric!
Hi, Beverly – thanks for this great article.
Could you help me with a closely-associated problem, please? I am trying to harness the power of batch files from inside FM. (We currently achieve it by creating the appropriate Batch file separately and then driving it open from a FM script.) We would like to progress the idea, to build up the batch file dynamically (rather than have only the static content we originally created), and to have the whole set of batch files contained within FM. This final step would make the trick really powerful and robust.
If I type in Notepad
ECHO Hello World
…then opening that as a batch file works perfectly.
If I store each of the lines in their own record (ie: 3 records) and export them using ‘Export Records’ and open that batch file it also works perfectly. (This was only discovered as a de-bugging step – it would make the idea impractical.)
If I build up the text into one field, and use ‘Export field contents’ it fails to run the batch file.
If I build the ‘return’ characters using the Pilcrow it fails to run (or even look right) in the batch file.
If I build it using Char(13) it fails to run.
If I build it with any combination of Char(13) or Char(10) it also fails.
If I use FM to show me the characters in the working, hand-typed, original batch file, it shows the ‘returns’ as Char(13) – hence I believed it would work if I use Char(13) to build it as a calculation.
I can copy and paste the working text between Notepad and FM and it will always run as a batch file.
The fundamental question is: what character is FM putting as the record delimiter when it is ‘Exporting Records’ (I believed it was ASCII13, and analysing the text exported shows that), and how could I add it as the ‘Return’ when ‘Exporting Field contents’? Adding ASCII13 to the text-build doesn’t work. We are so close (one character away!) to having a really strong, system-level management tool, and this is really frustrating!
Thanks for your help,
The point of the article was to show you that your method of one-line-one-record is OK. or… use the XSLT and export as XML to make sure the EXACT contents of a single-field-single-record is pushed to a file. IF you have a CR in the field, then it will be output.
this Export Tab Separated states:
So you see if you use the standard export, it will convert tabs and carriage returns. Thus the XML export which does NOT change characters in the field when exported.
Thanks very much for the fast response, Beverly, but I am trying to create a batch file on-the-fly, perhaps to create a folder with a name derived from the current found set, or using the current date, say. I want it to be usable multi-user, so I thought if I build the text into a global field and then export the field contents it will be flexible, and multi-user-capable.
When I use the ‘Export field contents’ script step there are no file formats to choose from (only with the ‘Export Records’ step), so I can’t see how I can export as XML that way.
I could create a dedicated table with one record, one global field, create the batch file text in that record, and ‘Export Records’ from there, but unfortunately when I choose XML as the export format it doesn’t produce a character-perfect copy of the field contents – it adds a lot of (HTML?) characters which – I’m sure you’ve worked out – I don’t understand. Should I be creating an XSLT file to use in the export and to transform the export into the exact character copy? Unfortunately, I have no idea how to do that…
Thanks again for your help.
You can search this blog (and others) for ways to DYNAMICALLY name a file. You can set variable(s) with whatever you want and use the variable name in the Export script step dialog. The PATH to the file is also important, because a limited number of locations are approved for export on all desktops and FMServer. (documents or desktop). See the functions: Get ( DesktopPath ) http://www.filemaker.com/help/13/fmp/en/html/func_ref2.32.29.html#1041647 and Get ( DocumentsPath ) http://www.filemaker.com/help/13/fmp/en/html/func_ref2.32.31.html#1029146 for example in help in where.
IF you export using the XML and the XSLT (as in the article), then you will get the exact contents. You cannont just export the XML! It will add the XML tags (not HTML tags). You must also include the XSLT in the export dialog. Just use the dynamic naming with the variable.
Thanks again, Beverly – I can generate variable file names, etc – the example uses for this tool could be to create a *folder* to hold the linked documents for a new member of staff, say. I need the OS-level control that a Batch file can give me to create the folder (if it doesn’t exist, for example).
I can understand from what you say that the XML export of my built-by-calculation global field will work, but only if it is exported with a matching XSLT file. That’s the bit I’m stuck at now – it is beyond my knowledge to create the appropriate XSLT file…
Thanks again for your help,
The XSLT has been created! That is what the article is about. :) Pease download the file listed above.
So, I got there, in the end…
We were quite tickled to exploit the power of batch files directly from an FM script, but now with the batch files being generated dynamically, and all of the content being managed inside the FM file (less chance of forgetting/moving/deleting important stuff) the flexibility and robustness of the tool is really inspiring.
Thanks very much indeed for your patience – and your XSLT file, of course!
The other comments on the blog are very helpful, too. Good that you got there… :)
have been searching for this. thanks for the wonderful tip :)
Hi Beverly – Your solution is the closest I have found to a solution for my issue.
I am exporting to a file from a field that is constructed by calculation in FileMaker. I could send the calc if you want.
What I am looking to accomplish is to replace a CR at the end of each line with a CRLF. Your xlst just adds a CRLF after the original CR, giving me an extra blank line between the data.
Sample of what I am trying to accomplish –
EASI 2.0 01 112088708 01 105687859 20100809 063949 PDT P 997 080906039194566
01 997 1.0 01 105687859 01 112088708 20100809 080953 T 080908095349328 A
EASX 080906039194566 01
And a sample of your result –
EASI 1.0 01 174989186 01 780226197 20150825 142506 MST P 997 165334650594741
01 997 2.0 01 780226197 01 174989186 20150807 092657 P 080709026164066
EASX 165334650594741 01
Can you assist in how to replace the CR with CRLF?
Thanks for any help you may be able to provide.
Todd W Carter
The XSLT doesn’t “add” anything, that’s the whole point! If you have CR in the field (single field, single record), then that will be exported. You can calculate the CRLF with Char(13) & Char(10) instead of using the Pilcrow (¶), if that helps.
What are you using to view the created TEXT file? Could that possibly be converting what you have exported so that end-of-line is not “right” or appears as an extra line?
Yes, post the Calc if you wish.
Thanks for your quick response. I am viewing the result from my calc with Notepad++ and the string has the CR in the right places. But when FM exports, it doesn’t include an LF. Below is my calc to create the exported data. It is a continuous string but I broke it into lines so you can see the concatenation more easily. The spaces in the text in quotes are tabs that are required as delimiters; the Pilcrow is the EOL which is the issue because the receiver of the file requires a CRLF.
Again, thanks for your assistance.
“EASI 1.0 01 “&
” 01 “&
” MST P 997 ” &
“¶”& “01 997 2.0 01 ” &
” 01 “&
” ” &
” P ” &
” 01″ &
Again, thanks for your help. However, I found what appears to be a solutin by changing my concatenation to include ASCII characters rather than FM’s Pilcrow…
Excellent! Char(13) & Char(10) instead of the Pilcrow should do the trick! Opening the file in some text editors may still “convert” the eol, so don’t count on that for proof that the XSLT pushes out the *exact* field contents.
Thank you! Your article had saved me a lot of time! I wrote a script to save as merge file with a csv extension and it works beautifully! So Grateful!