merge-excel-files-with-ai-and-python
load xlsx files from ~/Documents/sales_q1, merge then and add source_file column
Thinking
Clarifying file usage
I need to clarify the file path given for the folder. My first step is to load and inspect only, but if the user asks for a merge, then I need to handle that differently. I'll make sure to have a robust code for xlsx glob and each source. It seems I should focus on ensuring that there's no comma in the expression, and that I stick to the exact column display mandate without asking unnecessary questions.
Load all Excel files in the folder, combine them, and preserve each file’s name in source_file.
</>Load excel files
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()Loaded 3 files Shape: (1457, 8) Columns (8): ['order_id', 'order_date', 'region', 'product', 'units', 'unit_price', 'revenue', 'source_file']
| order_id | order_date | region | product | units | unit_price | revenue | source_file | |
|---|---|---|---|---|---|---|---|---|
| 0 | ORD-2025-01-0001 | 2025-01-01 | North | Wireless Mouse | 7 | 29.9 | 209.3 | 2025-01.xlsx |
| 1 | ORD-2025-01-0002 | 2025-01-01 | East | Standing Desk | 6 | 449.0 | 2694.0 | 2025-01.xlsx |
| 2 | ORD-2025-01-0003 | 2025-01-01 | North | Desk Lamp | 8 | 24.5 | 196.0 | 2025-01.xlsx |
| 3 | ORD-2025-01-0004 | 2025-01-01 | South | Laptop Stand | 19 | 45.0 | 855.0 | 2025-01.xlsx |
| 4 | ORD-2025-01-0005 | 2025-01-01 | North | Wireless Mouse | 8 | 29.9 | 239.2 | 2025-01.xlsx |
The combined dataset contains 1,457 rows from 3 Excel files. It retains the expected sales fields and includes source_file, which identifies the originating workbook for every order (for example, 2025-01.xlsx).
count rows per source_file
</>Count source file rows
df["source_file"].value_counts().rename_axis("source_file").reset_index(name="row_count")| source_file | row_count | |
|---|---|---|
| 0 | 2025-03.xlsx | 520 |
| 1 | 2025-01.xlsx | 503 |
| 2 | 2025-02.xlsx | 434 |
2025-03.xlsx: 520 rows2025-01.xlsx: 503 rows2025-02.xlsx: 434 rows
March has the most records; February has the fewest.
save merged file to the same directory, name it 2025-q1.xlsx
</>Save merged file
output_file = folder / "2025-q1.xlsx"
df.to_excel(output_file, index=False)
print(f"Saved merged file: {output_file}")Saved merged file: /home/piotr/Documents/sales_q1/2025-q1.xlsx
The merged workbook was saved in the same directory as the source files at:
/home/piotr/Documents/sales_q1/2025-q1.xlsx
It contains 1,457 combined rows from the three Q1 source workbooks.