Merge All Excel Files in a Folder with Python

Imagine that you have one Excel export for every month of the year. All twelve files are in the same folder, and now you want to combine them into one workbook so you can analyze the whole year.

In principle, this is the same task as merging three Excel files. Python can find all the .xlsx files in a folder, open them one by one, and combine the rows. Whether the folder contains three files or twelve does not make much difference to the code.

The interesting problem is what happens when the files are not quite identical. During a year, someone may rename a column, add a new field, or change an export template. Python can still merge the files without showing an error, but the resulting data may not mean what you think it means.

In this tutorial, we will merge twelve monthly sales files in MLJAR Studio, check that every file was loaded, find a column that changed during the year, fix it, and save a clean full-year Excel workbook.

Get the data

Download the demo files from datasets-for-start and put them in:

~/Documents/sales/
~/Documents/sales/

The folder contains twelve Excel workbooks, from 2025-01.xlsx through 2025-12.xlsx, with one file for each month of 2025.

The files are almost identical, but there are two small differences. In one workbook, the revenue column was renamed to Revenue. Another workbook has an additional discount_pct column that does not exist in the earlier files.

These are small changes, but they are common in real spreadsheet exports. Someone may rename a column, add a new field, or update an export template during the year. Python can still merge the files, so our job is not only to combine them, but also to check what changed.

Load and merge all Excel files

Open MLJAR Studio and choose Start a New AI Conversation. In the prompt box, type:

load xlsx files from ~/Documents/sales, merge them and add source_file column, print how many files loaded
load xlsx files from ~/Documents/sales, merge them and add source_file column, print how many files loaded

We are asking for one extra thing here: the number of files that were loaded. It is a very simple check, but it can catch problems immediately. If there are twelve Excel files in the folder, the first thing we want to see is that Python actually found twelve.

Studio loads the files and reports:

Files loaded: 12
Shape: (5866, 10)
Columns (10): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue', 'source_file', 'Revenue', 'discount_pct']
Files loaded: 12
Shape: (5866, 10)
Columns (10): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue', 'source_file', 'Revenue', 'discount_pct']

The first result looks good. Twelve files were loaded, exactly as expected, and together they contain 5,866 rows.

The column count is more interesting. Our original sales format has seven columns, and we asked Studio to add source_file, so we might expect eight columns in the merged dataframe. Instead, there are ten.

The two additional columns are Revenue, with a capital R, and discount_pct. This does not mean the merge failed. In fact, Python did exactly what we asked it to do. The difference tells us that the structure of the source files changed somewhere during the year.

What Python did

You do not need to write the merge code yourself, but it is useful to understand what Studio generated. This makes it much easier to see why slightly different spreadsheets can still be combined without producing an error.

Python code used to load and merge files:

from pathlib import Path
import pandas as pd

folder = Path("~/Documents/sales").expanduser()
files = sorted(folder.glob("*.xlsx"))

frames = []
for file in files:
    data = pd.read_excel(file)
    data["source_file"] = file.name
    frames.append(data)

df = pd.concat(frames, ignore_index=True, sort=False) if frames else pd.DataFrame()

print(f"Files loaded: {len(files)}")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()
from pathlib import Path
import pandas as pd

folder = Path("~/Documents/sales").expanduser()
files = sorted(folder.glob("*.xlsx"))

frames = []
for file in files:
    data = pd.read_excel(file)
    data["source_file"] = file.name
    frames.append(data)

df = pd.concat(frames, ignore_index=True, sort=False) if frames else pd.DataFrame()

print(f"Files loaded: {len(files)}")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()

The code first finds every .xlsx file in the folder. It then opens each workbook with pd.read_excel(), adds the filename to a new source_file column, and stores the dataframe in frames.

Finally, pd.concat() combines all of those dataframes into one. This is the useful part of using a loop: the code does not really care whether there are three files, twelve files, or fifty files in the folder.

There is also an important detail hidden inside pd.concat(). Pandas does not require every dataframe to have exactly the same columns. If one file has a column that the others do not have, pandas keeps that column and fills the missing positions with NaN.

That explains why we now have both revenue and Revenue. To a person, they probably mean the same thing. To pandas, they are two different column names.

Check that all twelve files were loaded

Before investigating the columns, let us first confirm that every monthly file contributed rows to the merged dataframe.

Ask Studio:

count rows per source_file
count rows per source_file

Studio generates:

source_counts = (
    df.groupby("source_file", dropna=False)
      .size()
      .reset_index(name="row_count")
      .sort_values("source_file")
      .reset_index(drop=True)
)

source_counts
source_counts = (
    df.groupby("source_file", dropna=False)
      .size()
      .reset_index(name="row_count")
      .sort_values("source_file")
      .reset_index(drop=True)
)

source_counts

The result is:

All twelve files are present, and their row counts add up to 5,866. Some months contain more orders than others, which is perfectly normal. There is no obvious missing or duplicated month.

This is an important distinction. The row-count check passes, but that does not mean the dataset is already correct. It tells us that the files arrived. It does not tell us that values from those files ended up in the correct columns.

That is why it is useful to check both the number of files and the column names after a merge.

Calculate total revenue

Now let us ask a simple business question:

total revenue
total revenue

Before calculating the result, Studio notices that the revenue values are split between two columns:

Revenue is split across revenue and Revenue columns due to inconsistent capitalization across source files. This combines both without double-counting and returns the total.

It generates:

revenue_values = df["revenue"].combine_first(df["Revenue"])
total_revenue = revenue_values.sum()

total_revenue
revenue_values = df["revenue"].combine_first(df["Revenue"])
total_revenue = revenue_values.sum()

total_revenue

The result is:

np.float64(4265097.600000001)
np.float64(4265097.600000001)

So the total revenue is 4,265,097.60 USD.

The long decimal tail in 4265097.600000001 is not a problem with the spreadsheet. It comes from the way computers represent floating-point numbers. For a report or dashboard, we would simply round the value to two decimal places.

The more important issue is what would happen if we ignored the extra Revenue column. If we calculated only:

df["revenue"].sum()
df["revenue"].sum()

we would get 3,979,816.60 USD.

That result looks completely reasonable, but it is wrong by 285,281.00 USD. The missing amount is all of July's revenue. In 2025-07.xlsx, the column is called Revenue instead of revenue, so pandas placed July's values in a separate column.

There was no error when the files were merged. That is exactly why checking the structure of the merged data matters.

Fix the revenue column

We know what happened, so now we can fix the dataframe instead of working around the problem every time we calculate revenue.

Ask Studio:

2025-07.xlsx uses Revenue instead of revenue - combine them into one revenue column
2025-07.xlsx uses Revenue instead of revenue - combine them into one revenue column

Studio generates:

df["revenue"] = df["revenue"].combine_first(df["Revenue"])
df = df.drop(columns="Revenue")

print(f"Remaining missing revenue values: {df['revenue'].isna().sum()}")
df.head()
df["revenue"] = df["revenue"].combine_first(df["Revenue"])
df = df.drop(columns="Revenue")

print(f"Remaining missing revenue values: {df['revenue'].isna().sum()}")
df.head()

The result is:

Remaining missing revenue values: 0
Remaining missing revenue values: 0

combine_first() works well here because the two columns contain the same kind of information. It keeps the value from revenue when it exists and uses the value from Revenue when revenue is missing.

In our data, 5,466 rows have their value in revenue, while the 400 rows from July have it in Revenue. No row has values in both columns, so combining them does not overwrite any sales data.

After the columns are combined, the old Revenue column is removed. The final check shows that there are no missing revenue values across the 5,866 rows.

Check revenue by month

We fixed the column, but it is worth checking the result before moving on. Ask:

total revenue per source_file
total revenue per source_file

Studio generates:

revenue_by_source = (
    df.groupby("source_file", as_index=False)["revenue"]
      .sum()
      .sort_values("source_file")
      .rename(columns={"revenue": "total_revenue"})
)

revenue_by_source["total_revenue"] = revenue_by_source["total_revenue"].round(2)
revenue_by_source
revenue_by_source = (
    df.groupby("source_file", as_index=False)["revenue"]
      .sum()
      .sort_values("source_file")
      .rename(columns={"revenue": "total_revenue"})
)

revenue_by_source["total_revenue"] = revenue_by_source["total_revenue"].round(2)
revenue_by_source

The result is:

July now has 285,281.00 USD of revenue instead of disappearing from the calculation. This is a useful confirmation that the fix worked.

Looking at totals per source file is often more useful than looking only at one final number. If one month suddenly has zero revenue or an unexpectedly small value, you know immediately which original workbook to inspect.

The twelve monthly values add up to 4,265,097.60 USD, which matches the total we calculated earlier.

What about discount_pct?

We still have one unusual column, discount_pct. Unlike Revenue, this one should not be merged into another column.

The discount_pct field exists only in 2025-11.xlsx. Someone started recording discounts in November, so rows from the previous eleven months have NaN in this column.

It may be tempting to replace those missing values with 0, but that would change the meaning of the data. A zero would mean that we know an order had no discount. In the older files, we do not know that. The discount simply was not recorded.

For the same reason, deleting the column would not be a good solution either. November contains real information that was not available in the earlier exports. Leaving the older values empty preserves that distinction.

This is a different kind of schema change from Revenue. Revenue was the same field under a different name, so we combined it. discount_pct is genuinely new information, so we keep it.

Save the full-year workbook

Now the revenue column is fixed and we have checked the monthly totals. We can save the merged dataframe as a new Excel file.

Ask Studio:

save merged file to the same directory, name it 2025-full-year.xlsx
save merged file to the same directory, name it 2025-full-year.xlsx

Studio generates:

output_file = folder / "2025-full-year.xlsx"
df.to_excel(output_file, index=False)

print(f"Saved: {output_file}")
output_file = folder / "2025-full-year.xlsx"
df.to_excel(output_file, index=False)

print(f"Saved: {output_file}")

The output confirms where the workbook was saved:

Saved: /home/piotr/Documents/sales/2025-full-year.xlsx
Saved: /home/piotr/Documents/sales/2025-full-year.xlsx

The final workbook contains 5,866 rows and nine columns. It has the original seven sales columns, the source_file column that lets us trace every row back to its monthly workbook, and the new discount_pct column. There is now only one revenue column, and it contains values for all twelve months.

There is one thing to remember before running the notebook again. The output file was saved into the same folder that our code scans for .xlsx files. If you rerun the first cell now, Python will also load 2025-full-year.xlsx as an input file.

Instead of:

Files loaded: 12
Files loaded: 12

you will see:

Files loaded: 13
Files loaded: 13

The merged workbook already contains all 5,866 rows, so loading it together with the twelve monthly files will duplicate the data.

For a notebook that you plan to reuse, it is better to save generated files into a separate output folder or change the file-selection code so it ignores the final workbook. The file count gives you a simple warning when this happens.

Reuse the notebook next year

The useful result of this tutorial is not only 2025-full-year.xlsx. You also have a Jupyter notebook that records how the data was merged, checked, fixed, and saved.

Next year, you can change the input folder and run the same notebook again. The loop will load the new monthly files, while the checks will help you see whether anything changed in the exports.

I would keep those checks in the notebook even after they pass. The file count tells you whether all expected files were loaded. The column list can reveal changes in the spreadsheet structure. The per-file revenue totals make it easier to spot a month whose values ended up in the wrong place.

That is especially useful with recurring spreadsheet work. A process that worked perfectly this year may receive slightly different files next year. Keeping the checks next to the merge makes those changes easier to notice before you start using the numbers.

A note on privacy

MLJAR Studio works with files directly on your computer. The Python code generated in this tutorial runs locally and reads the twelve workbooks from ~/Documents/sales/, so you do not need to upload the Excel files somewhere before Python can work with them.

There is a difference between where Python runs and where the AI model runs. If you use a cloud AI provider, the model needs some context to help with your data. Depending on the task, this can include your prompt, column names, dataframe shape, and some preview values. The complete workbook is not simply uploaded as part of reading the Excel file, but you should still consider what data context may be sent when working with confidential information.

If you want the AI part to stay local as well, MLJAR Studio can connect to local LLMs through Ollama, vLLM, LM Studio, Jan, and llama.cpp. In this setup, you can run the AI model on your own computer or infrastructure, so your prompts, data context, generated code, and results do not need to be sent to a cloud AI provider.

Summary

We started with twelve monthly Excel files and merged them into one dataframe. Adding source_file allowed us to check that every workbook was included and made it possible to analyze the results month by month.

The merge itself worked without errors, but our checks revealed something more important: one workbook used Revenue instead of revenue. Pandas treated those as two different columns, which meant a simple revenue calculation could silently leave out all of July. We combined the two columns, checked the monthly totals, and kept discount_pct because it represented genuinely new information rather than a naming mistake.

Finally, we saved a clean 2025-full-year.xlsx workbook and kept the whole process in a notebook. The next time another folder of monthly exports arrives, we do not need to rebuild the process from scratch.

Next steps

• Merge every Excel file in a folder for the concise workflow overview
• Merge Excel files in Python for a slower walkthrough using three matching files
• Explore spreadsheet workflows for more ways to analyze, transform, and automate Excel data