Issues reading many excel files

user_13039962 0
Viewed 38

I'm reading many xls files with this code:

import os
import pandas as pd

#reading the name of files
path = os.getcwd()
files = os.listdir(path)
files_xls = [f for f in files if f[-3:] == 'xls']

#creating empty dataframe
dfs = pd.DataFrame()

#reading and append xls files
for i in files_xls:
    data = pd.read_excel(i, 'Sheet 1',converters={'CODIGO':str})
    dfs = dfs.append(data)

With this code, I can read all the xls files with no problem. But when I want to define the path, I get an error.

#reading the name of files
path = "/path/to/file"
files = os.listdir(path)
files_xls = [f for f in files if f[-3:] == 'xls']

#creating empty dataframe
dfs = pd.DataFrame()

#reading and append xls files
for i in files_xls:
    data = pd.read_excel(i, 'Sheet 1',converters={'CODIGO':str})
    dfs = dfs.append(data)

Error message:

FileNotFoundError: [Errno 2] No such file or directory: 'Acobamba000659a.xls'

How can I solve this?

python
2 Answers

os.listdir gives you file name not path. you can use jurez solution or just use glob

import glob

dfs = pd.DataFrame()
path = "/path/to/file/*.xls"

for i in glob.glob(path):
    data = pd.read_excel(i, 'Sheet 1',converters={'CODIGO':str})
    dfs = dfs.append(data)
user_85601670

You are probably forgetting that os.listdir() returns just the file names, without the path. You might try this:

files_xls = [os.path.join(path, f) for f in files if f[-3:] == 'xls']
user_83381000
Related