Microsoft Excel's Unintelligent Defaults (multi-ligual CSV failure)

One of the best thing to do in an application is to work hard on the default behaviour, 95% of users never change their settings so the defaults really are vital to the use of the program.

Excel has wonderful features but its defaults are extremely frustrating. Many of them I simply do not understand, for example if a column has leading zeroes then the user probably wants to keep them! However, I came across one issue which I had not experienced until now.

Anyone who has had to deal with internationalisation will probably be aware of the differences in European decimals for example 1,000.00 in UK & US is written as 1.000,00 in many European countries. Microsoft Windows has region settings which take these into account. Excel uses the region settings as the default method for formatting files. This seems reasonable but it contains a setting called list separator which it uses as the default separator for reading a CSV file. If your region is one where "," is the standard for decimals then this separator will default to ";".

This means that if you double click a CSV file which is comma separated it will open and display in a single column. What is worse is if your data has a ";" in it then this will be used as a separator and the data will be split on this value. So a CSV file which opens with a double click perfectly on a computer in the US will open incorrectly on a computer in Germany. If you were to alter your CSV output to ";" separated then the file would open in Germany fine, but the US client would find the data all in the first column.

You cannot even get the end user to alter their region settings to use "," as the list separator because Excel will ignore this because you have "," as a decimal separator. Microsoft do not appear to have worked very hard at helping the user with the default behaviour.

There is of course a workaround which is to open Excel and use the import option to specify how to handle the file, but this feels like a punch in the face in terms of usability.

So what should use do if you want to allow your web application to output CSV files for end users to multiple regions where you expect many of them will use Excel to consume them?

Option 1: Use the Microsoft proprietary sep=, command

If you put the text "sep=," on the first line of a CSV then Excel will use the separator you have specified in this case "," to open the file. Now all your Excel users are happy no matter what their region settings. However, the down side is that users of any other program, like Open Office Calc for example, have a bit of rubbish on the first line that they need to remove or choose not to import and the end user probably will not understand why.

Option 2: Use the browser location / language settings to determine if you should send "," or ";" separated files

Pretty sophisticated aren't you detecting the region and then forcing the file to save in different formats. However, detection would be an imperfect mechanism and although could be of great benefit in some situations would fail in others.

Option 3: Let the user specify their default

The user is in control and can force the separator to be in the format that they want, everything is now right in the world. Oh except as previously stated 95% of users don't bother setting their preferences.

Option 4: Lobby Microsoft to stop using Unintelligent Defaults

Well I don't see this changing any time soon as a huge chunk of Windows users are effected negatively by this behaviour and Excel has had these massive flaws for many years and many different releases, but we can all dream can't we.