Creating a loop to read excel files - python

Question

I am trying to create a loop which will read each excel file and perform the calculations on them.

What I have is:

n=150
for i in range(n):
   dw = pd.read_excel(str(i) + '.xlsx')
   print(dw)

(Thereafter, I perform calculations on them - I just printed the result to see if it imported the files correctly)

However when I run this, it runs the last excel file (file named 150.xlsx) 150 times. (Instead of each of the files once.)

Is there a way around this?

You will not be able to get to 150 ? It will run from 0 to 149. Trying changing n to 151 — GhandiFloss
– GhandiFloss, Commented Sep 4, 2020 at 11:14
the above code should work. print (i) to confirm the iteration values. you might added dw = pd.read_excel(str(n) + '.xlsx') check the running code properly. — Mohamed Thasin ah
– Mohamed Thasin ah, Commented Sep 4, 2020 at 11:15
Thank you! I have checked and its still not working properly. On PyCharm too - any other possible reason for this that you maybe know? — Laura
– Laura, Commented Sep 4, 2020 at 17:56

Rouben · Accepted Answer · 2020-09-04 23:06:54Z

1

As Mohamed already stated, your code should work fine, maybe you accidentally wrote pd.read_excel(str(n)+'.xlsx') instead.

Alternatively, you can use os.walk to list all files in a directory and use the filenames instead:

import os

path = '<your path to excel files>'

files = []

# getting all files in directory
for (dirpath, dirnames, filenames) in os.walk(path):
    files.extend(filenames)

# opening every .xlsx file and performing calculations
for f in files:
    if f.endswith('.xlsx'):
        dw = pd.read_excel(os.path.join(path, f))
        # your calculation here

edited Sep 4, 2020 at 23:06

answered Sep 4, 2020 at 11:20

Rouben

1113 silver badges15 bronze badges

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

2 Comments

Laura Over a year ago

Thank you! The nature of my calculations makes the read_excel function more suitable (the calculations require me to refer to previous calculations based on the same index name of the folder ie. 'i'.) Hmm, I find it strange that both you and Mohamed say it should be working but it's not - both on pycharm and python. Could there be another explanation for this?

Rouben Over a year ago

I edited my answer to utilize the read_excel() function now, hope this helps. Regarding the other problem, I tried it locally and had no issues. I created 4 xlsx files named 1.xlsx to 4.xlsx each containing "This is <number>" as data and used exactly your code without any errors. There are multiple explanations for this, maybe your data is accidentally all the same, maybe something else. But the code snipped you provided is 100% correct. If you can share the actual script your working on maybe I can help a bit more.

Collectives™ on Stack Overflow

Creating a loop to read excel files - python

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