Converting Excel to CSV python

Question

I am using xlrd to convert my .xls Excel file to a CSVfile yet when I try to open the workbook my program crashes sending an error message

bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/xlrd/book.py", line 1224, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found 'Chrom\tPo'

The Chrom\tPo is part of my header for the excel file yet I don't understand what the error is with the Excel file and how to change it.

The program crashes right when i try to open the excel file using xlrd.open_workbook('Excel File')

What version of xlrd are you using, and what type of Excel file (xls vs xlsx)? — Dan, Jul 26 '16 at 15:47
Is the error while converting the file, or after it's converted when you try to open it? — Will, Jul 26 '16 at 15:48
Dan - The version of xlrd i am using is 1.0.0 and i am using .xls. And the error occurs when converting the file when I try to xlrd.open_workbook(excelfile) — Aaron Dank, Jul 26 '16 at 15:56

score 1 · Answer 1 · edited Sep 27 '17 at 05:50

1

I would use openpyxl for this.

import openpyxl
wb = openpyxl.load_workbook(file_name)
ws = wb.worksheets[page_number]
table = []
for row_num in range(ws.get_highest_row()):
  temp_row = []
  for col_num in range(ws.get_highest_column()):
    temp_row.append(ws.cell(row=row_num, col=col_num).value)
  table.append(temp_row[:])

This will give you the contents of the sheet as a 2-D list, which you can then write out to a csv or use as you wish.

If you're stuck with xlrd for whatever reason, You may just need to convert your file from xls to xlsx

edited Sep 27 '17 at 05:50

Graham

7,431
18
59
84

answered Jul 26 '16 at 16:17

Will

4,299
5
32
50

I realized that the error was that even though the file was saved with the name .xls it was actually a tab delimited file and I needed to save it as a xls file and then everything worked.. Thank you – Aaron Dank Jul 26 '16 at 16:23

score 0 · Answer 2 · edited May 23 '17 at 11:33

0

Here is an answer from a previous question: How to save an Excel worksheet as CSV from Python (Unix)?

The answer goes for openpyxl and xlrd.

edited May 23 '17 at 11:33

Community

1
1

answered May 19 '17 at 20:08

Drew Bennett

483
3
6
25

Converting Excel to CSV python

2 Answers2