Editor’s note: I am thrilled to feature a guest article by Beverly Voth.
In the article “An In-Depth Look at “Export Field Contents” (here), the study of how FileMaker exports text as single field contents is compared to the standard FileMaker Exports. Some of these present a problem when the field (exported) gets converted to something which a receiving system may reject as invalid. Several methods and alternative “fixes” are presented, including using XML & XSLT.
Update! NEW IN FMP 16 – we have a FIX!
There is a new function in FileMaker Pro (and Advanced) 16 that “fixes” a couple problems with Export Field Contents (namely the encoding and end-of-line). Instead of the Base64 nested function trick use the new Function:
TextEncode( text ; encoding ; lineEndings )
Reference: FM16 Help: TextEncode
Using TextEncode() & Export Field Contents
Set Field [ container ; TextEncode ( text ; encoding ; lineEndings ) ]
The container is any container field in your database, including global storage container fields. The text parameter is a field or variable which you have so carefully constructed into text as you want it. The encoding is the character encoding that is called “Output file character set” in the Specify Field Order dialog of standard Exports. The chart below specifies valid values for this parameter. The lineEndings allows you to pick how the “pilcrow” (¶, in the Calculation dialogs) or literal return-character is interpreted upon field export. The chart below specifies the valid values for this parameter, as well.
To get this Encoding: | Use this parameter (quoted): |
---|---|
Unicode | utf-8 |
Latin 1 | iso-8859-1 |
Cyrillic | windows-1251 |
Japanese | shift_jis |
Windows | windows-1252 |
Simplified Chinese | gb18030 |
Korean | euc-kr |
Traditional Chinese | big5 |
Mac Roman | macintosh |
To get this Line Ending: | Character(s): | Use this parameter: |
---|---|---|
Unchanged | (as entered) | 1 |
Carriage return (legacy Mac standard) | CR – Char(13) | 2 |
Line feed (modern Mac, Unix/Linux standard) | LF – Char(10) | 3 |
Carriage return followed by a line feed (Windows standard) | CRLF – Char(13) & Char(10) | 4 |
Your new scripting would be something like:
// set up your calculation into a TEXT field or variable Set Variable [ $carefullyCalculatedText ; <> ] // set up your path for export; something like: Set Variable [ $filepath ; Get ( TemporaryPath ) & "myfile.txt" ] // this replaces the Base64 nested functions: Set Field [ myTable::ExportFldContainer ; TextEncode ( $carefullyCalculatedText ; "utf-8" ; 1 ) // no change ] // this is the same, but we are Exporting the container field Export Field Contents [ myTable::ExportFldContainer ; $filepath ]
Notes & Tips:
- You may use a field (or variable) that is not calculated, such as user entered text, but it is still placed into a container before export with the new TextEncode() function.
- The $filepath is previously set as with all other exports.
- If you have an export where each “line” should end with “~” and CR, for example, then use the “1” parameter (unchanged) as you have calculated these values.
- Further testing is needed to see if the TAB (horizontal tab, ASCII 09) and other “invisible” characters are preserved with this function and upon export! The export with XML/XSLT may still be needed.
- Use ExecuteSQL() to create text that can be exported easily using the TextEncode() and Export Field Contents[]!
- Check out all the Container functions in FMP16 (changed or new)!
- If you want to keep the UTF-16 default for Export Field Contents, do not use the TextEncode, just export a text field from FMPro 16.
- REMEMBER that this is still only plain text (no formatting) exported.
- Containers still export ‘as is’, which is why this new conversion from Text to Container preserves the settings you specify.
Very helpful, Beverly!
Just note another caveat, and that is that Export Field Contents to export a container field didn’t work for me with FileMaker Server 16. I didn’t extensively test this so YMMV.
The specs say that the Script step to Export Field Contents does not work in FMS and with WD it is partial. YMMV, is correct.
Beverly
Thanks for this helpful tip!
Have you found a solution for exporting a text file on FM Server? I’m trying to write out some JSON data for another app to pick up & work on, and while Export Records returns no error when running PSOS, it doesn’t write the intended file either.
Wouldn’t that be sweet! Export Field Contents is not a Server compatible script step. But it’s a container, so how do you export any other container?
Beverly
Hi Beverly, I’ve been reading a lot of your export answers and docs, firstly, Thank You. Secondly, is there any way I can use the Export Records script step with TextEncode? I’m trying to output a found set of records to a tab file, but each record includes contents from close to 100 fields. I just want a tab file with Unix(LF) line breaks. Is it possible?
Jeannie, have you been reading my mind? LOL. And Thank YOU for reading my answers! Firstly, you’re welcome. Secondly, I don’t believe there is a way to change the end-of-line with any Export Records script step. With the exception of XML (with XSLT). You can get TEXT as a format where the tab delimits the fields and the LF (linefeed) is the end-of-line.
If you’d like to try that, I can work up a generic XSLT that would work with any number of fields. CAVEAT: there can be no TAB characters inside the field contents and any Carriage Returns inside field contents would not work with this method. If this is ok, let me know?!
Thank you Beverly. This solved a MAJOR problem for me today.
You are most welcome! Solves a lot of problems for me, too. If only we had more Compatibility on more products. In 18 & 19 the Files (script steps) take our “custom exports” to the next level, but has some warnings to use very carefully!