Consolidate Excel files
Tags: #python #consolidate #files #productivity #snippet
Author: Florent Ravenel
The objective of this notebook is to consolidate multiple Excel files (.xlsx) into one.

Input

Import library

Import the necessary libraries: os and pandas
1
import os
2
import pandas as pd
Copied!

Variables

1
# Output
2
excel_output = 'concatenate.xlsx'
Copied!

Model

Use a for loop to
  • List all the files in the current directory with os.listdir().
  • Filter files with the .endswith(‘.xlsx’) method.
  • Make sure the files will be stored into a list called my_list and then combined with pd.concat()
Then
  • Return a dataframe and name it df_concat.
1
files = os.listdir()
2
my_list = []
3
for file in files:
4
if file.endswith('.xlsx'):
5
df = pd.read_excel(file)
6
my_list.append(df)
7
8
df_concat = pd.concat(my_list, axis=0)
Copied!

Output

Export your dataframe to an Excel file.
1
df_concat.to_excel(excel_output, index=False)
Copied!
Copy link
Edit on GitHub