4 ms·

> if you mark a column in an Excel file as text, Excel won't apply magic to that column. The problem is that the CSV file format has no reliable mechanism to m

by lutusp 14y ago

> if you mark a column in an Excel file as text, Excel won't apply magic to that column. The problem is that the CSV file format has no reliable mechanism to mark a column as text( * ). Known workarounds include inserting a single quote at the beginning of a field, but this single quote may remain in the field forever, polluting the data downstream from the import. * The obvious approach of enclosing CSV text fields in quotes, and non-text fields without quotes, won't work -- too many Excel versions strip out all the quotes while importing, without considering the implication of their selective application. Also, in many Excel versions "DEC1" is converted into a date whether or not it's quoted.

maxerickson 14y ago
The point of my comment was mainly that "by rewriting data cells in spreadsheets" is a poor way to describe the bad import behavior of Excel. A CSV is an ill specified structured text file, not a spreadsheet (most spreadsheets have some sort of type information for each cell, who knows whether it is intentional and accurate and so forth, but it is there). In that context, "in an Excel file" meant "in a native Excel file".
- lutusp 14y ago
  > most spreadsheets have some sort of type information for each cell ... Yes, but the problem is that, without some knowledge and care on the part of the operator, the data type will be established during the import, not in advance, and on a cell-by-cell basis.
niggler 14y ago
I posted this the last time there was a discussion about CSV and excel: If you are generating the CSV yourself, save yourself some agony and just wrap the text in ="" $ cat test.csv ="12.34567890124312341234123412341234",="1-5" $ open -a Microsoft\ Excel test.csv https://news.ycombinator.com/item?id=5514587 https://news.ycombinator.com/item?id=5514587 Explanation: It uses another auto trick: if the leading character is '=', the result is treated as a formula. Thus, you can actually write formulas in your CSV and excel will interpret appropriately: $ cat test.csv =1+1,="1-5" $ open -a Microsoft\ Excel test.csv You should see the value '2' (with content `=1+1`) https://news.ycombinator.com/item?id=5514704 https://news.ycombinator.com/item?id=5514704
- spikels 14y ago
  An alternative solution that works is some situations is to format the column as "Text". However you must do this before pasting the data into the cells or else it will already be converted. I use this technique for zip codes which can have leading zeros.
gngeal 14y ago
"The problem is that the CSV file format has no reliable mechanism to mark a column as text( )."* The problem is that there is NO such thing a "the CSV file format". There's only a lot of different interpretations of how to store data in forms of rows with fields separated by commas or semicolons or whatnot, with every software package having slightly different ideas. Save the world, use S-expressions or JSON if you write something new.
- MichaelGG 14y ago
  CSV is not the issue here; Excel's automagic conversions are. If it could open JSON, it'd extract the values, then apply it's magic. So your JSON string "DEC1" would still be converted (JSON doesn't have a date type).

by lutusp 14y ago

maxerickson 14y ago

The point of my comment was mainly that "by rewriting data cells in spreadsheets" is a poor way to describe the bad import behavior of Excel. A CSV is an ill specified structured text file, not a spreadsheet (most spreadsheets have some sort of type information for each cell, who knows whether it is intentional and accurate and so forth, but it is there). In that context, "in an Excel file" meant "in a native Excel file".

lutusp 14y ago
> most spreadsheets have some sort of type information for each cell ... Yes, but the problem is that, without some knowledge and care on the part of the operator, the data type will be established during the import, not in advance, and on a cell-by-cell basis.

niggler 14y ago

I posted this the last time there was a discussion about CSV and excel: If you are generating the CSV yourself, save yourself some agony and just wrap the text in ="" $ cat test.csv ="12.34567890124312341234123412341234",="1-5" $ open -a Microsoft\ Excel test.csv https://news.ycombinator.com/item?id=5514587 https://news.ycombinator.com/item?id=5514587 Explanation: It uses another auto trick: if the leading character is '=', the result is treated as a formula. Thus, you can actually write formulas in your CSV and excel will interpret appropriately: $ cat test.csv =1+1,="1-5" $ open -a Microsoft\ Excel test.csv You should see the value '2' (with content `=1+1`) https://news.ycombinator.com/item?id=5514704 https://news.ycombinator.com/item?id=5514704

spikels 14y ago
An alternative solution that works is some situations is to format the column as "Text". However you must do this before pasting the data into the cells or else it will already be converted. I use this technique for zip codes which can have leading zeros.

gngeal 14y ago

"The problem is that the CSV file format has no reliable mechanism to mark a column as text( )."* The problem is that there is NO such thing a "the CSV file format". There's only a lot of different interpretations of how to store data in forms of rows with fields separated by commas or semicolons or whatnot, with every software package having slightly different ideas. Save the world, use S-expressions or JSON if you write something new.

MichaelGG 14y ago
CSV is not the issue here; Excel's automagic conversions are. If it could open JSON, it'd extract the values, then apply it's magic. So your JSON string "DEC1" would still be converted (JSON doesn't have a date type).