Depending on whether your OS uses a , or a . for decimal numbers changes how excel will parse a CSV file. Americans use a . for decimal numbers, so it will parse it as a CSV. Other countries use a , for decimal numbers, so it will parse it as a SSV (semi-colon separated) and everything will be in a single column.
To make matters worse, randomly, employees will have their OS using US or GB locales so that if you distribute a CSV, it will work for some employees, but not for others.
No. Excel changes the SEPERATOR when parsing depending on the locale settings. This means a CSV generated or saved with a decimal of . will not be able to be opened by one with a , and vice-versa. This is an Excel issue, as it doesn’t even try to determine or ask which separator to use. Hence why the comment above said you need to use the import wizard and not double click.
The syntax that MS Office uses to read/write a CSV is defined by the Regional Settings of your PC.
Open control-panel for regional settings, select "Advanced settings" button on the bottom
control.exe intl.cpl
If you don't know any of these problems, then all the people and systems you work with have a "." as decimal and "," as separator, and you are spared from the hell of MS Office being unable to overrule these OS-settings when treating a CSV
Depending on whether your OS uses a , or a . for decimal numbers changes how excel will parse a CSV file. Americans use a . for decimal numbers, so it will parse it as a CSV. Other countries use a , for decimal numbers, so it will parse it as a SSV (semi-colon separated) and everything will be in a single column.
To make matters worse, randomly, employees will have their OS using US or GB locales so that if you distribute a CSV, it will work for some employees, but not for others.
Excel's behaviour is almost as annoying. It's basically impossible to produce a correctly-formatted German document on an English OS and vice-versa.
this seems like less of an excel problem and more of an issue with an improperly escaped data set though?
No. Excel changes the SEPERATOR when parsing depending on the locale settings. This means a CSV generated or saved with a decimal of . will not be able to be opened by one with a , and vice-versa. This is an Excel issue, as it doesn’t even try to determine or ask which separator to use. Hence why the comment above said you need to use the import wizard and not double click.
I don't know any of these problems. I use a modern operating system and office suite that supports CSV not a specific subset and syntax of it.
The syntax that MS Office uses to read/write a CSV is defined by the Regional Settings of your PC.
Open control-panel for regional settings, select "Advanced settings" button on the bottom control.exe intl.cpl
If you don't know any of these problems, then all the people and systems you work with have a "." as decimal and "," as separator, and you are spared from the hell of MS Office being unable to overrule these OS-settings when treating a CSV
2 replies →
1.01 in US === 1,01 in EU
See the problem now?
Your operating system cannot solve this problem.
7 replies →