CSV Comma Separated Value File Format - How To - Creativyst - Explored,Designed,Delivered.(sm).MultilineExample { font-size:9pt; background-color:#CCCCDD; border-style:solid; border-width:1px; border-color:#9999CC; width:70%; text-align:left;}.InlineNote { font-size:9pt; font-style:italic; background-color:#CCCCDD; border-style:solid; border-width:1px; border-color:#9999CC; width:85%; text-align:left;}  Explored. Designed. Deliveredsm Creativyst Docs Understanding CSV File Formats Home Products Support/Doc Developers Forums Associates (?) Contact | News | Glossary | Site var msg; msg = GetDocMenu(); document.write(msg); How To: The Comma Separated Value (CSV) File FormatCreate or parse data in this popular pseudo-standard format Contents Overview The CSV File Format More Example CSV Data CSV & Unicode CSV to XML Converter CSV in New Designs CSV Format Variations Excel vs. Leading Zero & Space Permissions [top] OverviewThe 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. [top] The CSV File Format Each record is one line ...but A record separator may consist of a line feed (ASCII/LF=0x0A), or a carriage return and line feed pair (ASCII/CRLF=0x0D 0x0A). ...but: fields may contain embedded line-breaks (see below) so a record may span more than one line. Fields are separated with commas. Example John,Doe,120 any st.,"Anytown, WW",08123 Leading and trailing space-characters adjacent to comma field separators are ignored. So John , Doe ,... resolves to "John" and "Doe", etc. Space characters can be spaces, or tabs. Fields with embedded commas must be delimited with double-quote characters. In the above example. "Anytown, WW" had to be delimited in double quotes because it had an embedded comma. Fields that contain double quote characters must be surounded by double-quotes, and the embedded double-quotes must each be represented by a pair of consecutive double quotes. So, John "Da Man" Doe would convert to "John ""Da Man""",Doe, 120 any st.,... A field that contains embedded line-breaks must be surounded by double-quotes So: Field 1: Conference room 1 Field 2: John, Please bring the M. Mathers file for review -J.L. Field 3: 10/18/2002 ... would convert to: Conference room 1, "John, Please bring the M. Mathers file for review -J.L. ",10/18/2002,... Note that this is a single CSV record, even though it takes up more than one line in the CSV file. This works because the line breaks are embedded inside the double quotes of the field. Implementation note: In Excel, leading spaces between the comma used for a field sepparator and the double quote will sometimes cause fields to be read in as unquoted fields, even though the first non-space character is a double quote. To avoid this quirk, simply remove all leading spaces after the field-sepparator comma and before the double quote character in your CSV export files. Fields with leading or trailing spaces must be delimited with double-quote characters. So to preserve the leading and trailing spaces around the last name above: John ," Doe ",... Usage note: Some applications will insist on helping you by removing leading and trailing spaces from all fields regardless of whether the CSV used quotes to preserve them. They may also insist on removing leading zeros from all fields regardless of whether you need them. One such application is Excel. :-( For some help with this quirk, see the section below entitled Excel vs. Leading Zero & Space. Fields may always be delimited with double quotes. The delimiters will always be discarded. Implementation note: When importing CSV, do not reach down a layer and try to use the quotes to impart type information to fields. Also, when exporting CSV, you may want to be defensive of apps that improperly try to do this. Though, to be honest, I have not found any examples of applications that try to do this. If you have encountered any apps that attempt to use the quotes to glean type information from CSV files (like assuming quoted fields are strings even if they are numeric), please let me know about it. The first record in a CSV file may be a header record containing column (field) names There is no mechanism for automatically discerning if the first record is a header row, so in the general case, this will have to be provided by an outside process (such as prompting the user). The header row is encoded just like any other CSV record in accordance with the rules above. A header row for the multi-line example above, might be: Location, Notes, "Start Date", ... [top] Example DataHere 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. John,Doe,120 jefferson st.,Riverside, NJ, 08075Jack,McGinnis,220 hobo Av.,Phila, PA,09119"John ""Da Man""",Repici,120 Jefferson St.,Riverside, NJ,08075Stephen,Tyler,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 91234,Blankman,,SomeTown, SD, 00298"Joan ""the bone"", Anne",Jet,"9th, at Terrace plc",Desert City,CO,00123These records show how the CSV format behaves under a variety of conditions, such as using quotes to delimit a field with embedded quotes and embedded commas; leaving some fields blank (Mr. Blankman for example has no First Name field); as well as combinations ('Joan, "the bone", Ann' for example, who's middle name is included along with her first name and nickname). [top] CSV & UnicodeJust 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 confusionwhen used for wide characters. For a wealth of introductory and advanced information regarding character encoding issues there is a greatTutorial On Character Code Issueson the web. You will find this and other helpful links listed on ourLinks We Like page. [top] CSV to XML Converter FormThe 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. Col IDs, 1/line no spaces. Input (CSV file): DocID: Output (XML file): [top] CSV In New DesignsCSV 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. An even better choice for high-bandwidth designs where CSV compatibility isn't required might be to use a more functional, low-overheadalternative to CSV, such as Creativyst's Table Exchange (CTX) format. 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). A note about compression performance:Neither CSV or XML specify compression, they both sit somewhere between the transport/presentation layers without dictating the specifics of either. However, since compression is often used to improve performance on low bandwidth channels (precisely where such a design issue might be considered), it is a good idea to do an apples-to-apples comparison of how compressible the overhead portion of each file type will be. We'll start by using the best case for XML which is a file with one field per a record, and a column name that is one character long. This is rather absurd but reduces the problem to "onesies" to make comparison easy and unambiguous. In this case the overhead for XML will consist of four characters ("<, A, >" and "/" - assuming the one field's name is 'A'). The overhead for CSV on the other hand will consist of only three characters: a comma (','), a new line ('\n'), and the double quote character ("). That makes CSV's overhead a third more redundant, and therefore a third more compressible (theoretically) than XML. In this comparison XML is given the theoreticaly best case advantage, if you add more fields, each with its own name being added to XML's overhead, the comparison quickly gets much worse for XML. This is purely a theoretical analysis of performance under compression. If you would like to do the observational analysis please write to me (include your email address so we can discuss it). [top] CSV Format VariationsFor 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. |
|
