An In-Depth Look at “Export Field Contents”

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.

output_file_char_set

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.

Tab-Separated Text

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.

Comma-Separated Text
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.

comma-separated_txt

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.

Merge Type export
The top row of field names is also comma-separated (but not quoted), if more than one field is exported.
merge_txt

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.

HTML Table

This give us this text. Note: the carriage returns appear to be preserved by being converted to the HTML tag “<BR>”:
HTML Table export result
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.

XML type text export 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.
Specify XML and XSLT Options dialog
What you get will be similar to this. Notice that the returns-in-field are preserved.

Raw XML export as text

As shown in BBEdit

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.

XML with default stylesheet in browser

XML as seen in FireFox browser with Default style sheet applied

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!)

XSLT for exporting one record, one field

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.

xml_transformed

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

25 thoughts on “An In-Depth Look at “Export Field Contents”

  1. Kevin Frank

    Beverly writes:
    > 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.

    Reply
    1. Beverly Voth

      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.

  2. Doug Alder

    Hi Beverly,

    Again, thanks for this great followup. I tried the XSLT route in my solution, but I am getting an error message.

    Specifically:

    “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).

    Thanks,

    Doug

    Reply
    1. Beverly Voth

      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.

  3. Angus Cameron

    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:

    2.0

    HMRC-PAYE-RTI-FPS
    request
    submit

    XML
    1

    ISV635

    clear
    principal
    testing1

    635
    A635

    your 4 digit vendor ID
    your product name
    version number of your product

    Any help you can give me would be much appreciated.

    Reply
    1. Beverly Voth

      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?

  4. Angus Cameron

    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.

    Reply
    1. Beverly Voth

      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?

    2. Ben Kreunen

      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.

    3. Beverly Voth

      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)?
      Thanks!

    1. Beverly Voth

      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=""

  5. Mauro

    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.

    Reply
    1. Beverly Voth

      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.

  6. Eric Matthews

    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.

    Reply
    1. beverlyvoth Post author

      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.

    2. Eric Matthews

      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)
      150KB, 02:31
      300KB, 18:46
      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

  7. Eric Matthews

    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.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s