CSV文件格式

CSV即Comma Separate Values，这种文件格式经常用来作为不同程序之间的数据交互的格式。

具体文件格式

每条记录占一行

以逗号为分隔符

逗号前后的空格会被忽略

字段中包含有逗号，该字段必须用双引号括起来

字段中包含有换行符，该字段必须用双引号括起来

字段前后包含有空格，该字段必须用双引号括起来

字段中的双引号用两个双引号表示

字段中如果有双引号，该字段必须用双引号括起来

第一条记录，可以是字段名

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", ...