Exporting records as ascii text

[James Gill]

I am attempting to perform an export from my Filemaker database into Laboratory Information System and have been asked to provide my records as ascii encoded, delimited text.

 

When I perform the export on my end, everything appears ok, but apparently the records need to have a carriage return at the end of each record.  My original attempt was to create a Unstored Calculated field that placed a "|" between each field and then export those records as a tab-separated file and encode it as an MSDOS (ASCII) file.

 

So the field I exported was a calculation that consisted of:

 

field1 & "|" & field2 & "|" & field3

 

However, I was told that I needed to add an explicit carriage return at the end of each field, so I tried:

 

field1 & "|" & field2 & "|" & field3 & "¶" 

 

 

All that does is to add an additional carriage return to the document itself, resulting in a blank line of text between each record.  I'm confused here, as I thought that records exported by filemaker were automatically separated by a carriage return (although I'm probably wrong, as Export Records As only includes options for Tab and Comma separated values...)

 

So, is there any way for me to replace Filemakers record delimiter with the ASCII equivalent of "¶" ?

 

I've attached example files of both my export and their export so that you can hopefully see what I'm referring to.their export.txtmy export.txt

 

[Vaughan]

Export as csv or tab delimited text. These formats use carriage returns to delimit rows (records). The commas and tabs are used to delimit field values.

[James Gill]

Export as csv or tab delimited text. These formats use carriage returns to delimit rows (records). The commas and tabs are used to delimit field values.

 

That's what I originally thought as well.  However, their technical point of contact replied to my file with this, "What are you using as a record delimiter, I see an ascii (-1)

Can you change it to a CR LF"

 

To me, that is moon speak....

[MartieH]

Pretty sure that is ascii Line Feed which is generated on a keyboard by Control J   ^J

[James Gill]

When I hit control+j it results in a beep but nothing displays

[James Gill]

Ok, I've narrowed this down to Filemaker replacing my ASCII "line feed" character with a different value.  I found a program (Peep) that allows me to view the ASCII values of the exported text and have also found an ASCII table to show me what represents each character decimal value.

 

Taking my default calculation and exporting it as a .tab file and encoding it as ASCII (MS DOS), I can see that each record is delimited with a carriage return (ASCII value of "OD", decimal value of 13).  This matches up with what I would expect to see.

 

Because the technical POC has specified that each record needs to end with a "CR LF", I assume that this means I need each record to end with a carriage return and then a line feed.  The ASCII reference table I found specifies that the LF character decimal value is 10.  Using the built in Char function, I should be able to append a Char (10) to each record before it is exported.  In fact, viewing the calculation in the Data Viewer and then pasting into Peep results in the last character value of the line displaying as "10".  Knowing that Filemaker will automatically place a ASCII "13" for each record, I should be fine.

 

However, when I actually export the data and then view it inside of Peep, the Char (10) value has been replaced with Char (11) but the carriage return remains where it should be.  Suggestions?

[Fitch]

I'd have a look at the XML solution proposed here:

http://fmforums.com/forum/topic/60600-export-with-line-feed-characters/

 

...and written up very nicely here:
http://www.filemakerhacks.com/?p=6045

 

Or you could explore text conversion utilities:
http://stackoverflow.com/questions/1552749/difference-between-cr-lf-lf-and-cr-line-break-types

 

Or possibly write to a text file with a plugin:
https://github.com/nickorr/BaseElements-Plugin/wiki

 

And just for fun:
http://www.codinghorror.com/blog/2010/01/the-great-newline-schism.html

[Vaughan]

Ok, I've narrowed this down to Filemaker replacing my ASCII "line feed" character with a different value.  I found a program (Peep) that allows me to view the ASCII values of the exported text and have also found an ASCII table to show me what represents each character decimal value.

 

There is an option in the dialog to export as macintosh or windows: choose windows.

[James Gill]

There is an option in the dialog to export as macintosh or windows: choose windows.

 

Export as Windows (ANSI) or DOS (ASCII)?  Well...either way, neither of them add the actual "CR LF" I need.  I guess I'll have to give the suggestion that Fitch made a go.  Thanks though!