CODEWITHSUNDEEP
pythonxlsx
mohan111
mohan111

Reputation: 8865

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

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

Upvotes: 0

Views: 300

Answers (2)

Alexandre B.
Alexandre B.

Reputation: 5502

Try this path in the for loop:

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

Upvotes: 0

SM Abu Taher Asif
SM Abu Taher Asif

Reputation: 2331

The path is not correct. Use :

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

Upvotes: 1

Related Questions