xlrd - Python: TypeError: coercing to Unicode: need string or buffer, PosixPath

Question

I am trying to run this python script which is trying to rename the xslx file using filename found in a column within the same xslx. (Original script found Here)

import os
from pathlib import Path

import xlrd

def rename_excel_files(path):
    path = Path(path)

    for filename in os.listdir(path):
        file_path = path.joinpath(filename)
        excel_file = xlrd.open_workbook(file_path)
        work_sheet = excel_file.sheet_by_index(1)
        dest_path = path.joinpath(work_sheet.cell_value(20, 0) + '.xlsx')
        os.replace(file_path, dest_path)

if __name__ == '__main__':
     rename_excel_files(r'/Users/User/OQC')

But getting the following error when running it python2:

  File "oqc_rename.py", line 18, in <module>
    rename_excel_files(r'/Users/User/OQC')
  File "oqc_rename.py", line 10, in rename_excel_files
    for filename in os.listdir(path):
TypeError: coercing to Unicode: need string or buffer, PosixPath found

Updated: Error I get when I run python3:

 File "/Users/user/OQC/oqc_rename.py", line 19, in <module>
    rename_excel_files(r'/Users/user/OQC')
  File "/Users/user/OQC/oqc_rename.py", line 12, in rename_excel_files
    excel_file = xlrd.open_workbook(file_path)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/xlrd/__init__.py", line 148, in open_workbook
    bk = book.open_workbook_xls(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/xlrd/book.py", line 92, in open_workbook_xls
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/xlrd/book.py", line 1278, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/xlrd/book.py", line 1272, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'x00x00x00x01Bud1'

Based on what I have searched, I think the issue might be that I have the file already open prior to renaming? Confirmed xlsx file is not corrupt. However, I am not sure how to get around this issue as I am a python noob.

Thanks in advance!

question from:https://stackoverflow.com/questions/65837737/python-typeerror-coercing-to-unicode-need-string-or-buffer-posixpath

Answer 1

xlrd.open_workbook is expecting a string, just represent the Path object as one and it should be happy

-xlrd.open_workbook(path)
+xlrd.open_workbook(str(path))

Do note that the official docs say non-.xls files are not supported: https://pypi.org/project/xlrd/

This library will no longer read anything other than .xls files. For alternatives that read newer file formats, please see http://www.python-excel.org/.