Microsoft Excel 2007 Import CSV File Formatting

When importing CSV files into Microsoft Excel 2007, DO NOT include spaces after the delimiter (,).

Example 1:

Column 1,Column 2,Column 3,Column 4,Column 5
Some text here,Some more text,data,data,data

Column 1	Column 2	Column 3	Column 4	Column 5
Some text here	Some more text	data	data	data

In the simple case, the data does not contain any CSV specific characters, namely the delimiter, the comma (,) and the text qualifier, the double quote (").  The data columns are separated by the delimiter with NO spaces between the columns and the delimter.  In the simple case, the it is acceptable to include a space between the delimiter and the columns, but you will find later, that this will cause data import issues.

Example 2:
Column 1, Column 2, Column 3, Column 4, Column 5
Some text here, Some more text, data, data, data

Column 1	Column 2	Column 3	Column 4	Column 5
Some text here	Some more text	data	data	data

Example 3:

"Column 1","Column 2","Column 3","Column 4","Column 5"
"Some text here","Some more text","data","data","data"

Column 1	Column 2	Column 3	Column 4	Column 5
Some text here	Some more text	data	data	data

To be more generic, it is better to surround each data column with the text qualifier in case a special character in included in the data.  In the above Example 3, the data does not include any special characters, so the text qualifier is not necessary.

Example 4:

"Column 1","Column 2","Column 3","Column 4","Column 5"
"Some text here, with comma","Some more text","data,more data","data","data"

Column 1	Column 2	Column 3	Column 4	Column 5
Some text here, with comma	Some more text	data,more data	data	data

Example 4 contains the delimter between text qualifers, in this case, the comma will be treated as a character instead of a delimiter.  At this point, the space before and after the delimiters becomes important.  Placing a space after the delimiter will cause Excel to treat ALL commas in the CSV as a delimiter, therefore, the string

"Some text here, with comma"

will be split into 2 columns

"Some text here
with comma"

Example 5:

"Column 1", "Column 2", "Column 3", "Column 4", "Column 5"
"Some text here, with comma", "Some more text", "data,more data", "data", "data"

"Column 1"	"Column 2"	"Column 3"	"Column 4"	"Column 5"
"Some text here	with comma"	"Some more text"	"data	more data"	"data"	"data"

Example 6:
"Column 1","Column 2","Column 3","Column 4","Column 5"
"Some text here, with comma","Some more text with ""double quote""","data,more data","""extra"" data","data"

Column 1	Column 2	Column 3	Column 4	Column 5
Some text here, with comma	Some more text with "double quote"	data,more data	"extra" data"	data

To include data columns with a double quote character ("), escape it by replacing the double quote (") with 2 double quote characters ("").

Example 7:
"Column 1","Column 2","Column 3","Column 4","Column 5"
"Some text here with newline,
with comma","Some more text with ""double quote""","data,more data","""extra"" data","data"

Column 1	Column 2	Column 3	Column 4	Column 5
Some text here with newline,
with comma	Some more text with "double quote"	data,more data	"extra" data"	data

Under NO circumstance is the newline character allowed in the the data column, even if it has double quotes surrounding it.