6

I'm using xlrd to process .xls files, and openpyxl to process .xlsx files, and this is working well.

Then I'm handed what is ostensibly a .xls file, so I try to xlrd.open_workbook(), and get:

XLRDError: Unsupported format, or corrupt file: Expected BOF record; found '<?xml ve'

I take a look at this question, and I surmise that my file, although ending with extension .xls, must actually be a .xlsx. And indeed, I can view it in a text editor:

<?xml version="1.0" encoding="UTF-8"?>
                      <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                       xmlns:x="urn:schemas-microsoft-com:office:excel"
                       xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                       xmlns:html="http://www.w3.org/TR/REC-html40">
:
:
:

(for privacy reasons, I can't post the whole file, but it's probably not required for our analysis).

So I surmise that if I just copy (cp) it to a .xlsx, I should be able to open it with openpyxl.load_workbook(), but I get:

BadZipfile: File is not a zip file

If it's actually an xls (unlikely) but can't be opened with xlrd, and if it is atcually an xlsx but can't be opened with openpyxl, even after I cp it to a .xlsx, what to do?

Note: If I open up the .xls in Excel, save it as a .xlsx, and retry with openpyxl, it does load fine, but this manual step is not a luxury I will have in the executing of my program.

Community
  • 1
  • 1
Pyderman
  • 14,809
  • 13
  • 61
  • 106
  • What verson of xlrd are you running? – James Jul 03 '15 at 18:03
  • 1
    Do a file diff between what excel generates and the original xls? Maybe the diff is something that you can change – Radu Diță Jul 03 '15 at 18:22
  • @Zenadix Thanks, but 1) The fact that xlrd *should* open it but doesn't is precisely my issue. 2) A text editor **can** open a .xlsx (the XML you see above is a copy-paste from a .xlsx opened in a text editor). Try it youself with any .xlsx and any advanced text editor. 3) It really doesn't matter what Excel should or shouldn't be able to do with the file. All that matters is that it does open in Excel. Therefore, it *is* an Excel file. – Pyderman Jul 03 '15 at 18:41
  • @Pyderman So maybe Excel can read your file, but that doesn't mean your file is a proper `xlsx` or `xls` file. Excel can read a lot of other formats too. (Read https://support.office.com/en-ca/article/File-formats-that-are-supported-in-Excel-a28ae1d3-6d19-4180-9209-5a950d51b719) – Zenadix Jul 03 '15 at 18:54
  • If this is an .xlsx file with an .xls extension then openpyxl will reject it and say that xls files are not supported. What version of openpyxl are you using? – Charlie Clark Jul 04 '15 at 09:27

3 Answers3

8

One thing is clear: The file you're trying to open has a different format than its extension suggests.

As you already know, Excel file formats include (but are not limited to) xls and xlsx.

  • The Excel 2003 format (xls) is a binary format. This means that if you open a xls file with a text editor, you'll just see gibberish.

  • The Excel 2007 format (xlsx) is quite different. A xlsx file is a zip file with a bunch of XML files inside. You can use a zip archiver to extract the contents of the xlsx file. Then, you can edit the XML files using any text editor. However, opening a xlsx file directly with a text editor is like opening a zip file with a text editor: You'll just see gibberish.

The fact that you can open your file with a text editor (and read its contents) shows that it's neither a xls file nor a xlsx file. Your file is neither a binary file nor a zip file, it's a plain XML file.

Moreover, this error message says a lot.

BadZipfile: File is not a zip file

It means that openpyxl is trying to open your file as a xlsx file and therefore a zip file. But when it tries to extract its contents, it fails, because your file isn't even a zip file.

But if the file is neither a xlsx file nor a xls file, how can Microsoft Excel read it? I wondered that too. After some research, I believe your file has the XML Spreadsheet 2003 file format. This example looks very similar to the file content you posted. Since Microsoft Excel supports this format, it's no wonder that it can read your file.

Unfortunately, Python libraries such as xlrd and openpyxl only support xls and xlsx file formats, so they won't be able to read your file. I think you'll just have to manually convert it to a supported format.

Zenadix
  • 15,291
  • 4
  • 26
  • 41
  • Superb answer, and you rode out my fist-waving comments as long as I resisted your arguments. Thank you. A model response to a Stack Overflow question. – Pyderman Jul 03 '15 at 21:07
3

I am not on OSX, so this is not tested. You may be able to use the appscript package, despite it's lack of support, to open the offending file and the resave it.

from appscript import *
excel = app('Microsoft Excel')
wb = excel.open('/path/to/file.xls')
wb.save_as('/path/to/fileout.xlsx', file_format=k.XLSX_file_format)
#not sure the exact name of k.excel_file
James
  • 32,991
  • 4
  • 47
  • 70
0

I had a similar problem. It turned out that it needed the absolute file path. E.g., "c:/dir/filename.xlsx" instead of "filename.xlsx". Relative paths worked on osx, but not on Windows.

Nathan Garrett
  • 93
  • 5