"No such file or directory exist", but it obviously does-CodePudding

I'm trying to import multiple excel files to a DataFrame but I get the error: FileNotFoundError: [Errno 2] No such file or directory: 'test1.xlsx'

The code:

path=os.getcwd()
files = os.listdir(path "/testimport")

df = pd.DataFrame()

for f in files:
    data = pd.read_excel(f,
                           sheet_name = "Data",
                           skiprows = range(0, 4),
                           usecols = "B:I,P:V")
    df = df.append(data)

However, when I try to print the files it actually do work:

in:    for f in files:
        print(f)
        
out:    test1.xlsx
        test2.xlsx

How is this possible and how to solve it? I've tried absolute path but same result.

CodePudding user response：

You should provide the full path (including the directory names) for the input files. Currently, you are only providing the file names. So, the read line should be something like below:

data = pd.read_excel(os.path.join(path, "testimport", f), sheet_name = "Data", skiprows = range(0, 4), usecols = "B:I,P:V")

Note: os.path.join is the safer way to concatenate directory/file names, otherwise your code would be error prone in the case of extra forward slashes or when running in different operating systems.

CodePudding user response：

Alternatively to the posted answers, you can actually simplify your code by using scandir instead of listdir, which yields DirEntry objects, which have the full path available as a path attribute

import os

df = pd.DataFrame()

for f in os.scandir(os.path.join(os.getcwd(), "testimport")):
    data = pd.read_excel(
        f.path,
        sheet_name="Data",
        skiprows=range(0, 4),
        usecols = "B:I,P:V"
    )
    df = df.append(data)

CodePudding user response：

You can also changes the current working directory os.chdir('./testimport'):

os.chdir('./testimport')
path=os.getcwd()
 
files = os.listdir(path)
 
df = pd.DataFrame()
 
for f in files:
    data = pd.read_excel(f,
                           sheet_name = "Data",
                           skiprows = range(0, 4),
                           usecols = "B:I,P:V")
    df = df.append(data)