Explored. Designed. Deliveredsm
The Comma Separated Value (CSV) File Format
Create or parse data in this popular pseudo-standard format
The CSV ("Comma Separated Value") file format is often used to exchange data between disparate applications. The file format, as it is used in Microsoft Excel, has become a pseudo standard throughout the industry, even among non-Microsoft platforms.
As is the case with most exchange formats since XML, CSV files have become somewhat of a legacy format. New applications that wish to include an export format will generally use XML today (though there may be exceptions). In legacy systems though (pre-XML), CSV files had indeed become a de facto industry standard. Just as there are still billions of lines of CoBOL code in use today that need to be maintained, support for a legacy standard such as CSV is likely to be required long after it has stopped being implemented in new designs.
The CSV File Format
Here is a small set of records that demonstrate some of the constructs discussed above. These can be pasted directly into the form provided in the next section to see how our conversion form works.
CSV & Unicode
Just to make the point, CSV using a simple ASCII character encoding is quite capable of enclosing pure binary data fields. In this case, as long as you don't have any application level software making assumptions about the data in those fields, CSV fields can safely contain all 256 binary octets as basic binary data. The binary data may represent utf-16 characters, or it may represent a photograph. CSV doesn't care.
So there is nothing inherently wrong with using CSV to maintain data written in alternate character encodings such as utf-8 and utf-16. CSV's syntax is durable enough to deal with these encoding schemes. Problems arise however in two areas when attempting to transport data of different encodings as plain binary.
First, complexity can arise from mixed encoding schemes. That is, the encodings of the three characters used by CSV (, " and line-feed) may be the same width as the elemental character widths used in the binary field data, or they may be different. For example, utf-8 or utf-16 may be embedded in the fields of any CSV file that uses normal 8 bit characters for comma, quote, and line-feed. The CSV file may alternately use utf-16 for its commas, quotes, and line-feeds to enclose binary fields that contain utf-8 encoded characters. Such complexity must be dealt with deliberately somewhere by the applications that handle the data in those fields.
Problems also arise because the application or display system on which the data is conveyed may not be equipped to handle the encoding, or it may interpret it in unfamiliar ways.
The CSV to XML converter form used in this article for example, knows its output comes from an HTML form and goes to XML which must be displayed in a browser's HTML. To be compatible with this environment it intentionally converts many 8-bit characters to HTML entities (e.g. &, ", etc.).
This will cause all kinds of problems for character encodings other than the usual ISO-8859-x used for these pages. This isn't an incompatibility between CSV and utf-8, it is just an incompatibility between the character encodings used at the different presentation levels.
This issue arises in other areas as well. Spreadsheet programs will often assume all data in a CSV file is in the OEM's or system's default character encoding and attempt to interpret all characters accordingly.
The CSV to XML converter application used here can easily be configured to send output to a binary file with binary, un-cooked characters. In this case it will correctly produce the proper CSV for any character-encoding scheme including utf-8. The applications that will be asked to interpret those octets found in the CSV fields will have to know how to deal with them though. Also, this application will always use an 8-bit encoding for its CSV delimiter characters, and so may cause mixed encoding confusion when used for wide characters.
For a wealth of introductory and advanced information regarding character encoding issues there is a great Tutorial On Character Code Issues on the web. You will find this and other helpful links listed on our Links We Like page.
CSV to XML Converter Form
The following form will convert your CSV formatted data into XML. Please go to our CSV to XML Converter page, though, to be sure you are using the most up-to-date version of this facillity.
Usage: Simply paste your CSV file into the Input area and hit Convert. You may also specify the element names for the columns (fields) along with a name for the document element. If you don't specify column names or if you specify too few, those without names will be labeled "coln" where n is the column number within the record starting from zero. If you don't specify a document element name, the document element will be "document". When your file is finished the XML will display in the Output area. Hit the Select button to select it, then use your clipboard to cut and paste it wherever you'd like.
CSV In New Designs
CSV does have one advantage over XML for specification in new designs. CSV has much lower overhead, thereby using much less bandwidth and storage than XML. For new designs where overhead issues have high importance or criticality, CSV, or a more robust low-bandwidth alternative, may be the better choice.
Especially in requirements that utilize high-cost bandwidth and where large amounts of data must be moved often, CSV may be better specified. Hybrid implementations that convert to and from CSV where bandwidth is critical may also be a workable solution in some designs.
Returning to our CSV-to-XML comparison, the absolute theoretical best advantage for XML is one-letter element names in the XML and every field quoted in the CSV. Even with this unrealistic advantage, XML's overhead will be slightly greater than 200% of CSV's. Typically though, XML's overhead will be 900% to over 9000% greater than CSV's.
This is an apples to apples comparison and so only assumes transfers of regular tables (all rows of a column are the same type). XML and CSV will both transfer column names. In this comparison XML and CSV would both require extra overhead to transfer type data.
Please note: These numbers and analysis are for overhead only and do not attempt to measure or analyze the entire data file (overhead plus data). Because there is no typical data field size there is no typical ratio of overhead to data, so such an analysis would be meaningless for comparison purposes.
Lastly, when the data is very sparse, XML may be able to make up much of the overhead that CSV will use up in commas (though, this is being charitable).
CSV Format Variations
For the record there is currently no such thing as a standard CSV format (news flash: in late 2005 somebody did finally register an informational mime-type based on the CSV described in this paper). Fortunately for developers however, there is one CSV producing application with international usage that dwarfs all the other CSV producing applications combined. That is the format that's been detailed here. The CSV format described in this article has been called the "800 pound gorilla of CSV". It is not the prettiest or most technically elegant method by any means. But it is the one supported and used by the world's largest software development company. For this reason it should be supported by anyone doing serious software development and looking for a universal import/export mechanism for data produced.
There are many other CSV formats besides the variation described here. For many software designs, companies use CSV to transfer data between a number of their own applications and never intend it to be written or read by others. In such cases, the variations are almost endless.
In other cases, software developers produce their own formats with the expectation that others will adapt. One reason for such a design choice, even in the face of the 800 pound gorilla, might be that a much more technically advanced format is not that hard to conceive of.While the temptation might be to go with a more technically proficient format, for those producing new designs, I recommend including CSV support first. Why? Because this particular CSV format is most likely to be a format your users, and the people they interact with, can use. Once you have this CSV support in place, you can always add a technically superior alternative to CSV to your application's capabilities.
For those who are developing new designs we recommend following the big hairy one. However, taking some simple steps might help to improve how your design inter-operates with many of the non-Excel variations that are out there.
Excel vs. Leading Zero & Space
While this article is about CSV, this section is not. As mentioned, CSV stands on its own as a data transport format. The data it caries is just that, data. CSV does not care at all what the data is that is carried in its fields. It could be ASCII text, UNICODE text, binary image data, or even executables. It just doesn't matter to CSV what's in there.
Excel is an application that produces and uses CSV. A particular aspect of how Excel uses CSV has become a considerable source of confusion and uncertanty.
Excel will always remove leading zeros from fields before displaying them. It will also always remove leading spaces. It insists on helping you, even if removing these leading zeros will, for example, cause your numerically keyed records to sort out of order.
There is a solution
If you have a table for export in which leading zeros and spaces have significance AND that file will only be viewed in Excel, then you can use an Excel specific convention in those fields where leading zeros or spaces should be preserved (in Excel).
To implement this fix, double quote each field with leading zeros or spaces and place an equals sign (=) directly ahead of the first quote, like this:
A quirk in Excel allows you to put this particular field in your CSV file without the quoting rules discussed above (e.g. john,="08075",la). Because this csv file will be for Excel only, you could take advantage of that quirk if you really wanted to.
But Postel's Law says we should quote it according to the rules, even though we know it will be accepted in Excel without the quotes. So the field we've modified above will be further encoded according to the normal CSV rules, just like any other field when the CSV file is created:
...But the solution has a caveat
You can't just use this special field modification all the time if you are exporting tables that may be used in applications other than Excel. In many applications the extra equals sign and double quotes will simply be interpretted as corrupted data.
Even in Excel, there will be times when you will want to treat numbers as numbers. The modification discussed here will force Excel to treat numbers as formulas that return strings (not numbers).
A partial solution?
If you are exporting tables for general use that you know will often be viewed in Excel, you should probably provide a checkbox that the user making the export can check if he or she wishes to make "Excel-only CSV files with preserved leading characters."
If you are one of the many Apple owners that use this popular spreadsheet program, you'll need to ignore Postel's Law and use the first form of the workaround shown above (thanks Chris). This demonstrates that few rules-of-thumb come completely without exceptions.
This article is © Copyright, Creativyst, Inc. 2002 - 2010 ALL RIGHTS RESERVED.
Links to this article are always welcome.
However, you may not copy, modify, or distribute this work or any part of it without first obtaining express written permission from Creativyst, Inc. Production and distribution of derivative products, such as displaying this content along with directly related content in a common browser view are expressly forbidden!
Those wishing to obtain permission to distribute this article or derivatives in any form should contact Creativyst.
Permissions printed over any code, DTD, or schema files are supported as our permission statement for those constructs.