Automate Bulk Excel Column Extraction and Merging with Python Pandas

1. Introduction

The task is to extract specific columns from many Excel files spread across multiple directories and combine them into a new workbook, which becomes cumbersome when dealing with hundreds of folders.

2. Import Libraries

Only the os module for file handling and pandas for data processing are required.

import pandas as pd
import os

3. Code Implementation

Define source folder and columns

# Path to the folder containing Excel files
path = "D:/a/"
# Columns to extract
key = ['A', 'B']
folders = os.listdir(path)
output_file = path + "result.xlsx"
writer = pd.ExcelWriter(output_file, engine='openpyxl')

Collect Excel file paths

file_names = []
for p in folders:
    if '.xl' in p:
        continue
    folder_path = path + p + "/"
    xlsx_names = [x for x in os.listdir(folder_path) if x.endswith('.xlsx')]
    for f in xlsx_names:
        file_names.append(folder_path + f)

Read, extract, and concatenate

df = None
for xlsx_name in file_names:
    df1 = pd.read_excel(xlsx_name, sheet_name=0, index_col=None, header=0)
    _df = df1.loc[:, key]
    if df is None:
        df = _df
    else:
        df = pd.concat([df, _df], ignore_index=True)
    print(xlsx_name + "  保存成功！共%d个，第%d个。" % (len(file_names), num))

4. Execution Result

Folder structure of files to be processed:

Code runs successfully:

Resulting Excel file saved:

5. Conclusion

The example demonstrates how pandas can efficiently batch‑extract and merge columns from numerous Excel workbooks, highlighting Python’s powerful data‑manipulation capabilities.