How to loop through a folder in Python-CodePudding

I am a new python user and I am trying to loop through all the items in a set file. Here is my code this far -

import os
import pandas as pd
print(os.getcwd())


for files in os.listdir(r"../Python/Rev Renewables/Inputs/Option Inputs/"):
    file = pd.read_excel(files,sheet_name='ContractSpecs')
    print(file)

When I load the for loop without the pd.read_excel it prints the names of each of the sheets in the console yet when I add in the read_excel portion I receive an error stating "FileNotFoundError: [Errno 2] No such file or directory: 'O2.LS Power SP15 Option-EY.xlsx'" I am not sure why it is able to locate the file and the correct name yet when it attempts to print to excel it can't locate the file name even when they are identical.

Thank you

CodePudding user response：

os.listdir in python lists all the files in a given directory you probably should give the entire path to pd.read_excel


import os
import pandas as pd
print(os.getcwd())
path = r"../Python/Rev Renewables/Inputs/Option Inputs/"

for files in os.listdir(path):
    file = pd.read_excel(path files,sheet_name='ContractSpecs')
    print(file)

CodePudding user response：

Make sure there are only xlsx files in the directory you are accessing and that you are executing the script from that directory. For me your code worked because I executed the code from the same folder where the xlsx files are placed, but if you are running it from, lets say, a directory folder, and you have the xlsx files in folder/files/, python will try to read files and get a list of names like file.xlsx, but you will need this: ./files/file.xlsx to be able to locate the file.

CodePudding user response：

I would also recommend looking into the pathlib functionalities instead of using os, although both work in this case. This module provides an object oriented approach and has worked awesomely well for me. Although I'm not experienced enough to really have an comprehensive overview over the pros and cons of both methods, I think pathlib "can do more" than os... Pathlib vs. os.path.join in Python