Alternatives to Tab Delimited or CSVs

[Michael_McGowa1]

I am looking for suggestions on the best way to do the following.

We want to run an essay contest where a member can go to a Marketo landing page with a form to fill out their information, copy and paste their essay into a field in the form.

In my testing the essay field is a text area and since we are limiting the essay to 800 words, it should handle the 30,000 character maximum.

The form works, the problem is downloading the list of people with their essays. This list is created by a smart list. When downloading the list we are limited to a tab delimited or a csv. When I open it the essays will not be contained in their cell because either a carriage return is recognized as a tab or a comma in the essay moves the copy into a cell.

Is there a better way to go about this? Is using a text area to hold the essay the best field to use? Is there another option for downloading a list besides tab delimited or csv?

[SanfordWhiteman]

A textarea should be a quoted string value, so commas within the value will not disrupt CSV parsing (this is an official standard for the CSV format).

What app are you opening the CSV in? Can you show the raw text of a row with commas within a value?

[Michael_McGowa1]

I am using Excel to open the CSV file. I have found that if I edit the view and have the field holding the essay last, it causes fewer errors.

[SanfordWhiteman]

I'd like to see the raw CSV output here. If the string is not quoted that's a bug (and would of course apply to short values as well as relatively long ones).

[Michael_McGowa1]

Attached is the file I am importing into an Excel spreadsheet. Also attached is a screenshot of what I am seeing.

[SanfordWhiteman]

OK, the CSV file is formatted correctly -- it's a matter of your reader (Excel) not understanding how to parse it.

When I open it in my CSV reader (Ron's Editor -- a best-in-class piece of software regardless of its old-timey design!) it's parsed correctly:

And when I export from Ron's to Excel format it preserves correctly:

So your approach should be to use Ron's or a comparable editor first because those apps are better equipped to understand the original format.

[SanfordWhiteman]

Michael, d'you know what else? I don't even have the problem you seem to have in Excel with that file (tested in Excel 2007 and 2013, both for Windows).

Here's 2013:

How are you opening the file?  Since you have the DATA tab selected, I wonder if you're trying to import it instead of simply open it?

[SanfordWhiteman]

BTW the relevant part of the CSV standard is here in RFC 4180 Section 2.6​.

It's notable that enclosing line breaks in quotes is the only official way to escape breaks in a line-oriented format.

That it's official doesn't mean, of course, that it's always supported, as you've seen by trying to use Excel as a reader.  But you might be tempted to think another familiar form of escaping is valid --

"field 1","field 2","a field with a \r\n line break"

-- but it would be up to the app to decide that \r\n (or ASCII-specific \x0d\x0a or whatever) should be turned into the control characters for a line break. But that's not in the standard at all! As familiar as that style is (it works in languages like C, Java, and JavaScript) it's not truly universal. In other contexts -- HTML being a great example -- it just means "backslash, letter r, backslash, letter n" and it won't be turned into anything else.

[Michael_McGowa1]

Thanks Sanford. Much appreciated.