Text to CSV export, what is your favourite approach?

I used a virtualList and exported from there, but I would have liked being able to just go with something closer to "export field content" with a global field.

That said, exporting FileMaker records instead of a text field, made it so I did not have to care so much about the contents of my data (quoting values, watching for carriage returns, commas, quotes and anything else I might forget).

I'm just curious about what people prefer to do in a similar situation. Thanks for your input.

1 Like

The way Export Records automatically handles escaping/quoting rules is awesome. It eliminates a lot of worry.

I think if the situation allowed for it, then I'd still prefer to export field contents or Write Data File like you said.

The escaping rules aren't too bad so a CF to escape values as needed, used in conjunction with Insert Calculated Result to build the csv data, is an attractive option to me.

Let ([
	~sub = Substitute ( value ;
		[ "\"" ; "\"\"" ]
	) ;
	~hasQuotes = value <> ~sub ;
	~hasComma = Position ( ~sub ; "," ; 1 ; 1 ) > 0 ;
	~hasReturn = Position ( ~sub ; Char(13) ; 1 ; 1 ) > 0 
		or Position ( ~sub ; Char(9) ; 1 ; 1 ) > 0 ;
	~quotesRequired = ~hasQuotes or ~hasComma or ~hasReturn
];
	If ( ~quotesRequired ; "\"" & ~sub & "\"" ; ~sub )
)

You could also play it safe (and maybe even get better performance) by always wrapping values in quotes and just escaping double quotes with another " in front. Then you could just ignore any linefeeds and commas in the values.

1 Like

There is also a piece that depends on what people are doing with it after the export. For example, if your users are going to double click it and want it to open with Excel, with the data in columns, sometimes you need add a piece of metadata to the file.

1 Like

Good point. I've experienced this too, usually only when I'm trying to open tab delimited (or other non-comma-delimited) data, in which case I've had to specify the delimiter at the top. I'm able to open a csv generated the way I mentioned above in excel via doubleclick without any special metadata, though.

I'm hopeful there are some documented cases where the metadata is required instead of just always guessing and testing, like I usually do. :man_facepalming:

I have a sample somewhere. Let me see if I can find it quick.

1 Like

When I Export Field Contents in one of my files, I have to add "Sep," at the beginning of the file to get it to open with columns separated. I assume it's an encoding thing, but I don't know. I just put the Separator indicator at the beginning and it works fine. lol... one day I will care enough to figure out why. But it's a utility file that only doesn't a task every few months for someone else.

When I manually look at the file, and resave it, it opens in Excel as I would expect.

Stores.csv - look at it in a text editor.
StoresPlain.csv - doesn't have the "Sep," text, but was also resaved using VS Code as a CSV.

As you can see here, they both open in Excel with the text in separate columns. On straight export, it does not open correctly.

StoresCSV.zip (2.2 KB)

2 Likes

Interesting. Something definitely got screwed up in the raw export from field version.

When I preview the file in Mac OS Finder I see a strange character combo at the start:

Screen Shot 2020-04-13 at 2.45.24 PM

And when I open that file in Excel, the first " character appears to have disappeared:

This is all ringing a bell, though I haven't encountered it lately. Thanks for showing that.

1 Like

A few other ways to get CSV from FileMaker.

  1. Interactively: You can use a third-party tool like razorsql or even an "IDE". Do a SQL statement on a FileMaker database (easy to set up using FMI's free JDBC driver) and then choose to export CSV.

  2. Programmatically with JDBC, and FMI's free JDBC driver, you could issue the same SQL you did interactively in "1." above, and just write simple CSV export code (examples all over the Internet....copy and paste code mostly).

  3. Programmatically with micro-service: POST the text to export to a little micro-service method you write (I've written tons of these so I could give you some sample code if you're interested) that writes the CSV any way you like.

Since I work with multiple environments, code I write generally is wanted by more than one software client like FMP. Thus, I gravitate toward "3." above. Also, 3. gives you a single point of update for 1 software client or for 10,000.

Creating CSV is a standard thing that has been solved many ways.

Post some sample data and I'll be happy to help! :slight_smile:

Happy coding!

In order of preference:

  1. Record export and let FileMaker handle the complexity for me;
  2. Use a calculation field to generate the record data then export that field in a tab-delimited file;
  3. Use a calculation field to generate the record data, collate the data then export the field data to file;
  4. Use a calculation field to generate the record data, collate the data then write the data to file.

I will skip 1 when I need a field separator other than comma.

A little more info on the CSV file format…

I've written CSV exporters and parsers in C. CSV rules are simple. They allow for a lot:

  1. The file contains only text;
  2. The field separator can be anything;
  3. The record separator can be CR, LF or CR-LF;
  4. Data NEEDS to be double-quote encapsulated ONLY if the field or record separator is present in the data.

There is no prescribed escaping mechanism. The most common escape mechanisms I know of is the doubling of double-quotes. I have also seen the C-style escape mechanism.

There is no allowance for non-record data, therefore no meta-data or field names. The fact some applications allow you to export field names as the first record can cause problems with importers that view all records as data.

Hope this helps.

1 Like

I think it also needs to be double-quote encapsulated if a double-quote exists in the data. The doublequote in data becomes "" and the whole value is then double-quote encapsulated as you said. But that and the presence of a field or record separator in the data are the only situations I'm aware of that require the double quote encapsulation.

No encapsulation is needed if double double-quotes exist in the data and the double double-quotes is not the only data. This assumes the most common escape mechanism is used by the importer.

1 Like

good to know! thx. I was just going based on RFC 4180, but as is often the case, reality and spec don't always perfectly align. (or I misunderstand the spec!)

  1. Fields containing line breaks (CRLF), double quotes, and commas
    should be enclosed in double-quotes.

https://tools.ietf.org/html/rfc4180

1 Like

You are basing yourself on the correct document. The keyword in the quote is SHOULD, not MUST. More importantly, this document is a memo, not a standard, as stated in its first paragraph:

"This memo provides information for the Internet community. It does not specify an Internet standard of any kind…"

My statement is based on parser logic. If I have the following lines…

DE""EF,1,2,abc,22923
""DEEF,1,2,abc,22923
DEEF"",1,2,abc,22923

I can logically infer in each case that the quotes are part of the data and not encapsulation markers.

If I have the following line:

"",1,2,abc,22923

Then there are two interpretations for the first field: the quotes are data, therefore the result is a single double-quote; the quotes are encapsulation markers, therefore the result is an empty value. The second one will likely win in most parsers.

1 Like

Would you please post a representative text example you'd like to export to CSV?

Hi @anon45965781 this is one row I got from the export using my virtual list. FileMaker is quoting every field. Obviously, I replaced a bunch of data to make sure I'm not posting customer data online. I'm not sure why you are asking the question, but here is the sequence (1st row may or may not be headers):

"10609","","Sample text","","4/11/2020","3/27/2020","","2/29/2020","3/26/2020","","3/28/2020","","","","Sample Name","1316","email@email.test","Contact Name: John Doe","(123) 456-7890","other@email.test","Entity","NULL","O400407","Some address","12345","Same Address","a street number and street","NULL","a city name","XX","12345","12345","Yet another address","street number and street","NULL","city name again","XX","12345","","what color is this","2","0","6","mySize","a product description","a product code","22.39","TONAL","This is another note","SOME-CODE-HERE","3letters","ALL CAPS HERE TOO","444","","3MoreLetters","","","","","",""

Also, please note, the text NULL you see in there is actual data (and not some representation of an empty string).

If this output is what you want and need, I didn't need to post anything. From all the discussion above, which got a bit academic, I wasn't sure.

Thanks,