Excel has some annoying defaults when importing data, too.
My company tracks transactions with an ID that is a numeric string that Excel assumes is an integer but longer than it supports so it truncates the value and displays it in scientific notation.
Original value: -7223371999747962216
Truncated value: -7223371999747960000
Displayed value: -7.22337E+18
It would be fine if it just treated it as text but by discarding digits it makes the values useless. You can’t open a CSV file directly. You have to manually import the data and specify specific columns as text. Every. Single. Time. It cannot be automated.
I’ve been sent so many excel files with truncated data like that because most people don’t even know about the problem, let alone know how to work around it.
Sure, if you have control over the source, but that is not always the case. CSV is very loosely specified and there are large variations in implementations.
Comments
Excel has some annoying defaults when importing data, too.
My company tracks transactions with an ID that is a numeric string that Excel assumes is an integer but longer than it supports so it truncates the value and displays it in scientific notation.
Original value: -7223371999747962216
Truncated value: -7223371999747960000
Displayed value: -7.22337E+18
It would be fine if it just treated it as text but by discarding digits it makes the values useless. You can’t open a CSV file directly. You have to manually import the data and specify specific columns as text. Every. Single. Time. It cannot be automated.
I’ve been sent so many excel files with truncated data like that because most people don’t even know about the problem, let alone know how to work around it.
It may be automatable if you enclose the values in double quotes and specify that double quotes mark text (which is the default I think).
Sure, if you have control over the source, but that is not always the case. CSV is very loosely specified and there are large variations in implementations.