Python - Read multiple excel files and print data in a folder

Question

I can read multiple excel files in a folder by using os:

import matplotlib.pyplot as plt
from matplotlib import cm
import numpy as np
import os
import pandas as pd
import math

folder = r'C:\Users\Denny\Desktop\Work\test_read'
files = os.listdir(folder)

for file in files:
    if file.endswith('.xlsx'):
        df = pd.read_excel(os.path.join(folder,file), header = None, skiprows=[0,1])

files

And it indeed shows three excel files:

Out[33] ['test_3d.xlsx', 'test_3d1.xlsx', 'test_3d2.xlsx']

But if I want to read the data in, for example, test_3d.xlsx, I used

test_3d.xlsx

or

print(text_3d)

I got

NameError: name 'test_3d' is not defined

I can only get the last one, i.e., test_3d2, by

print(df)

My question is how to get the data of the other two .xlsx files?

Thanks!

mozway · Accepted Answer · 2021-08-29 09:04:27Z

1

You overwrote the first two dataframes as you used the same name.

Instead save them in a dictionary:

dfs ={}
for file in files:
    if file.endswith('.xlsx'):
        dfs[file[:-5]] = pd.read_excel(os.path.join(folder,file), header = None, skiprows=[0,1])

You can then access them by name:

dfs['test_3d']

answered Aug 29, 2021 at 9:04

mozway

267k13 gold badges56 silver badges106 bronze badges

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

2 Comments

Denny Over a year ago

Do we have to use [:-5] no matter how many excel files we want to read? I mean if using [:-6]. Thanks!

mozway Over a year ago

This is just to remove the ".xlsx'", this is the simplest but there are other methods to remove extensions from strings.

Collectives™ on Stack Overflow

Python - Read multiple excel files and print data in a folder

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related