How to read multiple excel files and load into one excel sheet

Question

I have multiple excel files in one directory Directory :

H:\Learning\files

Files :

customer-status.xlsx
sales-feb-2014.xlsx
sales-jan-2014.xlsx
sales-mar-2014.xlsx
sample-salesv3.xlsx

I'm trying to load sales.xlsx files into one master sales file. here is my script :

import pandas as pd
import os
import glob


all_data = pd.DataFrame()
for f in glob.glob('H:Learning/sales*.xlsx'):
    df = pd.read_excel(f)
    all_data = all_data.append(df, ignore_index=True)
print(all_data)
writer = pd.ExcelWriter('mastersales.xlsx', engine='xlsxwriter')
all_data.to_excel(writer, sheet_name='Sheet1')
writer.save()

when i'm trying to print sales*.xlsx files and it's showing empty Data frames

Empty DataFrame
Columns: []
Index: []

More over I have implemented this code from this SO answered question

How to concatenate three excels files xlsx using python? but this is not giving me required result

I have tried like that but it is not traversing into files folder where sales xlsx files are there — mohan111, Jun 25 '19 at 08:30
Does `print(glob.glob('H:Learning/*.xlsx'))` show your expected files ? If not, you might not be in the right folder. The rest of the code looks good.. — Alexandre B., Jun 25 '19 at 08:35
print funciton outside the loop is no problem, the loop should save all the required data in `all_data` — PV8, Jun 25 '19 at 08:39
I don't know if it's a typo, but notice that in the line `for f in glob.glob('H:Learning/sales*.xlsx')` the address don't match the directory, try `print(df)` inside the for loop and see if it prints anything — Rotem Tal, Jun 25 '19 at 08:46
By reading your question a second time, it seems you are not looking at the right directory since your files are in `H:\Learning\files`. Replace the `for` lop by the right path: `for f in glob.glob('H:Learning/files/*.xlsx'):`. — Alexandre B., Jun 25 '19 at 08:50
@AlexandreB. this is I have given : for f in glob.glob(r'H:\Learning\files*.xlsx'): — mohan111, Jun 25 '19 at 09:30

score 1 · Answer 1 · answered Jun 25 '19 at 08:56

1

The path is not correct. Use :

glob.glob(r'H:\Learning\sales*.xlsx')

answered Jun 25 '19 at 08:56

SM Abu Taher Asif

2,221
1
12
14

If you have folders inside "Learning" please add them to the path and then try. The code I have shared is worked in my Windows Machine – SM Abu Taher Asif Jun 25 '19 at 09:53
I have added full path and it is working and I have gave 10 from my side – mohan111 Jun 25 '19 at 09:59

Alexandre B. · Accepted Answer · 2019-06-25T09:51:56.210

0

Try this path in the for loop:

glob.glob(r'H:\Learning\files\sales*.xlsx')

edited Jun 25 '19 at 09:51

answered Jun 25 '19 at 09:35

Alexandre B.

5,387
2
17
40

for f in glob.glob(r'H:\Learning\files\sales*.xlsx'): – mohan111 Jun 25 '19 at 09:49

How to read multiple excel files and load into one excel sheet

2 Answers2