0

Hi everyone I am trying to extract data from a list of spreadsheets on a folder using python with openpyxl and the os module. I retrieve the list of file, get the path from them and use it to load the workbook but each time the path trigger an error as each backslash is doubled, so if my file is on c:\Users\me\myfile it ends as C:\Users\me\myfile

here is the code I used

import os from openpyxl import Workbook,load_workbook

mydir = r'C:\Users\me\PycharmProjects\

for file in os.listdir(mydir):

if file.endswith('.xlsx'):
    filepath = os.path.abspath(file)
    wb = load_workbook(filepath, read_only=True)
    print(wb.sheetnames)
Fabio R
  • 1
  • 1
  • 2
    Please edit the question to include the code. See [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). – sj95126 Aug 12 '22 at 17:32
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. – Community Aug 12 '22 at 19:26

2 Answers2

0

Few things:

  1. The double backslash is not incorrect, it is just how Python represents it to the user.
  2. If you use read_only=True, you will need to explicitly close using wb.close() as it uses lazy loading.
hlin03
  • 125
  • 7
  • Well i understand that, and if I print it the path is correct. My issue is that when i use it as input for the load_workbook it trigger an error as the file does not exist and output the path with double slash as the cause – Fabio R Aug 13 '22 at 14:35
  • Thanks, for more clarity, can you please share the exact error you are receiving? It can help us better understand the issue and help you resolve :) – hlin03 Aug 14 '22 at 03:37
  • FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\me\\PycharmProjects\\ExcelManager\\sheet.xlsx' ok I spotted the problem, the absolute path left out a folder. The spreadsheets are inside a folder called 'testSheets' which is not added to the absolute path. The absolute path should be 'C:\\Users\\me\\PycharmProjects\\ExcelManager\\testSheets\\sheet.xlsx' – Fabio R Aug 15 '22 at 08:10
0

Sorted the issue,

using the absolute path of the os module to build the path for the load_workbook is not correct as it remove the parent folder of the files from the path

I solved using only the files name and the starting folder path

Fabio R
  • 1
  • 1