Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python csv writer wrong separator?

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').

like image 440
orlp Avatar asked Sep 14 '11 22:09

orlp


People also ask

What is the default separator in csv?

csv files, Microsoft Excel uses the List separator defined in Windows Regional settings. In North America and some other countries, the default list separator is a comma, so you get CSV comma delimited.


2 Answers

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)

like image 159
donkopotamus Avatar answered Oct 12 '22 01:10

donkopotamus


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)
like image 45
miku Avatar answered Oct 12 '22 00:10

miku