Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Export Records with CRLF on a Mac

Question asked by ncg1 on Mar 8, 2018
Latest reply on Feb 21, 2019 by beverly

Like • Show 0 Likes0
Comment • 38

I am using the Export Records script step to export a file of tab-separated values. These are payment instructions for my bank which has very specific requirements for the data format.

I'm using FileMaker 16 on current Mac OS, and the line breaks in the exported file are Mac OS 'Carriage Returns'. The bank's system doesn't see them: it only sees one long line of text and the import fails.

I am currently manually opening the exported file in a text editor and changing the line breaks to Carriage Return Line Feeds there.

How can I export this with CRLF's as the line breaks please?

comment Feb 20, 2019 4:39 PM

Correct Answer

Here is another SIMPLE method to export a tab-delimited file with CRLF line endings. Again, no calculation fields or scripts to collect data - all that's needed is a single global container field. Requires version 16 or higher.

Note: this uses Filemaker's native export to tab-delimited file, so no header will be generated automatically. But it is possible to add a custom header at the conversion stage.

See the reply in context

No one else had this question

Outcomes

38 Replies

Johan Hedman Mar 8, 2018 2:21 AM
Mark Correct
Correct Answer
You can use new functionality in FMP16 to export with CRLF.

https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Ftextencode.html%23

https://filemakerhacks.com/2017/05/09/export-field-contents-update-for-fm-16/

There is also a example file to pick up

GitHub : Genecom/FileMakerSampleSolutions: FileMaker サンプルソリューション
Like • Show 2 Likes2
Actions
JohnDCCIU Feb 15, 2019 1:15 PM
Mark Correct
Correct Answer
This is still a totally obnoxious time-wasting workaround using ExecuteSQL and a Container and TextEncode and blah blah blah for a decades-old bug in the Export Records script step. It's not a solution. :-(
Like • Show 0 Likes0
Actions
edwardlscott @ JohnDCCIU on Feb 15, 2019 3:05 PM
Mark Correct
Correct Answer
Can you prep the data prior to export by using the Substitute function to put the correct character in?

Substitute( ExportField ; char( 13 ) ; char( 13 ) & char( 10 ) )

char( 13 ) = Carriage Return
char( 10 ) = Line Feed

From Github:
Windows uses carriage return + line feed for newline:
```
\r\n 
```
Unix only uses Line feed for newline:
```
\n
```
1 person found this helpful
Like • Show 1 Like1
Actions
SteveMartino @ JohnDCCIU on Feb 15, 2019 3:06 PM
Mark Correct
Correct Answer
It's only a 'waste of time' setting it up for the first time. After that it's smooth sailing. And I didn't use ExecuteSQL or a Container, or TextEncode. Just looping and appending.

I do this practically everyday. I have to take my list of customers and put them on an SD card that's used in old ASCII based on-board truck computers for delivery.
Every record must be appended with CR LF, and all the customer fields have to padded to certain lengths with periods (.).
The script loops thru every record, concatenates them to specific format (padded to 512 bytes, 510 + CR LF), puts them on the SD card. 2500+ records takes less than 4 seconds.
At the end of the day, another script imports and parses the data from the card.

As a side note, the onboard computers are stable and basically bullet proof. Upgrading them is about 8K per truck, so I'm happy with this method.
1 person found this helpful
Like • Show 2 Likes2
Actions
JohnDCCIU Feb 15, 2019 3:13 PM
Mark Correct
Correct Answer
There are many workarounds for this lack of obvious and necessary functionality. I personally do it with
"do shell script \"perl -pi -e 's/\\\r\\\n|\\\n|\\\r/\\\r\\\n/g' /path/to/file/file.ext\""
But not the point. The point is this is an egregious and longstanding bug for which these kinds of silly and/or inefficient hacks should be completely unnecessary.
Like • Show 2 Likes2
Actions
ncg1 @ Johan Hedman on Feb 19, 2019 10:41 AM
Mark Correct
Correct Answer
Hi Johan,

This looks lovely - thank you - but it doesn't seem to work with Export Records, just with Export Field Contents.

Is there a way to either use it with Export Records, or to Export Records in to a field rather than a file, and then export the field using this function?
Like • Show 1 Like1
Actions
ncg1 @ edwardlscott on Feb 19, 2019 10:44 AM
Mark Correct
Correct Answer
Thank you Edward - super helpful. I agree it's CR/LF that I need.

I'm not sure I can amend the data in advance so it exports correctly as even if I add char(10) at the end of each line I will need to end up with char(13) then char(10) in that order on export.

I suppose I could add a false field containing char(10) at the front of the first field of each record other than the first line, but that feels a little silly.

TSGal - is there an approved way to do this please?
Like • Show 1 Like1
Actions
beverly @ ncg1 on Feb 19, 2019 10:54 AM
Mark Correct
Correct Answer
The Export Records (regardless of type) may not retain the values as you seem to have carefully placed them. Are you using calculation field(s) to get the desired characters?
Thanks,
Beverly
p.s. did you see the companion article: An In-Depth Look at “Export Field Contents” | FileMakerHacks
I'd like to update this if we can come up with something that works for you!
Like • Show 0 Likes0
Actions
comment Feb 19, 2019 12:36 PM
Mark Correct
Correct Answer
The attached demo shows how to export to a tab-delimited file with Windows (CRLF) line breaks.

No calculation fields or scripts to collect data are required for this.
Attachments
- Export TSV.zip70.3 KB
2 people found this helpful
Like • Show 0 Likes0
Actions
JohnDCCIU @ comment on Feb 19, 2019 1:11 PM
Mark Correct
Correct Answer
That's a very cool little hack!

I'm not a FileMaker XML export expert, but It seems to depend on a local XML file in the destination, so for it to run on any given machine, so you'd need to pre-write that XML file to the destination, correct? I guess that could be done by storing it in a Container field and doing an Export Field Contents beforehand?

Still way too much work for this decades-old flaw in FileMaker, but cool nonetheless.
Like • Show 0 Likes0
Actions
comment @ JohnDCCIU on Feb 19, 2019 1:29 PM
Mark Correct
Correct Answer
It's not a hack at all. Exporting as XML while applying an XSL transformation is an integral part of FMP since version 5.5 - and producing a text file as a result of the transformation is a perfectly legitimate and intended use of XSLT.

JohnDCCIU wrote:

It seems to depend on a local XML file
The XSLT file can be local or remote (accessible through HTTP). If you want, it can be stored in a container and exported to the temp folder before exporting the data.
Like • Show 2 Likes2
Actions
ncg1 @ comment on Feb 19, 2019 3:21 PM
Mark Correct
Correct Answer
OK - so thank you Michael for sharing a very elegant solution for a skilled coder who understands XML and XSL transformations. It's not going to work for me, as I'm not at that level, and I don't want to rely on code that I don't understand at the moment.

beverly - thank you. I found your update on Export Field Contents (Update for FM 16) | FileMakerHacks which suggested this:

// 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 ]

I have everything I want nicely set up in a table (that varies in number of rows), rather than in a text field. Is there an easy way to get the table contents in to your $carefullyCalculatedText variable?
Like • Show 0 Likes0
Actions
comment @ ncg1 on Feb 19, 2019 3:59 PM
Mark Correct
Correct Answer
ncg1 wrote:

I don't want to rely on code that I don't understand at the moment.
It's not that difficult to understand.
Like • Show 0 Likes0
Actions
ncg1 @ comment on Feb 19, 2019 5:23 PM
Mark Correct
Correct Answer
Sure, but the syntax is not natural for someone who's not a coder. It's a neat solution but not for me.
Like • Show 0 Likes0
Actions
comment @ ncg1 on Feb 19, 2019 6:05 PM
Mark Correct
Correct Answer
ncg1 wrote:

the syntax is not natural for someone who's not a coder.
Is xsl:for-each difficult to understand? Or xsl:value-of? Those are the two instructions that do almost all of the work here. And surely you don't find xsl:if or xsl:text "not natural"?

You do need to be at least superficially familiar with the structure of the raw XML that FileMaker produces as the input to the transformation. This is explained in the help and takes a minute or two to absorb. But if you prefer to fill your solution with junk that serves only someone else down the line, don't let me persuade you otherwise.
Like • Show 0 Likes0
Actions
beverly @ JohnDCCIU on Feb 19, 2019 6:56 PM
Mark Correct
Correct Answer
I'm replying, but I haven't checked the demo. One of my articles also uses an XSLT method, but that's mostly for the one-field export. I understand that this is something new and your reluctance to use it. Maybe when you're ready, you can post back another thread for help in using XML/XSLT?
Anyway, one of the reasons I wrote the articles, was because you are correct "this decades-old flaw in FileMaker" is needing some work-arounds. So I tried various methods to export that one field and make it comply with those other systems out there. And that's what you are running into, but need to use records exported. I'll reply more on your other question...
Beverly
Like • Show 0 Likes0
Actions
beverly @ ncg1 on Feb 19, 2019 7:01 PM
Mark Correct
Correct Answer
I have everything I want nicely set up in a table (that varies in number of rows), rather than in a text field. Is there an easy way to get the table contents in to your $carefullyCalculatedText variable?
"easy"? maybe. LOL
I just want a sample of your "record" and what it is you are exporting. Is it one field (many records), many fields (many records), and where do you place that CRLF (if at all)? For example, are you using calculated fields? Might any of your field contents have TAB or CR characters inside before any calculation for export?
There are ways to "gather" just as there are ways to "parse/split". But what you have may determine what you need to do with it!
Thank you,
Beverly
Like • Show 0 Likes0
Actions
user19752 @ comment on Feb 19, 2019 7:32 PM
Mark Correct
Correct Answer
It shouldn't be problem on this case since it should run on OSX, but on Windows there is bug (from 5.5) that exported with XSL results CRCRLF line break...sigh.

2 people found this helpful
Like • Show 0 Likes0
Actions
comment @ user19752 on Feb 20, 2019 12:22 AM
Mark Correct
Correct Answer
user19752 wrote:

on Windows there is bug (from 5.5) that exported with XSL results CRCRLF line break...
I've seen reports of this, but only a few. I suspect it only affects some Windows systems, not all.
Like • Show 0 Likes0
Actions
JohnDCCIU @ user19752 on Feb 20, 2019 2:02 AM
Mark Correct
Correct Answer
The Export Records script step and command in the Windows version of FileMaker writes CR+LF files just fine, so this macOS FM client bug is not an issue: you'd just use the Export Records script step and you're good to go.

Of course, if you needed to have a CR-only or LF-only output file from Windows, you'd have a similar problem, but a CR/LF-only file is not something that's ever needed, in my experience, as CR+LF long ago became (unfortunately and redundantly) the universal default format.
1 person found this helpful
Like • Show 0 Likes0
Actions
beverly @ JohnDCCIU on Feb 20, 2019 2:27 AM
Mark Correct
Correct Answer
Some apps don’t mind if you use CR, LF, or CR+LF. Unfortunately, there are systems which do need a precise end-of-line (and encoding). I see the TextEncode() with Export Field Contents as a good step. Now we need to be able to use those “features” with Export Records as well. :)
1 person found this helpful
Like • Show 1 Like1
Actions
beverly @ beverly on Feb 20, 2019 4:51 AM
Mark Correct
Correct Answer
So, MacOS here. Testing Export Records with this (tab-separated values):
The Output file character set is Windows (ANSI)
When I open with Text editor (BBEdit in this example) and look at the "end-of-line":
This is the LF! (not the CRLF and not the CR).
While this may or may not help. Consider calculating the final field to be content & Char(13) then export with this format. When I just tested, I got the dreaded upside down question mark where the "CR" was converted to VT (ASCII 11). And that is what tab-separated values export does (converts when you really don't want it to!). I still get the LF as the end of line, but NOT the CRLF. <== epic fail!
I'm thinking there has to be more. I'll work on the "get many rows" to one field, but still would like to see some "sample" of the data that may be exported.
Thanks,
Beverly
Like • Show 0 Likes0
Actions
comment @ beverly on Feb 20, 2019 5:08 AM
Mark Correct
Correct Answer
beverly wrote:

The Output file character set is Windows (ANSI)
The 'Output file character set' controls the file encoding, not the line-break type.

beverly wrote:

Consider calculating the final field to be content & Char(13) then export with this format.
You can't fool the system that way. When you export as tab-delimited, carriage return characters in a field are converted to vertical tab characters. You do want this, because otherwise the export would break up and become misaligned whenever a field contains a legitimate return.
Like • Show 0 Likes0
Actions
beverly @ comment on Feb 20, 2019 5:23 AM
Mark Correct
Correct Answer
The file I tested did not have the CR (as expected on the export), but the LF. It was just an interesting diversion that as I noted, FAILED! Nothing more, no challenges. My test, my results of it. I will delete the test if it offends anyone!
Like • Show 0 Likes0
Actions
ncg1 @ beverly on Feb 20, 2019 2:54 PM
Mark Correct
Correct Answer
beverly wrote:
I'll work on the "get many rows" to one field, but still would like to see some "sample" of the data that may be exported.
Hi Beverly,
Thank you for keeping looking at this! In my case the rows are transactions to be imported in to a business bank account. Here is some sample data:
SORT CODE BENEFICIARY NAME BENEFICIARY ACCOUNT REFERENCE AMOUNT
090128 Person Name 12345678 Reference 104.13
601101 Another Person or Company 12345678 Reference 184.66
There could be up to 100 rows or so, I guess. Thank you!
Like • Show 0 Likes0
Actions
beverly @ ncg1 on Feb 20, 2019 3:25 PM
Mark Correct
Correct Answer
Ok. Nothing special, just needing TAB between fields and CRLF at end-of-row.
One more question (2?)
1. Do you a need header row?
2. Do you have a scripted find for the records, or perhaps random/manual?
(Sorry on phone, so cannot reference previous replies if you posted a script.
TY for hanging in there!
Beverly
Sent from miPhone
Like • Show 0 Likes0
Actions
comment Feb 20, 2019 4:39 PM
Unmark Correct
Correct Answer
Here is another SIMPLE method to export a tab-delimited file with CRLF line endings. Again, no calculation fields or scripts to collect data - all that's needed is a single global container field. Requires version 16 or higher.

Note: this uses Filemaker's native export to tab-delimited file, so no header will be generated automatically. But it is possible to add a custom header at the conversion stage.
Attachments
- ExportTabCRLF.fmp12.zip68.2 KB
2 people found this helpful
Like • Show 2 Likes2
Actions
beverly @ comment on Feb 20, 2019 9:33 PM
Mark Correct
Correct Answer
Nice! If OP needs something, this "ExportRecords, ReImport (to container), manipulate, Export Field Contents" should be easy enough to follow.
Thanks for posting it.
Like • Show 1 Like1
Actions
JohnDCCIU @ comment on Feb 20, 2019 9:50 PM
Mark Correct
Correct Answer
Very elegant workaround, thanks!

FMI still needs to get their act together on the line ending options for the Export Records script step, but this is the next best thing.
Like • Show 1 Like1
Actions
beverly @ JohnDCCIU on Feb 20, 2019 10:04 PM
Mark Correct
Correct Answer
Ditto! a better Export (and Import) method to be able to choose:
1. end-of-line delimiter(s)
2. encoding
3, delimiters (between fields!)
4. handle CR and TAB (in field) by alternate characters, and/or don't alter on export
If you are still up for options (as we all learn from each other), an ExecuteSQL() (a find) might create the single field, single record for the one Export Field Contents. And I love that method to satisfy the criteria, above.
however...
Export Field Contents doesn't work on FM Server. <sigh/>
Beverly
Like • Show 0 Likes0
Actions
comment @ beverly on Feb 21, 2019 12:35 AM
Mark Correct
Correct Answer
beverly wrote:

1. end-of-line delimiter(s)
2. encoding
3, delimiters (between fields!)
4. handle CR and TAB (in field) by alternate characters, and/or don't alter on export
I agree with 1 to 3 being needed. Being able to choose the alternate characters would be nice. But not altering in-field delimiter character is not a legitimate request. The only purpose this could be used for is to fool the system. Let's fix the system so it doesn't need to be fooled.

--
P.S. How about:
5. choose to add header row
Like • Show 0 Likes0
Actions
ncg1 @ comment on Feb 21, 2019 2:58 AM
Mark Correct
Correct Answer
This is brilliant. Thank you Michael - and also thank you Beverly!
Like • Show 1 Like1
Actions
beverly @ comment on Feb 21, 2019 3:49 AM
Mark Correct
Correct Answer
Not fooling any system. But I wholly support your opinion and right to express it. There is no challenge here, just my desires based on decades of working with many DBs and systems.

Some places need TAB &/or CR preserved as is. Hopefully other characters are used as field delimiters and row delimiters, in these use cases!
This would be options I’d like to see in Export Records. (And possibly Import Records).
In fact, the one field one record XSLT relies on preserving all characters in the field exported. There are 'formats' other than TSV, CSV that a system may needed in its TEXT.
Even some clever ExecuteSQL() can be written to result in text with other than the default delimiters. It’s one of the cool features of it. Tho, again we are left with the single field single record for Export Field Contents. (No worries, if not used on FMServer & not using Plug-in) And windows users report problems with EFC as not being exactly as in Field. But this is too far off topic, maybe?
My list was to be in parity with other DBs (or apps) which have a means of setting custom delimiters, custom encoding, and returning all characters (as is).
For example, writing a 'command' to open a cash drawer on a register may need invisible ASCII characters. I would be at an impasse if I could not be given what is in a field (or fields & records).
Bar codes may need an invisible character (CR, perhaps?) and there may be a need to Export the carefully calculated visible and invisible TEXT/characters to somewhere else (as-is) for use.
These are just options that I have experienced when using FileMaker in the middle of two (or more) systems that otherwise fail to "communicate" with each other.
That we have TextEncode() and other revised container functions along with Export Field Contents is quite the blessing! You (any one following this thread) would not want to see the (horrible) methods in FileMaker 2.1, 3, etc. LOL
I believe plug-ins were devised as means to do things we could not otherwise.
But I/we would like the Import and Export Records to be a little beefier than now. So, yes, #4 stays on my wish list.
Until then XML/XSLT helps (somewhat), as do some very nice plug-in options or CWP (if needing to go that far).
Beverly
Sent from miPhone
Like • Show 0 Likes0
Actions
comment @ beverly on Feb 21, 2019 4:19 AM
Mark Correct
Correct Answer
beverly wrote:

Some places need TAB &/or CR preserved as is.
I am afraid you might be missing my point. If you selected a character as field/record delimiter, you cannot have it preserved in-field as well. That would break the export: the data after the delimiter character would be moved to the next column/record, and all the subsequent fields/records would become misaligned. This is why Filemaker converts tabs to spaces and carriage returns to vertical tabs when exporting as .tab natively. It cannot work without this.
Like • Show 1 Like1
Actions
beverly @ ncg1 on Feb 22, 2019 6:05 AM
Mark Correct
Correct Answer
Sorry if we took you down some paths that may not have helped! These forums are read by many "lurkers" who may also learn, so sometimes we just make various suggestions. And, even old dogs can be taught new tricks...
Beverly
Like • Show 1 Like1
Actions
beverly @ comment on Feb 21, 2019 6:57 AM
Mark Correct
Correct Answer
I got your point. I'm not sure you got mine. But let's just move on?
Like • Show 0 Likes0
Actions
user19752 @ comment on Feb 21, 2019 8:12 PM
Mark Correct
Correct Answer
Other software can implement other ways. PostgreSQL allow line break as is in CSV file.
PostgreSQL: Documentation: 11: COPY
CSV format will both recognize and produce CSV files with quoted values containing embedded carriage returns and line feeds. Thus the files are not strictly one line per table row like text-format files.
So it is good thing if FM can export such file.
1 person found this helpful
Like • Show 0 Likes0
Actions
comment @ user19752 on Feb 22, 2019 12:18 AM
Mark Correct
Correct Answer
Not sure what your point is. Line breaks in a .csv file are perfectly legitimate, as long as the field is surrounded by quotes. I don't know why Filemaker chooses to replace them - I believe it's a mistake. Fact is that commas in fields are NOT replaced - again, for the same reason that they can be distinguished from commas that serve as separators. But if you wanted an option to NOT escape the quotes in fields, that would not be a legitimate request.

BTW, Filemaker has no problem importing a .csv file with in-field line breaks - showing again that the limitation on export is redundant.
Like • Show 0 Likes0
Actions

Export Records with CRLF on a Mac

Outcomes

Attachments

Attachments

Related Content

Recommended Content