How to iterate through multiple excel files using python

Question

I am trying to develop a python script that will iterate through several Excel .xlsx files, search each file for a set of values and save them to a new .xlsx template.

The issue I'm having is when I'm trying to get a proper list of files in the folder I'm looking at. I'm saving these filenames in a list variable 'fileList' to manage iteration.

When I run the code os.chdir(sourcepath), I'm constantly getting a FileNotFoundError: [WinError 2] The system cannot find the file specified: C:\\Users\\username\\PycharmProjects\\projectName\\venv\\Site List\\siteListfolder

I think this has to do with the '\\' that is displaying in the error, but when I run a print(sourcepath) in this code, the path is properly displayed, with just one '\' between each subdirectory instead of two.

I need to be able to get the list of files in the siteListfolder, and be able to iterate through them using this kind of logic:

priCLLI = sys.argv[1]
secCLLI = sys.argv[2]
sourcepath = os.path.join(homepath, 'Site List', f'{priCLLI}_{secCLLI}')
siteListfolder = os.listdir(sourcepath)
for file in siteListfolder:
   for row in file:
      <script does its work>

'siteListfolder = os.listdir(sourcepath)' is generating the error Thanks to all in advance for supporting this kind of forum.

"When I run the code os.chdir(sourcepath)" - but that isn't part of the actual code you showed. "I think this has to do with the '\\' that is displaying in the error" No; that's normal - it shows you a representation of the path (a string), i.e. the same thing that you would have to type into your Python code to get the actual string. It has double backslashes because those would be needed as escape sequences in code. The problem is that the folder actually isn't there. — Karl Knechtel
– Karl Knechtel, Commented Jul 31, 2021 at 19:50
sorry for that 'os.chdir' typo, and the folder does indeed exist, I can see it in Windows. So I'm not sure why Python doesn't see it.... — GrandMasterD
– GrandMasterD, Commented Jul 31, 2021 at 19:58
Are you able to make it work if you reduce the code to just building the path and doing os.listdir? Are you able to make it work if you look for parent directories of the one causing the problem? Also, this is probably just an artifact of your anonymization process; but I notice that the example path you show doesn't have an underscore in siteListfolder, but your code will clearly produce an underscore with f'{priCLLI}_{secCLLI}'. — Karl Knechtel
– Karl Knechtel, Commented Jul 31, 2021 at 20:00
No on question 1. If I declare sourcepath = r'<path>', I still get the error. Yes on question 2. if I build a fileList based on the parent directory, which is where the script is located, then yes it does return a list of filenames in the directory, ``` homepath = os.getcwd() fileList = os.listdir(homepath) ``` — GrandMasterD
– GrandMasterD, Commented Jul 31, 2021 at 20:06
Are you sure you didn't compute the name of a file, and then attempt to list its contents as a directory? — Karl Knechtel
– Karl Knechtel, Commented Jul 31, 2021 at 20:08

Thaer Nofal · Accepted Answer · 2021-11-02 13:09:47Z

1

import os

directory = ('your/path/directory')
Source_Workbook = []
for filename in os.listdir(directory):
    if filename.endswith(".xlsx"):
        Source_Workbook.append(filename)
        print(Source_Workbook)

answered Nov 2, 2021 at 13:09

Thaer Nofal

234 bronze badges

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

1 Comment

PCM Over a year ago

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

Collectives™ on Stack Overflow

How to iterate through multiple excel files using python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related