Discovering regional settings (list separator) from the Internet

Question

After the unpleasant surprise that Comma Seperated Value (CSV) files are not necessarily separated by commas, I try to find out if there is a way to detect that the value of the regional list separator on the client machine is an http request.

The scenario is as follows: the user can download some data in CSV format from the website (RoR, if that matters). This CSV file is generated on the fly, sent to the user, and most of the time he double-clicked and opened it in MS Excel on a computer running Windows. Now, if the user has a ',' defined as a list separator, the data is correctly ordered in columns, but if some other separator (';' is widely used here) is set, all this just falls into one column. So, is there a way to determine which delimiter is used on the client machine and generate the file accordingly?

I have a feeling of drowning that this is not so, but I would like to be sure before passing it to the “client” it cannot be done, sorry ")

Answer 1

Here is the JavaScript solution I just wrote based on the method shown here :

function getListSeparator() { var list = ['a', 'b'], str; if (list.toLocaleString) { str = list.toLocaleString(); if (str.indexOf(';') > 0 && str.indexOf(',') == -1) { return ';'; } } return ','; } 

The key is in the toLocaleString () method, which uses the system list separator.

You can use JavaScript to get the list separator and set it in a cookie that you might find from your server.

I checked all Windows locales, and it seems that the default list separator is almost always either ",", or ";". For some locales, the drop-down list on the control panel offers both options; for others, he offers only ",". One language, Divehi, has a strange character that I have not seen before as a list separator, and for any locale the user can enter any line that they want as a list separator.

Putting random strings as a separator in a CSV file sounds like a problem to me, so my function above will return either ';' or '.' and it will only return ';' if he cannot find the string ',' in the string Array.toLocaleString. I'm not quite sure if the .toLocaleString array has the format that is guaranteed in browsers, so indexOf checks and does not highlight a character with a specific index.

Using Array.toLocaleString to get a list separator works on IE6, IE7 and IE8, but unfortunately it doesn't work on Firefox, Safari, Opera and Chrome (or at least the versions of these browsers on my computer): all of them, seem to separate the elements of the array with a comma, regardless of the setting of the "Windows List Separator".

It is also worth noting that by default, Excel seems to use the "decimal separator" system when parsing numbers from CSV files. SK. So, if you localize the list separator, you can also localize the decimal separator.

Answer 2

I think everyone should use Calc from OpenOffice - it asks when you open the encoding file, column delimiters and more. I do not know the answer to your question, but perhaps you can try to send the data in html tables or in xml - excel should read them correctly. In my experience, it is not easy to export data to Excel. A few weeks ago I had problems with this, and after several hours of work, I asked a person who could not open my csv file in excel about the version. It was Excel 98 ...

Check out the html and xml example .

Answer 3

Could you just indicate that users with non-comma delimiters set the profile profile type and then generate CSVs based on user preferences with commas by default?

Answer 4

Toms, as far as I know, there is no way to achieve what you need. The most you can do is try to locate the user and map it to the locale / list separator database, resulting in a separation of the list separator in the .CSV file.

Answer 5

The simplest version of the getListSeparator function allows any character to be a separator:

 function getListSeparator_bis() { var list = ['a', 'b']; return(list.toLocaleString().charAt(1)); }// getListSeparator_bis 

Just set any char (fe '#') as a list separator on your OS and try the code as above. The corresponding char is returned (i.e. '#' if set to sugested).