Re: Alternatives to Tab Delimited or CSVs

Michael_McGowa1
Level 3

Alternatives to Tab Delimited or CSVs

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?

8 REPLIES 8
SanfordWhiteman
Level 10 - Community Moderator

Re: Alternatives to Tab Delimited or CSVs

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
Level 3

Re: Alternatives to Tab Delimited or CSVs

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
Level 10 - Community Moderator

Re: Alternatives to Tab Delimited or CSVs

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
Level 3

Re: Alternatives to Tab Delimited or CSVs

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

SanfordWhiteman
Level 10 - Community Moderator

Re: Alternatives to Tab Delimited or CSVs

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:

pastedImage_0.png

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

pastedImage_1.png

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
Level 10 - Community Moderator

Re: Alternatives to Tab Delimited or CSVs

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:

pastedImage_0.png

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
Level 10 - Community Moderator

Re: Alternatives to Tab Delimited or CSVs

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
Level 3

Re: Alternatives to Tab Delimited or CSVs

Thanks Sanford. Much appreciated.