-2

I am very new to Python, here I'm trying to open an Excel XLS file in Python code but it is showing me some error as below.

Code:

from xlrd import open_workbook
import os.path
wb = open_workbook('K:\NEW_GIS\NEW Data\Garpani_NLRMP_Data\ROR_Files\part-1.xlsx')

I am also following below link for upgrade version: https://openpyxl.readthedocs.org/en/latest/

Error:

Traceback (most recent call last):

  File "C:/Users/n/.qgis2/python/plugins\Importtool\Import_tool.py", line 327, in select_output_file_5
    workbook = open_workbook(r'K:\NEW_GIS\NEW Data\Garpani_NLRMP_Data\ROR_Files\Part-1.xlsx')

  File "C:\PROGRA~1\QGISWI~1\apps\Python27\lib\site-packages\xlrd\__init__.py", line 429, in open_workbook
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)

  File "C:\PROGRA~1\QGISWI~1\apps\Python27\lib\site-packages\xlrd\__init__.py", line 1545, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])

  File "C:\PROGRA~1\QGISWI~1\apps\Python27\lib\site-packages\xlrd\__init__.py", line 1539, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)

XLRDError: Unsupported format, or corrupt file: Expected BOF record; found 'PK\x03\x04\x14\x00\x06\x00'

closed as off-topic by Simbamangu, Brad Nesom, Mapperz Aug 17 '15 at 16:15

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general Information Technology, with no clear GIS component, are off-topic here, but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software) and Database Administrators (relational databases)" – Simbamangu, Brad Nesom, Mapperz
If this question can be reworded to fit the rules in the help center, please edit the question.

  • What error are you getting? – Evil Genius Aug 14 '15 at 13:45
  • I am getting :Traceback (most recent call last): File "C:/Users/n/.qgis2/python/plugins\Importtool\Import_tool.py", line 327, in select_output_file_5 workbook = open_workbook(r'K:\NEW_GIS\NEW Data\Garpani_NLRMP_Data\ROR_Files\Part-1.xlsx') File "C:\PROGRA~1\QGISWI~1\apps\Python27\lib\site-packages\xlrd_init_.py", line 429, in open_workbook biff_version = bk.getbof(XL_WORKBOOK_GLOBALS) – Ashwin Bhayal Aug 17 '15 at 12:07
  • 1
    please do not place information for the question in comments. Edit original question. – Brad Nesom Aug 17 '15 at 16:03
2

You haven't escaped your backslashes. Try using a raw string by placing the letter 'r' in front of the string.

from xlrd import open_workbook
import os.path
wb = open_workbook(r'K:\NEW_GIS\NEW Data\Garpani_NLRMP_Data\ROR_Files\part-1.xlsx')
  • I am getting :Traceback (most recent call last): File "C:/Users/n/.qgis2/python/plugins\Importtool\Import_tool.py", line 327, in select_output_file_5 workbook = open_workbook(r'K:\NEW_GIS\NEW Data\Garpani_NLRMP_Data\ROR_Files\Part-1.xlsx') File "C:\PROGRA~1\QGISWI~1\apps\Python27\lib\site-packages\xlrd_init_.py", line 429, in open_workbook biff_version = bk.getbof(XL_WORKBOOK_GLOBALS) – Ashwin Bhayal Aug 17 '15 at 12:08
  • Are you sure you didn't leave out the last line of the error? What you pasted shows the traceback but I don't see an error message. Please edit the full error message into your original question – BruceDoh Aug 17 '15 at 12:24
  • I have edited the full error message into my original question. – Ashwin Bhayal Aug 17 '15 at 12:28
2

Excel workbooks carry a considerable amount of formatting that can make them tough to deal with in python. I have found that it is easier to work with tabular data in python using the built-in csv module. In case you aren't familiar, CSV is a standard way to simplify reading & writing of a singular tabular data table.

To export as a CSV open the spreadsheet in Excel and choose 'Save as'. In the 'Save as type' drop-down choose 'CSV (Comma Delimited)'

Once your data is in the CSV format, use the built-in 'csv' module to read it contents.

import csv
csv_file_path = r"..\myData.csv"

with open(csv_file_path, 'r') as csv_file:
    for row in csv.reader(csv_file):
        print row
1

Your spreadsheet is in Excel 2007+ .xlsx OOXML format. The link in your question is to the openpyxl docs which can read .xlsx but you're not using openpyxl, you're trying to open the .xlsx file with xlrd which only reads the old Excel binary .xls format.

Here is some more information on working with Excel spreadsheets in Python.

Not the answer you're looking for? Browse other questions tagged or ask your own question.