3

I have many Python scripts that output CSV files. It is occasionally convenient to open these files in Excel. After installing OS X Mavericks, Excel no longer opens these files properly: Excel doesn't parse the files and it duplicates the rows of the file until it runs out of memory. Specifically, when Excel attempts to open the file, a prompt appears that reads: "File not loaded completely."

Example of code I'm using to generate the CSV files:

import csv
with open('csv_test.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerow([1,2,3])
    writer.writerow([4,5,6])

Even the simple file generated by the above code fails to load in Excel. However, if I open the CSV file in a text editor and copy/paste the text into Excel, parse it with text to columns, and then save as CSV from Excel, then I can reopen the CSV file in Excel without issue. Do I need to pass an additional parameter in my scripts to make Excel parse the CSV files the same way it used to? Or is there some setting I can change in OS X Mavericks or Excel? Thanks.

Improve this question
6
  • 2
    Definitely not a python problem. As for how to use excel, ask on SuperUser... Commented Oct 26, 2013 at 21:04
  • 1
    To confirm: The code above (when properly indented of course) creates a CSV file that Excel is unable to read? Commented Oct 26, 2013 at 21:40
  • Excel for Mac, or Excel on Windows? Commented Oct 27, 2013 at 2:02
  • I have similar scripts but only use open(filename, 'w') rather than passing the b flag also. Give that a try? I can't replicate your issue either way though. Commented Oct 27, 2013 at 2:04
  • 1
    I'm getting the same error in Excel for Mac 2011 (14.3.8) opening CSVs generated using PHP 5.3.27's fputcsv() function on a CentOS box. The same files opened fine before I installed Mavericks. So this probably is a problem with Excel's CSV import under Mavericks. Commented Nov 6, 2013 at 16:14

2 Answers 2

Reset to default
6

Maybe I had the similar problem, the error message "SYLK: File format is not valid" when open python autogenerated csv file. The solution is really funny. The first two characters must not be I and D in uppercase (ID). Also see "SYLK: File format is not valid" error message when you open file.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You were right! this is hilarious and sad at the same time
lol, it works. I was 99% sure something like this will not work ;) Microsoft link dead btw
The Microsoft KB link is indeed dead—even the Internet Archive Wayback Machine can't find it. However Wikipedia has some interesting information about their proprietary Symbolic Link (SYLK) file format and CSV files.
3

Possible solution1: use *.txt instead of *.csv. In this case Excel (at least, 2010) will show you an import data wizard where you can specify delimiters, character encoding, field types, etc.

UPD: Solution2: The python "csv" module has a "dialect" feature. For example, the following modification of your code generates valid csv file for my environment (Python 2.7, Excel 2010, Windows7, locale with ";" list delimiters):

import csv
with open('csv_test2.csv', 'wb') as f:
    csv.excel.delimiter=';'
    writer = csv.writer(f, dialect=csv.excel)
    writer.writerow([1,2,3])
    writer.writerow([4,5,6])
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.