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.
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=5514587Explanation:
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`)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.
In that context, "in an Excel file" meant "in a native Excel file".
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.
Surely MS research has shown that this is one of the great pain points with Excel/Word?
[¹ I haven't used MS Word for real in over a decade, it used to have a display of OVR in a bottom panel IIRC for when overwrite mode was on.]
Perhaps, but Microsoft is infamous for ignoring Excel's many faults. An article linked yesterday described a litany of very serious statistical errors of long standing, without any indication of a timetable to repair the defects.
It probably doesn't make any sense to apply automatic conversions to formats that are more or less defined by convention, but if you mark a column in an Excel file as text, Excel won't apply magic to that column.
The bigger problem is that it is considered acceptable to not keep a record of the changes being made to the data (a script can serve nicely as both a data processor and a record of the processing being done).