CODEWITHSUNDEEP
pythonexcelcsvpython-3.x
Bolesław Breczko
Bolesław Breczko

Reputation: 1

csv.writer writing all contents to one cell

EDIT: Solved thanks to @ayhan. If you are from Poland use semicolon instead of comma as a separator.

I have a list of lists that looks like this:

list = [['edytor poranka PN', '1/04/2016', '15:00'], ['edytor PN 2', '2/04/2016', '08:30'], ['edytor PN noc', '3/04/2016', '22:00']]

csv.writer writes each list into one cell instead to 3 separate ones.

My code looks like this:

with open('file.csv', 'w') as f:
    writer = csv.writer(f, delimiter=',', quoting=csv.QUOTE_MINIMAL)
    writer.writerows(list)

I've also tried this method:

for i in list:
    writer.writerow(i)

And it also doesn't work. My other issue is that each row is separated by an extra empty row and I don't know how to get rid of it. I know there has been similar questions but no solution is working for me.

Upvotes: 0

Views: 6955

Answers (2)

martineau
martineau

Reputation: 123463

Slayer's code worked for me here is the US, but if I changed my Windows Region and Language settings to Poland it doesn't and I was finally able to reproduce your problem.

According to @ayhan this is because of a bug in Excel where it expects ; to be the delimiter when reading and saving csv files in locales where , is used as the decimal separator (decimal mark) such as it is in Poland (and most of the rest of the world).

Probably the easiest work-around is just specify the delimiter Excel expects in your region when you create the csv.writer object, as shown below:

#!/usr/bin/env python3

import csv

list_of_lists = [['edytor poranka PN', '1/04/2016', '15:00'],
                 ['edytor PN 2', '2/04/2016', '08:30'],
                 ['edytor PN noc', '3/04/2016', '22:00']]

with open('file.csv', 'w', newline='') as f:
    writer = csv.writer(f, delimiter=';')
    for sublist in list_of_lists:
        writer.writerow(sublist)

Contents of file.csv after running the script:

edytor poranka PN;1/04/2016;15:00
edytor PN 2;2/04/2016;08:30
edytor PN noc;3/04/2016;22:00

Afterwards, if I open it in Microsoft Excel 2013, with my Windows Format and Location set to "Poland" in the Windows Region and Language control panel, I see the following (which is now correct):

screenshot of file after opening it in excel 2013

Update

A way of doing it dynamically would be to have the script determine what delimiter to use based on the current locale settings and pick something different if there's a conflict — although hardcoding ; should work any place that doesn't use it as the decimal mark (and I'm not aware of one that does).

Since we now know what was causing the issue, I would also take out the for loop Slayer introduced and go back to the version of your code that used writerows().

import csv
import locale

# Chose an Excel compatible csv delimiter.
locale.setlocale(locale.LC_ALL, '')
DELIMITER = ';' if locale.localeconv()['decimal_point'] == ',' else ','

list_of_lists = [['edytor poranka PN', '1/04/2016', '15:00'],
                 ['edytor PN 2', '2/04/2016', '08:30'],
                 ['edytor PN noc', '3/04/2016', '22:00']]

with open('file.csv', 'w', newline='') as f:
    writer = csv.writer(f, delimiter=DELIMITER)
    writer.writerows(list_of_lists)

Upvotes: 2

Pythonista
Pythonista

Reputation: 11615

You need to specify the newline to prevent the extra row from being added on each iteration. Also naming your variable list is a bad idea, since it's a built in type.

with open('file.csv', 'w', newline='') as f:
    writer = csv.writer(f)
    for a_list in lists:
        writer.writerow(a_list)

Upvotes: 1

Related Questions