The Problem (Q-score 6, ranked #196th of 303 in the Excel VBA archive)
The scenario as originally posted in 2014
I am trying to combine 2 different Excel files. (thanks to the post Import multiple excel files into python pandas and concatenate them into one dataframe)
The one I work out so far is:
import os
import pandas as pd
df = pd.DataFrame()
for f in ['c:\file1.xls', 'c:\ file2.xls']:
data = pd.read_excel(f, 'Sheet1')
df = df.append(data)
df.to_excel("c:\all.xls")
Here is how they look like.
However I want to:
- Exclude the last rows of each file (i.e. row4 and row5 in File1.xls; row7 and row8 in File2.xls).
- Add a column (or overwrite Column A) to indicate where the data from.
For example:
Is it possible? Thanks.
Why community consensus is tight on this one
Across 303 Excel VBA entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — niche answer (below median) (+8)
Advisory answer — community consensus with reference links
Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.
For num. 1, you can specify skip_footer as explained here; or, alternatively, do
data = data.iloc[:-2]
once your read the data.
For num. 2, you may do:
from os.path import basename
data.index = [basename(f)] * len(data)
Also, perhaps would be better to put all the data-frames in a list and then concat them at the end; something like:
df = []
for f in ['c:\file1.xls', 'c:\ file2.xls']:
data = pd.read_excel(f, 'Sheet1').iloc[:-2]
data.index = [os.path.basename(f)] * len(data)
df.append(data)
df = pd.concat(df)
When to Use It — classic (2013–2016)
Ranked #196th in its category — specialized fit
This pattern sits in the 98% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Excel VBA archive for a higher-consensus alternative.
What changed between 2014 and 2026
The answer is 12 years old. The Excel VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.