Consolidate Excel files
Tags: #python #consolidate #files #productivity #snippet #operations #excel
Last update: 2023-04-12 (Created: 2021-10-07)
The objective of this notebook is to consolidate multiple Excel files (.xlsx) into one.
Import the necessary libraries: os and pandas
import os
import pandas as pd
# Output
excel_output = "concatenate.xlsx"
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)
Export your dataframe to an Excel file.
df_concat.to_excel(excel_output, index=False)
Last modified 1mo ago