Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Python - How to Merge all excel files in a folder
To merge all excel files in a folder, use the Glob module and the append() method.
Let’s say the following are our excel files on the Desktop −
Sales1.xlsx
Sales2.xlsx
Note − You may need to install openpyxl and xlrd packages.
At first, set the path where all the excel files you want to merge are located. Get the excel files and read them using glob −
path = "C:\Users\amit_\Desktop\"
filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)Next, create an empty dataframe for the merged output excel file that will get the data from the above two excel files −
outputxlsx = pd.DataFrame()
Now, the actual process can be seen i.e. at first, iterate the excel files using for loop. Read the excel files, concat them and append the data −
for file in filenames: df = pd.concat(pd.read_excel(file, sheet_name=None), ignore_index=True, sort=False) outputxlsx = outputxlsx.append(df, ignore_index=True)
Example
Following is the code −
import pandas as pd
import glob
# getting excel files to be merged from the Desktop
path = "C:\Users\amit_\Desktop\"
# read all the files with extension .xlsx i.e. excel
filenames = glob.glob(path + "\*.xlsx")
print('File names:', filenames)
# empty data frame for the new output excel file with the merged excel files
outputxlsx = pd.DataFrame()
# for loop to iterate all excel files
for file in filenames:
# using concat for excel files
# after reading them with read_excel()
df = pd.concat(pd.read_excel( file, sheet_name=None), ignore_index=True, sort=False)
# appending data of excel files
outputxlsx = outputxlsx.append( df, ignore_index=True)
print('Final Excel sheet now generated at the same location:')
outputxlsx.to_excel("C:/Users/amit_/Desktop/Output.xlsx", index=False)Output
This will produce the following output i.e., the merged excel file will get generated at the same location −
11K+ Views
