Links

Consolidate Excel files

Tags: #python #consolidate #files #productivity #snippet #operations #excel
Author: Florent Ravenel
Last update: 2023-04-12 (Created: 2021-10-07)
The objective of this notebook is to consolidate multiple Excel files (.xlsx) into one.

Input

Import library

Import the necessary libraries: os and pandas
import os
import pandas as pd

Variables

# Output
excel_output = "concatenate.xlsx"

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.
files = os.listdir()
my_list = []
for file in files:
if file.endswith(".xlsx"):
df = pd.read_excel(file)
my_list.append(df)
df_concat = pd.concat(my_list, axis=0)

Output

Export your dataframe to an Excel file.
df_concat.to_excel(excel_output, index=False)