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
– Dan, Commented Jul 26, 2016 at 15:47
Is the error while converting the file, or after it's converted when you try to open it? — Will
– Will, Commented Jul 26, 2016 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
– Aaron Dank, Commented Jul 26, 2016 at 15:56

Graham · Accepted Answer · 2017-09-27 05:50:33Z

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, 2017 at 5:50

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Jul 26, 2016 at 16:17

Will

4,4696 gold badges35 silver badges52 bronze badges

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

1 Comment

Aaron Dank Over a year ago

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

Community · Accepted Answer · 2017-05-23 11:33:26Z

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, 2017 at 11:33

CommunityBot

11 silver badge

answered May 19, 2017 at 20:08

Drew Bennett

4833 gold badges6 silver badges25 bronze badges

Collectives™ on Stack Overflow

Converting Excel to CSV python

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related