How do I detect the user's locale to get the correct csv separator?

Question

I have a simple data conversion tool and one of the outputs it can produce is a csv file.

This works perfectly here in the UK but when I shipped it out to a German customer I had some issues. Specifally, they use a ',' to represent the decimal point in a floating point number and vice versa. This means that when they open their data file in excel, the result is rather messy to say the least :-)

Substituting the correct character is trivial, but how can I detect whether or not to apply this?

Edit:

So this:

a,b,c
1.1,1.2,1.3
"1.1",1,2,"1,3"
"this,is,multi-
-line",this should be column 2, row 4
a;b;c
"a;b","c"

..looks like this when loaded into excel in the UK:

+----------------+-----+-----+-----+
| a              | b   | c   |     |
+----------------+-----+-----+-----+
| 1.1            | 1.2 | 1.3 |     |
+----------------+-----+-----+-----+
| 1.1            | 1   | 2   | 1,3 |
+----------------+-----+-----+-----+
| this,is,multi- |     |     |     |
| -line          | 2   | 4   |     |
+----------------+-----+-----+-----+
| a;b;c          |     |     |     |
+----------------+-----+-----+-----+
| a;b            | c   |     |     |
+----------------+-----+-----+-----+

..but what happens in Germany?

Answer 1

Use:

System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator

Writing CSV: The "List separator" string should be used as the delimiters in CSV (see below on how to change this variable). Changing the value of the "List separator" is also reflected in Excel when saving as CSV.

Reading CSV: Determining the delimiter in CSV is another story and it is a bit more complex. In principle it is possible to use a "," as a CSV delimiter in one system and use a ";" or even a "*" or any ("string") as a delimiter on another system: This article provides some insights on how to detect CSV delimiters where reading cross-systems CSV files:

http://www.codeproject.com/Articles/231582/Auto-detect-CSV-separator.

Also you can perform some tests on your exporter by changing the "List separator" value in Windows as follows (might differ between with each Windows OS):

• Open Region and Language dialog.
• Select on the "Format" tab.
• Click on "Additional Settings"
• Edit the value of the "List separator"

Answer 2

As others recommended already, the format should not be locale sensitive. This is true for storage (in files like CSV or other formats) or communication protocols. You should worry about locale sensitivity for the presentation layer only. Otherwise it means that a file saved by an American user (for instance) cannot be loaded by a German one (and the other way around).

See here for more complete guidelines: http://mihai-nita.net/2005/10/25/data-internationalization/