Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 563
Jon Cage Avatar asked Nov 11 '11 10:11

Jon Cage


People also ask

How do I find the CSV separator?

When the field separator (delimiter) is a comma, the file is in comma-separated (CSV) or comma-delimited format. Another popular delimiter is the tab. If a field contains the delimiter character within its text, the program interprets this as the end of the field rather than as part of the text.

How do I open a CSV file with the correct delimiter separator?

Using the "From Text" feature in Excel Click the Data tab, then From Text. Select the CSV file that has the data clustered into one column. Select Delimited, then make sure the File Origin is Unicode UTF-8. Select Comma (this is Affinity's default list separator).

How do I change the locale of a CSV file?

In the Behavior > Actions tab open the Import or Export action. Click the expression (beaker) icon following the CSV file locale property and enter the following locale expression: USERSETTINGS(CsvLocale) Click the Save button.


2 Answers

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"
like image 160
Ahmad Avatar answered Sep 28 '22 02:09

Ahmad


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/

like image 22
Mihai Nita Avatar answered Sep 28 '22 02:09

Mihai Nita