

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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 −
- Related Questions & Answers
- Python - Read all CSV files in a folder in Pandas?
- How to convert PDF files to Excel files using Python?
- How to Merge all CSV Files into a single dataframe – Python Pandas?
- How to read all files in a folder to a single file using Java?
- How to get list of all files/folders from a folder in Java?
- How to read all excel files under a directory as a Pandas DataFrame ?
- How to read multiple text files from a folder in Python?(Tkinter)
- How to process excel files data in chunks with Python?
- How to Copy files or Folder without overwriting existing files?
- How to copy files from one folder to another using Python?
- Ask a user to select a folder to read the files in Python
- How to write files to asset folder in android?
- Java program to merge contents of all the files in a directory
- How to merge multiple files into a new file using Python?
- How to copy certain files from one folder to another using Python?
Advertisements