You get one sales export every month, and at the end of the quarter you need to combine three Excel files into one. You can do this manually in Excel. Open the first workbook, copy the rows from the second one, paste them at the bottom, and then repeat the same thing with the third file. If everything goes well, it only takes a few minutes.
The problem often appears later. Three weeks after the merge, you notice a number that looks strange and want to check where it came from. Was this row in the January file, the February file, or the March file? When you copy and paste all the rows into one spreadsheet, that information is easy to lose.
In this tutorial, we will merge three Excel files with Python in MLJAR Studio. We will also add a source_file column, so every row keeps the name of the workbook it came from. At the end, we will check that all rows were loaded correctly and save the result as a new Excel file. The Python code stays in a notebook, so you can reuse the same process next quarter instead of starting again.
Before we start
If you have not used MLJAR Studio before, there are a few things worth knowing because they explain how we will work with the Excel files in this tutorial.
MLJAR Studio is a desktop application built on JupyterLab. You work with files directly on your computer, so there is no need to upload the spreadsheets before Python can read them. In this tutorial, for example, we can point Python directly to ~/Documents/sales_q1/.
We will use the AI assistant to write Python code for us, but the generated Python runs on your computer against your local files. The result is also a normal Jupyter notebook with the .ipynb extension. You can inspect the code, change it, rerun it later, or open the notebook in another application that supports Jupyter notebooks.
The AI helps us write the code, but the code itself is visible. That is useful in a task like this because we can see exactly how the files are loaded, merged, checked, and saved.
Get the data
For this tutorial, download the demo files from datasets-for-start and put them in:
~/Documents/sales_q1/
The folder contains three workbooks:
2025-01.xlsx 2025-02.xlsx 2025-03.xlsx
There is one file for each month of the first quarter of 2025. All three spreadsheets have the same seven columns: order ID, order date, region, product, units, unit price, and revenue.
If you want to follow the tutorial with your own files instead, that is fine too. Put the Excel files you want to combine into one folder and use that folder path in the prompt.
Load and merge the Excel files
From the welcome view, select Start a New AI Conversation. This will open a new conversational notebook.
In the prompt box at the bottom, type:
load xlsx files from ~/Documents/sales_q1, merge then and add source_file column
You should see the view like in the screenshot below:
There is a typo in this prompt. It says merge then instead of merge them. I left it here on purpose because Studio understood what I wanted anyway. You do not need to write perfect instructions. Describe the task the way you would describe it to a colleague.
Press Send. Studio writes the Python code, runs it, and shows the merged data.
The response tells us:
Loaded 3 files Shape: (1457, 8) Columns (8): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue', 'source_file']
We loaded three Excel files and ended up with 1,457 rows and eight columns. Seven columns came from the original spreadsheets. The eighth, source_file, was added during the merge.
This new column contains the name of the workbook each row came from. January rows contain 2025-01.xlsx, February rows contain 2025-02.xlsx, and March rows contain 2025-03.xlsx.
That small addition makes the merged spreadsheet much easier to check later. Once all the rows are combined, they otherwise look exactly the same, and there is no easy way to know which original file contained a particular row.
What Python did
You do not need to write the Python yourself to complete this tutorial, but it is worth looking at the generated code. It shows exactly what happened to our files.
The Python code returned by AI (it is collapsed by default):
from pathlib import Path import pandas as pd folder = Path("~/Documents/sales_q1").expanduser() files = sorted(folder.glob("*.xlsx")) if not files: raise FileNotFoundError(f"No .xlsx files found in {folder}") 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) print(f"Loaded {len(files)} 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 first part finds all .xlsx files in the folder. expanduser() converts the ~ in our path into the actual home directory on the computer. Then glob("*.xlsx") finds the Excel files, and sorted() processes them in filename order. With names such as 2025-01.xlsx, 2025-02.xlsx, and 2025-03.xlsx, that is also calendar order.
The if not files check is there to catch a common mistake. If the folder path is wrong or there are no Excel files inside it, we get a clear error instead of continuing with an empty result.
The next part opens each workbook with pd.read_excel(). Before the data is added to the list of dataframes, Python creates the source_file column and fills it with the current filename:
data["source_file"] = file.name
It is important to do this before combining the files. At this point Python still knows exactly which workbook each dataframe came from. After everything has been stacked together, that information would otherwise be lost.
Finally, pd.concat() combines the dataframes into one:
df = pd.concat(frames, ignore_index=True, sort=False)
ignore_index=True gives the combined dataframe one new row index from beginning to end, instead of keeping the separate indexes from the three source files.
For this tutorial, you do not need to remember all of these pandas options. The important idea is simple: find the Excel files, read them one by one, remember each filename, and then combine the rows.
Check that all files were merged
We have a dataframe with 1,457 rows, but it is a good idea to check where those rows came from before saving anything.
Ask Studio:
count rows per source_file
Studio generates a small pandas expression:
df["source_file"].value_counts().rename_axis("source_file").reset_index(name="row_count")
The result is:
| source_file | row_count |
|---|---|
| 2025-01.xlsx | 503 |
| 2025-02.xlsx | 434 |
| 2025-03.xlsx | 520 |
We have 503 rows from January, 434 from February, and 520 from March. Together that gives:
503 + 434 + 520 = 1,457
This matches the total number of rows reported after the merge.
Checking the number of rows per file is more useful than checking only the final total. If one workbook was only partly loaded, a total row count would not tell us which file caused the problem. With source_file, we can compare each number with the corresponding original workbook.
You might notice that value_counts() does not always display the files in January, February, March order. By default, it sorts by the number of occurrences. That is fine for this check. The row counts themselves are what matter.
Save the merged Excel file
Now that we know all three source files are present, we can save the merged dataframe as a new Excel workbook.
Ask:
save merged file to the same directory, name it 2025-q1.xlsx
You will see the view like in the screenshot below:
Studio generates Python code and run it locally:
output_file = folder / "2025-q1.xlsx" df.to_excel(output_file, index=False) print(f"Saved merged file: {output_file}")
The output confirms where the file was written:
Saved merged file: /home/piotr/Documents/sales_q1/2025-q1.xlsx
You should now see the new workbook in the same folder as the three original files.
The index=False option is worth keeping. A pandas dataframe has its own row numbers, starting at 0. We usually do not want those numbers to become an extra column in the Excel file. Without index=False, Excel would get an additional first column containing values from 0 to 1456.
At this point, the practical part of the task is finished. We have one Excel workbook containing all three months, and every row still tells us which original file it came from.
Reuse the notebook next quarter
One advantage of doing the merge this way is that the work does not disappear when you close the application. The AI conversation is stored as a Jupyter notebook. Your prompts are saved as markdown cells, the generated Python is saved as code cells, and the results are stored with them.
Next quarter, you could put 2025-04.xlsx, 2025-05.xlsx, and 2025-06.xlsx into a folder, change the folder path and output filename in the notebook, and run the cells again.
There is one small trap to remember. In this tutorial, we saved 2025-q1.xlsx into the same folder from which the code loads every .xlsx file. If you run the first cell again without changing anything, Python will now find four Excel files instead of three. The newly created 2025-q1.xlsx will be treated as another input file, so the already merged rows will be loaded again.
That is why the line:
Loaded 3 files
is useful. If you expect three input files and suddenly see Loaded 4 files, stop and check the folder before continuing.
For a notebook that you plan to run regularly, an even better solution is to keep generated files in a separate output folder. For example, you could read source spreadsheets from sales_q1/ and save the final workbook into sales_q1/output/. Another option is to change the Python code so that it ignores the output filename.
The important part is that you now have a repeatable process. You do not have to remember which rows to copy, where to paste them, or how you checked the result last time. The notebook records the steps for you.
A note on privacy
Because MLJAR Studio works with local files, your Excel workbook does not need to be uploaded somewhere before Python can read it. The generated code runs on your computer and opens the spreadsheet directly from your disk.
There is an important difference between where the Python code runs and where the AI model runs. If you use a cloud AI model, the model needs some context to help you with the data. Depending on the task, that can include your prompt, column names, the shape of the dataframe, and preview values such as those shown by df.head(). The complete Excel workbook is not simply uploaded as part of the file-reading step, but you should still be careful when working with confidential information because some data context may be sent to the AI provider.
If you need 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 three separate monthly Excel files and combined them into one workbook. Instead of simply stacking the rows, we added a source_file column so that we can always trace a row back to its original spreadsheet.
We also checked the number of rows contributed by each file before saving the result. That simple verification gives us much more confidence that the merge worked correctly. Finally, we saved everything as 2025-q1.xlsx, while keeping the Python steps in a Jupyter notebook that can be reused for the next batch of files.
For three spreadsheets, copying and pasting by hand may still feel quicker. The real benefit appears when the task comes back next week, next month, or next quarter. Instead of repeating the manual work, you already have the process written down and ready to run again.
Next steps
- Merge Excel files with AI and Python for the concise workflow overview
- Merge all Excel files in a folder when you have a full year of exports and the columns may have changed
- Explore spreadsheet workflows for more ways to analyze, transform, and automate Excel data