Python csv writer wrong separator?

Question

Disclaimer: I'm in Europe.

According to this page Excel uses the semicolon ; as default separator in Europe to "prevent conflicts" with the decimal comma.

Now, I have this Python code:

import csv

data = [["test", "data"], ["foo", "bar"]]
writer = csv.writer(open("data.csv", "wb"), dialect="excel")
writer.writerows(data)

Which should generate this file:

test;data
foo;bar

but instead it uses commas. Why is this happening? locale.getdefaultlocale() returns ('nl_NL', 'cp1252').

Solution 1

This is because the csv.excel dialect is not locale aware. If you wish to explicitly use semicolons as the delimiter then you need to either explicitly pass the delimiter to csv.open as

writer = csv.writer(open("data.csv", "wb"), delimiter=";")

or create a new dialect and register it

class excel_semicolon(csv.excel):
    delimiter = ';'
register_dialect("excel-semicolon", excel_semicolon)

In either case, you should test how floating point numbers are written ... I suspect they won't be written in the European format you desire (with a comma as the radix)

Solution 2

The excel dialect is specified by the following attributes (in Lib/csv.py, line 57):

delimiter = ','
quotechar = '"'
doublequote = True
skipinitialspace = False
lineterminator = '\r\n'
quoting = QUOTE_MINIMAL

I see no hint, that this is somehow locale-dependent - hence you'll always get , with the default dialect.

But that's easily fixed, e.g.

class excel_semicolon(csv.excel):
    delimiter = ';'

writer = csv.writer(open("data.csv", "wb"), dialect=excel_semicolon)