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.

Why do we keep using CSV?

I recently made a shift to working the health domain and despite the wonderful work in data transfer standards, all data transfer is in CSV, both for reporting to external organisations, and for data migrations when implementing new systems.

Unfortunately the use of CSV is the cause of the endless repetition of the same stupid errors, with the same waste of developer time. (bad escaping, failing to handle null fields etc.)

I know we can do better, and anything between JSON and XML (depending on the instance) would be fine. (Most of the time this is data going from one MS SQLserver 2005 to another!)

I feel as if each time I see this happening I am literally watching one developer waste anothers time.

So why do we keep shafting each other? When will we stop?

share|improve this question
14  
If you're just getting into the health domain and you think CSV is bad... just wait until you run into HL7! –  Greg Feb 14 '11 at 21:46
2  
@Greg LOL, don't frighten him, the surprise is always best :) –  James Love Feb 14 '11 at 21:48
39  
-1 This is an anti-CSV rant against problems not caused by CSV. What exactly do you think would happen if you read and wrote XML without a library? Your problems would be a hundred times worse. –  Jesse Millikan Feb 14 '11 at 21:52
11  
"So why do we keep shafting each other? When will we stop?" I dunno, where I work we manage to use CSV just fine without anyone getting shafted (indeed - it's the XML stage that's by far more frustrating). Maybe you and your coworkers are doing something wrong? –  FrustratedWithFormsDesigner Feb 14 '11 at 21:56
3  
All of the discussion so far misses a very real problem with CSV: the delimiter character is likely to appear in the data, and CSV takes a less-than-optimal approach to that issue (putting quotes around the data just pushes the problem downstream). A better approach would be to use pipe-delimited files. –  Larry Coleman Feb 14 '11 at 22:59
show 14 more comments

13 Answers

up vote 8 down vote accepted

In your case, it seems that CSV isn't a good fit due to it's lack of hard specification.

For non-trivial data it's not the right choice.

Why / When is CSV a good choice? Probably too many instances to mention, the benefits of simplicity for flat data are obvious. As long as the data is sanitized / escaped properly there are no problems. Generally speaking though, all these cases would be simple / trivial. Of course, the standard delimiter appearing in the content is often a pain when dealing with CSV.

But if you're doing something more involved than getting a non-technical client to send data from an Excel sheet or some other similar use-case, then CSV is probably insufficient for any serious use.

XML is a far better fit (yes even more so than JSON) since you are able to do detailed standardised schema specification for it. (Not to mention that specs/schemas enjoy the flexibility of multiple implementation styles, XSD, DTD & Relax NG)

For closed loop systems, especially where bandwidth is a concern, JSON can be a better fit than XML, but the lack of schema specification language(s) often precludes it from enterprise level applications.

share|improve this answer
3  
Indeed "As long as the data is sanitized / escaped properly". However way to many programmers seem to be able to get this wrong, writing their own (in pseudo-code write('"');write(fld1);write('"'); ad nauseum.) Then they miss putting quotes around something. Then they write their own parser.... –  Gerry Feb 15 '11 at 10:22
2  
Yep, the roll-your-own crew really should start using this internet thingy, maybe even learn the meaning of the word ... Library. –  Slomojo Feb 15 '11 at 10:24
    
sharing information! re-usable code! stupid new-fangled ideas. Repeating other peoples mistakes was good enough for my great^50 grandfather, and it's good enough for me! –  Steve314 Feb 15 '11 at 20:32
    
@Steve314 - /me "makes a face of both horror and amusement." –  Slomojo Feb 15 '11 at 21:28
add comment

My theory is that specifications are written by managers who last touched something technical many, many years ago. I raised this point with my manager a while back, suggesting XML would be a better implementation. He told me "XML was too difficult" !! But several Mb of undocumented CSV was OK........

share|improve this answer
add comment

why do I use it?

  1. the customer wants it
  2. it's faster than xml over the network (smaller network load)
  3. nothing more complex is needed to get the data across
  4. cross platform
  5. human readable
  6. easy to implement readers and writers for it

etc. etc.

share|improve this answer
add comment

I've seen a lot of technical answers but I suspect the reason people use CSV is the same reason people use a lot of other techniques/technologies: because it's the one they're the most familiar with

share|improve this answer
add comment

It really is all about mainframes and excel here.

Mainframes because those old systems figured out how to communicate using CSV. So the big apps that dump the data can read and write it and have no reason to change now.

Excel because it can open CSVs directly. In fact, it takes over the .csv extension when you install it. Users just click the slightly funny looking excel icon and it opens and makes a nice grid they can wrangle with.

Now, modern versions of excel are quite capable of reading, say, XML, directly. But to do so, a user has to understand a bit more that "double click on that picture." And double clicking on the right picture can be too much to ask in some industries . . .

share|improve this answer
add comment

If there is one thing wrong with CSV, it is that CSV appears so simple that many developer try to invent their own parsers/writers and later on blame CSV for not handling escaping correctly. With a good CSV parser (many good one out there), there will be no problem at all.

Some one mentioned CSV is not good for non-trivial data but I don't agree. XML allows non-trivial data because different data set can be put in different "container" tags. With CSV, you can always put different data in different files to achieve the same effect.

Further, in my opinion, using XML for data transfer fundamentally goes against the purpose of XML - data transfer usually implies a stable contract between providers and consumers while XML is meant to carry expandable information subject to interpretation when it is consumed.

share|improve this answer
add comment

Because a lot of analysts use Excel (for pivot tables and such), and it's a lot easier to output CSV than to output native Excel format.

Footnote: given how many problems I've seen with Excel handling CSV files, like removing leading zeroes and losing precision, this is probably a false sense of being easier.

share|improve this answer
add comment

I guess CSV are just good when you only have simple text data, with only commas and either semicolon/endline at the end.

Tree architectured data or composited data can hardly be used with CSV.

CSV is just a plain 2D array of text as in excel, nothing much...

share|improve this answer
add comment

First, I agree that there are some very real problems with the format:

  • It's stringly typed.
    • With no distinction between text and numeric values, Excel will guess wrong and screw up your postal codes and credit card numbers.
    • There's no standard way to represent binary data.
    • There's no standard way to distinguish between NULL and '', which is a problem when importing CSV files into SQL databases.
  • Poor support for "special characters".
    • The lack of numeric character references like (XML &#xNNNN; or JSON \uNNNN) means there is no standard way to represent control characters or non-ASCII characters.
    • Many implementations do not properly implement line breaks within a field.
  • The lack of a standard. There's RFC 4180, but it's not universally followed.

But on the other hand:

  • The alternatives are worse. JSON and XML, being designed around trees, are a poor fit for table-based data, specifically in terms of...
  • COMPACTNESS! In XML, you have to have a start tag and and end tag for each column in each row. In CSV, you only write the column headers once.
  • CSV is very easy to generate.
  • Non-programmers can open CSV files in Excel.
share|improve this answer
    
in reverse; using this data in excel would be a sackable offence, CSV is easy to generate badly, compactness is not a problem, trees are a better fit for this data. –  Stephen Feb 16 '11 at 13:28
add comment

Let me throw out a few points in favor of CSV:

  • CSV is simple(r than any alternative suggested in OP) to implement and parse
  • CSV is understood by almost every piece of software on the planet (past and present)
  • CSV forces a fairly flat, simple schema (there is a single flat list of fields)
  • CSV is more human-readable than XML, JSON, or (UGH!) HL7 (V2.x, pre-xml)
share|improve this answer
10  
+1 for human readable. It's idiot proof. –  user2567 Feb 14 '11 at 21:45
13  
You dont have to play 'devils advocate'... all those points you make are completely valid and explain why CSV is still used. Its just plain simpler. –  GrandmasterB Feb 14 '11 at 21:45
7  
@Stephen: How many different variations of CSV do you know? –  FrustratedWithFormsDesigner Feb 14 '11 at 22:01
3  
@FrustratedWithFormsDesigner how many escaping conventions can you think of? –  Stephen Feb 14 '11 at 22:36
7  
@Pierre303, idiot proof... If you think you've 'idiot proofed' something, you haven't tested it with enough idiots. –  Slomojo Feb 15 '11 at 10:28
show 12 more comments

First and foremost, because even though consuming CSV data can be (slightly) non-trivial, generating it is extremely easy.

I'd also point out that neither JSON nor XML is really easier to get right (for either the producer or the consumer). In fact, one barely has to look around at all to know that lots of people try to use regexes to parse XML data, even though there's absolutely no question that doing so cannot and will not work.

Most of the problems that can (and do) arise with CSV can (and do) also arise with both JSON and XML. XML, in particular, adds many more potential problems of its own. A library to parse XML data is generally larger, slower, and more difficult to use than a similar library for CSV data.

share|improve this answer
1  
appearing to produce it correctly is extremely easy, consuming something that lacks a spec is non-trivial when you have non-trivial data. –  Stephen Feb 14 '11 at 23:01
1  
@Stephen: note that I did not include "correctly" in that first sentence. Its omission was intentional! –  Jerry Coffin Feb 14 '11 at 23:11
add comment

CSV is a bit faster, smaller in size, very easy to handle (even in Excel) and many existing applications understand it, it is a widely used standard.

It is still a first choice in many situations.

I personally still like that format a lot. But I use JSON too, but for other applications like web UI.

share|improve this answer
1  
I agree with every bit of this, except the initial use of "a bit". –  Orbling Feb 14 '11 at 22:04
3  
It can be an absolute bas***rd with Excel if you have data that needs to retain leading zeros.... ask me how I know! ... other than that Excel provides a good interface. –  Dal Feb 15 '11 at 0:21
    
@Dal: I used to work at a credit union, and had to deal with CSV files that contained credit card numbers. Which have 16 digits. That Excel rounded to 15. –  dan04 Feb 15 '11 at 1:05
    
Or worse that it converted them to scientific notation. :( I remember the first time I got an error back on our ACH processing that a remote account number was invalid, only to find out someone had edited the csv in excel (only to remove a row) and it had changed a bunch of 30 digit account numbers into 2.3456356e29 and such. –  cabbey Feb 15 '11 at 4:06
    
Oh, yes, scientific notation. I've gotten some interesting results with alphanumeric customer IDs that happened to contain Es. –  dan04 Feb 15 '11 at 4:41
show 2 more comments

Backwards compatibility. If your external orgs web service handles CSV, and all your existing tools handle CSV, neither party has any motivation to move to a new service. Why would your external org start supporting a different format? No-one they work with can use it! Why would you start producing a different format? None of the organizations you work with accept it!

The real issue I see here is, why are your developers rolling their own CSV code every time? If they used a stable, rock-solid CSV library, they wouldn't have the issues you describe. The problems are caused by developers rolling their own solution instead of using a library, and I honestly don't see how moving to JSON or XML magically fixes that. You'd still have people trying to regex them up instead of using a library.

share|improve this answer
4  
+1 for rolling-their-own every time. I see developers who don't learn, not a flawed data format. :-) –  Greg Feb 14 '11 at 21:44
    
'backwards compatibility' - you are right of course - but not moving forward is costing thousands. –  Stephen Feb 14 '11 at 21:46
    
Its fine to roll your own CSV library... just re-use it! –  GrandmasterB Feb 14 '11 at 21:46
5  
@Stephen: No, reimplementing CSV every time you need it is costing thousands. CSV as a format is fine, the developers who can't get it right are the problem. –  Anon. Feb 14 '11 at 21:49
6  
@Stephen: So your problem with CSV is that it's too simple and you want something more complex? –  Anon. Feb 14 '11 at 21:53
show 5 more comments

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.