Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

So typically a CSV file uses a comma and the return character as it's field and line delimiters.

This runs into obvious problems with text which can contain both these characters.

Obviously there are options there (escaping them) but how do people handle this? Use different characters - pipes or tildas? Escape them? Not use delimited files, after all it's 2010 and we have XML now?

Looking at least effort for a decent chance of not seeing problems.

(Just to be clear, this is a question out of curiosity rather than something more solid - it's something I've come up against time and time again playing with data, always got round it but it normally feels a bit, well, dirty, and wondered what other people's experience was).

share|improve this question
    
+1 Common problem and good question. –  Tim Post Dec 14 '10 at 12:12
    
Think carefully about using CSV - it's nice and easy to deal with (see answers for common escaping rules), but it's not nearly as inter operable as it should be - if you're just communicating with your own programs it's fine, but if you want to import elsewhere it gets a bit odd because different programs obey different escape rules. –  Michael Kohne Dec 14 '10 at 12:15
    
@Michael - Absolutely. The issue though is that it's so omnipresent that you'll almost always come up with times when it's a very tempting option, and in the case of many older systems it's the only option. –  Jon Hopkins Dec 14 '10 at 12:18
    
Mature libraries exist in many languages (certainly the common ones) for reading and writing character delimited files. They will handle most any situation. Writing one's own CSV parser seems to be a common sort of anti-pattern. –  qes Dec 14 '10 at 16:44
add comment

7 Answers

up vote 10 down vote accepted

According to Wikipedia:

Fields with embedded commas must be enclosed within double-quote characters.

And furthermore:

Fields with embedded double-quote characters must be enclosed within double-quote characters, and each of the embedded double-quote characters must be represented by a pair of double-quote characters.

I don't know who invented that, but it effectively shows that eventually you have to escape. It's the only solid solution. Everything else is just duct tape on top of duct tape: maybe works for now, but eventually you'll bump on a case where you need an exception to the exception of an exception, and it doesn't take long before your mudball of rules is way more complex than a simple escape character solution had been.

It seems that CSV creators first tried to avoid escaping commas by coming up with double-quoted special syntax, which allowed saving commas, but then someone wanted to save double-quote characters too, so they had to escape at that point - funnily using the double-quote as an escape character. Had they decided to escape properly in the first place, the syntax would be simpler now.

share|improve this answer
3  
What should be, and what is .. often differs :) –  Tim Post Dec 14 '10 at 12:19
    
I think the solution is quite ok. For simple data, CSV works fine, for complex data then quoting is necesary, and escaping " with "" traces back to BASIC. –  Ernelli Dec 14 '10 at 12:30
1  
@Ernelli: Now that I think about it more, it may actually be a reasonable compromise between human-readability and simplicity. The problem escaping is that it looks ugly to humans, even though it's trivial for the computer to parse. Thus, reserving escapes for rare cases only ("fields with embedded double-quote characters") produces output that usually looks quite human-readable. This is a good solution, assuming that commas in field names are more frequently used than double-quotes in field names. –  Joonas Pulakka Dec 14 '10 at 12:35
add comment

I'm assuming you have something like this:

Foo,Baz,,,"Foo,Baz"

If strings that contain the delimiter aren't quoted or escaped, you have no real reliable way of parsing the file.

You can, however examine the data to parse and draw conclusions like:

  • Comma separated floats should be treated as a string
  • If the line before or after this contains less delimiters, skip parsing this line and log it
  • Treat ' like "

You do have to write a parser to handle stuff like that, but it doesn't have to be complicated.

In my experience, importing massive dumps from something like Excel always results in having to go back and review some oddballs. Your challenge is to give your program just enough common sense regarding the data so that it doesn't do a crazy insert. Then review what was logged and wash/rinse/repeat.

I once handled an internal FAQ for a small company that used all Ubuntu workstations. A part of the FAQ gave 'shell shortcuts', and it came to me pipe delimited. Well, the answers were also typically pipe delimited (i.e. grep foo | something) and not quoted or escaped. I feel that pain :)

share|improve this answer
add comment

Nothing wrong with CSV up to a point

CSV works well for rigidly defined data that is unlikely to change format and doesn't spring many surprises on the recipient parser.

Here's a handy list of the big gotchas:

  1. Escaping ""s within ""s (field contains field delimiter)
  2. ""s containing CRLFs (field contains line delimiter)
  3. Unicode (underlying text format may be insufficient)
  4. Different line terminators for different OSes (is CR or CRLF or LF or NUL?)
  5. Inline comments (line prefixed with #, //, --, ; etc)
  6. Version management (the latest version of the file contains more or less fields)
  7. Differentiating between NULL and empty data (,"", is empty but ,, is null?)

You could approach this with a meta-data header that describes how the fields should be parsed, but then you may as well just use XML. It's because of this sort of freeform CSV mess that it was invented. The XML approach just seems too heavyweight for what could, on the face of it, be a simple problem.

A popular alternative is the "weird character delimiter" strategy. This gets around a lot of the escaping issues above because you use something like a | (pipe) character for field delimiting, and a CRLF for record termination. This doesn't get around the multi-line field issue (unless you use a field counter) but you do get nicely formatted lines for humans.

Overall, if you're just looking for a simple way of handling this kind of file then, in the Java world, you could simply throw OpenCSV at it. That way you abstract away all of the problems into an established framework.

share|improve this answer
add comment

CSV is still a valid format in many situations, especially since it's still got to be the easiest way for a customer to write data that needs to be imported into your application. Few of our customers like to deal with XML, perhaps because it's very verbose and has all of those "scary" angle brackets. It's so much simpler for them to wrap their brains around a simple list of items separated by an agreed upon character, and also agree that the same character will not be allowed in the contents of a field.

That said, you still have to handle the input correctly and check for situations where they use invalid characters. I've started to use FileHelpers for my CSV parsing needs.

share|improve this answer
add comment

i usualy stick to the standard and escape them. in most programming languages there is good builtin support or a good library available.

it depends on the situation which format will be used and CSV is a reasonable format to exchange simple data format structures.

share|improve this answer
add comment

Forget CSV, use JSON. Easy to write, easy to parse. XML is so 2005.

share|improve this answer
6  
and has the same problem when you want to use a character that's part of the JSON format (like { or ,) –  Salandur Dec 14 '10 at 11:59
    
Salandur: Not at all! There are exact rules how to escape! But { and , don't even need to be escaped, because inside are string, they are not ambiguous! –  user281377 Dec 14 '10 at 12:05
1  
Well and good, but I don't recall excel having an "Export to JSON" feature :) There are times when you have to parse odd things, if only to get them into a more agreeable format. –  Tim Post Dec 14 '10 at 12:25
1  
And JSON is just so totally brilliant for passing around a million objects of the same shape. Oh, wait. –  Frank Shearar Dec 14 '10 at 12:27
1  
JSON offers no improvement over CSV in regarding to this question and crucially lacks interoperability with many applications (as has been mentioned, can't import or export from Office, SQL DBs etc). JSON is great for internal, light-weight client-side operations but XML is much better for passing data between applications. –  Dan Diplo Dec 14 '10 at 12:37
show 4 more comments

Usually, what I find myself doing is getting a TSV (tab-separated values) rather than a CSV file, pull the file into Emacs and see which of a few uncommon characters it NEVER uses ($ is usually a good choice around here), and then I convert all the tabs to $.

From there, GNU AWK can be told to use $ as the field separator, and Bob's your uncle.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.