Create excel-compatible CSV file with python?

Question

I am trying to create a csv file using python that is truly Excel-compatible (I am using Excel 2007, if that makes any difference).

Here is the sort of thing I'm trying to do

import csv

data = [ ['9-1', '9-2', '9-3'] ]

fd = open('test.csv', 'wb')
try:
    writer = csv.writer(fd, dialect='excel', quotechar='"', quoting=csv.QUOTE_ALL)
    writer.writerows(data)

finally:
    fd.close()

This creates a csv file that contains:

"9-1","9-2","9-3"

When I load it in Excel, I get the columns:

09-Jan  09-Feb  09-Mar

[Grrr, thanks Excel. Seriously, what is the point of quotes?]

Now, I have read elsewhere that in order to get Excel to treat strings as literal they must be prefixed by an equals sign ="Like This". So what I really want to produce is:

="9-1",="9-2",="9-3"

Is there any way to do this with the csv module in python? I'm guessing it will involve creating a custom dialect, but I can't see any variable that would give me the desired result.

Answer 1

If your aim is just writing a list as a table on Excel. You can try below sample, where ";" and dialect='excel-tab' property enables us to switch between coloumns.

import csv

RESULTS = [
    ['val_col1;','val_col2;','val_col3']
]
resultFile = open("testExcel.csv",'wb')
resultWriter= csv.writer(resultFile, dialect='excel-tab')
resultWriter.writerows(RESULTS)

Answer 2

FWIW, Excel 2007 is not even compatible with itself. If you type 9-1 into an empty spreadsheet, it converts it to 1-Sep as soon as you leave the cell. If you change the cell format from General to Text, you can enter the string literals unchanged and save it as a .csv file, but when you read the same file into a blank spreadsheet (with General format) it gets reconverted to dates.

Excel saves the file as

9-1,9-2,9-3

without quotes and without "=" signs. Even setting the cell formats to Text before reading the csv file doesn't work for me - it imports as dates (display value 1-Sep, cell value 9/1/2011) with the 3 cells in Custom format; the rest of the cells are converted to General.

Edit: A csv file containing:

"9-1",'9-2',='9-3',=''9-4''

reads into the spreadsheet as:

1-Sep  '9-2'  ='9-3'  ="9-4"

So the = doesn't do any good, but at least the single quotes will get a string literal, with the quotes preserved. Someone more knowlegeable in Excel will have to help with getting rid of the quotes; "paste values" doesn't strip them off.

Answer 3

The problem is that you're importing your CSV file into Excel using Excel's CSV importer. Counterintuitive, I know, but you should not import CSV files that way. Instead, import them as text files (i.e., name with a txt extension). Then you will have the opportunity to specify the type of each column and can properly choose Text for columns that look like dates to Excel.

If you're looking for a turnkey "here is a file you can open in Excel" approach, try xlwt (not my original recommendation pyXLWriter) and avoid CSV entirely.

Answer 4

Ok, thanks for all your help, I've come up with a rough solution, so I thought I'd share it here.

It's not the prettiest of solutions but it works for my purposes. A warning though: It will break if you have any quotes in your strings!

import csv
import re

data = [ ['9-1', '9-2', '9-3'] ]


def quoteCsvData(data):
    for x, row in enumerate(data):
        for y, item in enumerate(row):
             if data[x][y] != '':
                 data[x][y] = r'="' + data[x][y] + r'"'
    return data

def unquoteCsvData(data):
    for x, row in enumerate(data):
        for y, item in enumerate(row):
            if data[x][y] != '':
                m = re.match(r'="([^"]*)"',data[x][y])
                if m:
                    data[x][y] =  m.group(1)
    return data


fd = open('test2.csv', 'wb')
data = quoteCsvData(data)
try:
    writer = csv.writer(fd, delimiter=',',
                            quotechar='|', 
                            quoting=csv.QUOTE_NONE, 
                            escapechar='\\')
    writer.writerows(data)

finally:
    fd.close()

fd = open('test2.csv', 'rb')
try:
    reader = csv.reader(fd, delimiter=',', 
                            quotechar='|', 
                            quoting=csv.QUOTE_NONE, 
                            escapechar='\\')
    data = []
    for row in reader:
        data.append(row)

finally:
    fd.close()

data = unquoteCsvData(data)

print data

Update: removed spamming of empty cells with lots of =""

Answer 5

try::

dialect=csv.excel

as in

import csv
csv.excel 

• lineterminator '\r\n'
• skipinitialspace False
• quoting 0
• delimiter ','
• quotechar '"'
• doublequote True

For me its a bit of a black art that has never bothered me enough to go read the code, but the little experimentation I did always got excel (2003) to behave well enough with the above formats.

Answer 6

data = [ ['9-1', '9-2', '9-3'] ]
fd = open('test.csv', 'wb')
for row in data:
    for x in row:
        fd.write('="{}",'.format(x))
    fd.write('\n')