python csv list separator based on regional settings

Question

how to detect list separator in users machine with Python?

CSV file needs to be created on users machine and the list separator must be detected automatically (so that excel can read the CSV file).

I've found out that Excel takes CSV elements separator from "Regional Options -> Numbers -> List separator". locale module in Python is used to detect cultural settings, but it (locale.localeconv) does not contain list separator. Opening CSV writer with dialect='excel' does not help. Any idea how to get the correct separator?

EDIT

The following code seems to work (but can't accept any upvotes as the solution is not mine)

import locale

langlocale = locale.getdefaultlocale()[0]
locale.setlocale(locale.LC_ALL, langlocale)
dp = locale.localeconv()['decimal_point']
delimiter = ','
if dp == ',':
    delimiter = ';'

Answer 1

• How to read 'List separator' from OS in Java?

Provided the idea to read the list separator symbol from the Windows registry.

• Python code to read registry

Provided the code to access Windows Registry values.

Using the _winreg package, the Windows list separator value can be retrieved from the registry as follows:

from _winreg import *

def getListSeparator():
    '''Retrieves the Windows list separator character from the registry'''
    aReg = ConnectRegistry(None, HKEY_CURRENT_USER)
    aKey = OpenKey(aReg, r"Control Panel\International")
    val = QueryValueEx(aKey, "sList")[0]
    return val

print getListSeparator()

Answer 2

Write an XLS file with xlwt.

Take 2: Use the locale module and some heuristics:

>>> import locale
>>> locale.setlocale(locale.LC_ALL, '') # set to user's locale, not "C"
'English_Australia.1252'
>>> dec_pt_chr = locale.localeconv()['decimal_point']
>>> if dec_pt_chr == ",":
...     list_delimiter = ";"
... else:
...     list_delimiter = ","
...
>>> print repr(dec_pt_chr), repr(list_delimiter)
'.' ','
>>> locale.setlocale(locale.LC_ALL, 'French_France.1252')
'French_France.1252'
>>> dec_pt_chr = locale.localeconv()['decimal_point']
>>> if dec_pt_chr == ",":
...     list_delimiter = ";"
... else:
...     list_delimiter = ","
...
>>> print repr(dec_pt_chr), repr(list_delimiter)
',' ';'
>>>

Answer 3

I use sniff to autodetect it:

import csv

dialect = csv.Sniffer().sniff(file.readline())
file.seek(0)
file.readline()
file.seek(0)
fieldnames=( 'assignatura', 'professor', 'grup',  )
reader = csv.DictReader(file, fieldnames=fieldnames, dialect=dialect )

for row in reader:

        codiFranja = unicode(row['assignatura'],'iso-8859-1')
        ...

EDITED:

If you are planning to create a csv from python and read it from excel, then you need to create file with locale delimiter. But python csv module don't use locale: http://mail.python.org/pipermail/csv/2003-May/000507.html

Andreas> This could be really simple to implement using the locale module. But I took a short look at the locale module and it seems like there is no way to get the list separator sign (probably it's not POSIX complaint).

A workaround may be to write delimiters on a configuration file on each python user client instalation based on locale desktop computer and read this delimiters when python make csv file.

Also you can write vbscript code that creates and excel file and exports it to csv then look for delimiter each time that you need to create your python csv file.

Perhaps the most elegant solution is to use schema.ini: http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx . You can generate the csv file and, in the same foleder, schema.ini file.