如何用VBA/Python/R批量提取CSV数据并转存为Excel文件

需求梳理 #

• 批量处理数百个CSV文件
• 提取文件名首个下划线前的ID
• 定位CSV中A列包含“Date and Time”的行，从此行开始提取A、E列数据并去除多余空格
• 将ID作为输出Excel的A列，原CSV的A列作为B列，原CSV的E列作为C列，保存为Excel文件

实现脚本（基于pandas，上手简单） #

先确保安装依赖库：

pip install pandas openpyxl

修改脚本中的路径配置后直接运行：

import os
import pandas as pd

# 配置路径，替换成你的实际路径
csv_folder = "存放CSV文件的文件夹路径"
output_excel = "最终输出的Excel文件路径.xlsx"

# 存储所有处理后的数据
all_processed_data = []

# 遍历文件夹下的所有文件
for filename in os.listdir(csv_folder):
    if not filename.lower().endswith(".csv"):
        continue
    
    # 提取文件名里的ID：首个下划线前的内容
    file_id = filename.split("_")[0]
    
    # 读取CSV，自动跳过列开头的空格
    raw_df = pd.read_csv(os.path.join(csv_folder, filename), skipinitialspace=True)
    
    # 查找"Date and Time"所在行的索引
    start_row_idx = None
    for idx, cell_value in enumerate(raw_df.iloc[:, 0]):
        if isinstance(cell_value, str) and "Date and Time" in cell_value:
            start_row_idx = idx
            break
    
    if start_row_idx is None:
        print(f"⚠️ 文件{filename}未找到'Date and Time'行，已跳过")
        continue
    
    # 提取目标列：A列（索引0）和E列（索引4）
    target_df = raw_df.iloc[start_row_idx:, [0, 4]].copy()
    
    # 清除所有字符串单元格的前后空格
    target_df = target_df.apply(lambda col: col.str.strip() if col.dtype == "object" else col)
    
    # 插入ID列作为第一列
    target_df.insert(0, "ID", file_id)
    
    # 重命名列对应输出要求
    target_df.columns = ["ID", "原CSV_A列(Date and Time)", "原CSV_E列"]
    
    # 把当前文件的数据加入总列表
    all_processed_data.append(target_df)

# 合并所有数据并写入Excel
if all_processed_data:
    final_result = pd.concat(all_processed_data, ignore_index=True)
    final_result.to_excel(output_excel, index=False, engine="openpyxl")
    print(f"✅ 处理完成，结果已保存到{output_excel}")
else:
    print("❌ 没有找到符合条件的CSV文件，或所有文件均未找到目标行")

脚本说明 #

• 自动跳过非CSV文件，避免无效处理
• 加入容错判断，单个文件找不到目标行时仅打印警告，不中断整个批量流程
• 双重处理空格：读取CSV时自动跳过列开头空格，后续再清除单元格前后冗余空格
• 所有CSV的数据会合并到同一个Excel工作表，ID列对应每个源文件的标识

内容的提问来源于stack exchange，提问作者Bonneroo