csv规则和格式
时间：2005-12-19 来源：odin_free
下面是cvs官方定义规则：
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", ...
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", ...
相关阅读 更多 +
