In this article, we will see how to combine all Excel files present in a folder into a single file.
The python libraries used are:
- Pandas: Pandas is a python library developed for a python programming language for manipulating data and analyzing the data. It is widely used in Data Science and Data analytics.
- Glob: The glob module matches all the pathnames matching a specified pattern according to rules used by Unix Shell.
Excel files used:
Three Excel files will be used which will be combined into a single Excel file in a folder using python. The three Excel files are x1.xlsx, x2.xlsx, and x3.xlsx:
- Firstly we have to import libraries and modules
- Setting the path of the folder where files are stored. This line of code will fetch the folder where the files are stored.
- Displaying the names of files in the folder using Glob module. glob.glob( ) function will search for all the files in the given path with .xlsx extension. print(filenames) displays the names of all the files with xlsx extension.
- Initializing Empty data frames. A Data Frame is a Table data structure in python for analyzing and manipulating the data. Here we have to initialize an empty data frame for storing the combined data in the three files
- Iterating through all the files in the folder one by one. We have to iterate through each file using for loop. The pd.concat() function will concatenate all the multiple sheets present in the excel files as in the case of the third excel file in this example and will store in a variable called df. finalexcelsheet.append( ) function will append the data present in df variable into finalexcelsheet one by one. Hence with this piece of code, you will be able to combine the Excel files with ease
- Displaying the combined data. To display the combined file just write print(finalexcelsheet).
- Insert the combined data into a new Excel file.
Below is the complete python program based on the above approach:
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course