VBA 宏批量合并 CSV 到 Excel 完整指南：编码处理、列名对齐、数组中转与 Power Query / pandas 替代方案

很多业务系统（电商订单、CRM、银行流水、广告投放报表）导出数据时只给 CSV 格式，运营每天要把几十甚至上百个 CSV 合并成一份 Excel 做汇总分析。手工逐个打开复制粘贴几小时跑不完。VBA 宏是 Excel 内置最直接的批量自动化方案：写一段几十行的代码，点一下就处理完所有 CSV。本文给出可以在 Excel 2010-2024 各版本通用的宏代码，并扩展到 Power Query 现代方案、Python pandas 方案的对比、字符编码处理（UTF-8 BOM、GBK 中文）、大文件性能优化、表头去重、合并后数据校验等实战环节。

需求场景与方案选型

典型场景

电商日报合并：每天从淘宝、京东、拼多多导出 CSV，几十张表合成一张周报。
多平台广告数据：百度、Google、Meta、TikTok 各自导出 CSV，每个文件结构相似但字段顺序略有不同。
多区域销售汇总：连锁店每家店一份 CSV，总部需要按行合并做地域分析。
历史数据归档：每月一个 CSV，年底要合成全年文件。

四种主流方案对比

方案	学习成本	性能	适用场景
VBA 宏	低	中	一次性、文件 < 100 个
Power Query	中	高	需要数据清洗、定期任务
Python pandas	高	极高	百万行级、需要二次处理
命令行 cat/awk	中	高	纯文本处理、Linux 环境

本文以 VBA 宏为主线，最后给出 Power Query 与 pandas 的等价实现作为参考。

VBA 宏方案：完整代码与逐段解析

准备工作

把所有需要合并的 CSV 文件放到同一个文件夹，比如 D:\merge\。
在该文件夹里新建一个空的 Excel 文件，重命名为 merge.xlsm（注意是 .xlsm 不是 .xlsx，宏功能必须 xlsm/xlsb 格式）。
打开 merge.xlsm，按 Alt+F11 打开 VBA 编辑器。
插入 - 模块，把下面的代码粘贴到模块代码窗口。

完整宏代码

Option Explicit

Sub MergeCSVFiles()
    Dim folderPath As String
    Dim csvFile As String
    Dim wsTarget As Worksheet
    Dim wbSource As Workbook
    Dim lastRow As Long
    Dim startRow As Long
    Dim fileCount As Integer
    Dim mergedFiles As String
    Dim includeHeader As Boolean
    Dim startTime As Double

    startTime = Timer
    fileCount = 0
    includeHeader = True
    mergedFiles = ""

    folderPath = ThisWorkbook.Path & "\"
    Set wsTarget = ThisWorkbook.Worksheets(1)
    wsTarget.Cells.Clear

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    csvFile = Dir(folderPath & "*.csv")

    Do While csvFile <> ""
        On Error Resume Next
        Set wbSource = Workbooks.Open( _
            Filename:=folderPath & csvFile, _
            ReadOnly:=True, _
            Local:=True)
        On Error GoTo 0

        If Not wbSource Is Nothing Then
            With wbSource.Worksheets(1)
                lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                If lastRow > 0 Then
                    If includeHeader Then
                        startRow = 1
                        includeHeader = False
                    Else
                        startRow = 2
                    End If

                    Dim destRow As Long
                    destRow = wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row + 1
                    If destRow = 2 And IsEmpty(wsTarget.Cells(1, 1)) Then destRow = 1

                    .Range(.Cells(startRow, 1), .Cells(lastRow, .UsedRange.Columns.Count)).Copy _
                        wsTarget.Cells(destRow, 1)
                End If
            End With

            wbSource.Close SaveChanges:=False
            Set wbSource = Nothing

            fileCount = fileCount + 1
            mergedFiles = mergedFiles & vbCrLf & csvFile
        End If

        csvFile = Dir
    Loop

    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

    MsgBox "合并完成！" & vbCrLf & _
           "处理文件数：" & fileCount & vbCrLf & _
           "总行数：" & wsTarget.Cells(wsTarget.Rows.Count, 1).End(xlUp).Row & vbCrLf & _
           "耗时：" & Format(Timer - startTime, "0.00") & " 秒" & vbCrLf & _
           "合并的文件：" & mergedFiles, vbInformation, "CSV 合并器"
End Sub

逐段解析

性能优化三件套：Application.ScreenUpdating = False、DisplayAlerts = False、Calculation = xlCalculationManual 在循环开始前设为关闭，循环结束后恢复。这三项是 VBA 宏的标配性能优化，能让运行速度提升 5-10 倍。

表头处理：includeHeader 标志位让第一个文件保留表头，后续文件从第 2 行开始（跳过重复的表头）。这是「按行追加」合并的核心。

destRow 计算：每次找当前已写入数据的最后一行 +1 作为下一文件的写入起点。考虑到第一次时表是空的，加了 IsEmpty 判断避免错位。

Local:=True 参数：Workbooks.Open 时传 Local:=True 让 Excel 用本地区域设置解析 CSV（特别是分隔符与日期格式）。中文 Windows 默认分隔符是逗号，欧洲版可能是分号。

错误处理：On Error Resume Next 让某个 CSV 文件打不开（比如被锁定）时跳过继续，而不是整个宏崩溃。

运行宏

关闭 VBA 编辑器，回到 Excel 工作簿。按 Alt+F8 打开宏列表，选择 MergeCSVFiles，点「执行」。运行结束会弹窗显示文件数、总行数、耗时、合并清单。

常见的特殊场景处理

CSV 编码问题（UTF-8 vs GBK）

Excel 的 Workbooks.Open 默认按系统区域设置识别编码：中文 Windows 默认 GBK。如果 CSV 是 UTF-8 编码（特别是从 SaaS 平台导出的），打开会变乱码。

解决方案 A：UTF-8 with BOM。让导出方在文件头加 BOM（0xEF 0xBB 0xBF），Excel 自动识别为 UTF-8。但许多平台导出不带 BOM。

解决方案 B：用 ADO 显式按 UTF-8 读：

Dim adoStream As Object
Set adoStream = CreateObject("ADODB.Stream")
adoStream.Charset = "UTF-8"
adoStream.Type = 2 ' Text
adoStream.Open
adoStream.LoadFromFile folderPath & csvFile
Dim rawText As String
rawText = adoStream.ReadText
adoStream.Close

' 解析 rawText 按行拆分逗号
Dim lines() As String
lines = Split(rawText, vbCrLf)
Dim i As Long, j As Long
For i = 0 To UBound(lines)
    Dim cells() As String
    cells = SplitCSVLine(lines(i)) ' 自定义函数处理引号包裹的字段
    For j = 0 To UBound(cells)
        wsTarget.Cells(destRow + i, j + 1).Value = cells(j)
    Next j
Next i

SplitCSVLine 自定义函数要处理引号包裹（"a, b, c" 算一个字段而不是三个）：

Function SplitCSVLine(line As String) As String()
    Dim result() As String
    Dim n As Long: n = 0
    ReDim result(0 To 100)
    Dim i As Long, ch As String, current As String, inQuotes As Boolean
    inQuotes = False
    current = ""
    For i = 1 To Len(line)
        ch = Mid(line, i, 1)
        If ch = """" Then
            inQuotes = Not inQuotes
        ElseIf ch = "," And Not inQuotes Then
            result(n) = current
            n = n + 1
            current = ""
            If n > UBound(result) Then ReDim Preserve result(0 To n + 100)
        Else
            current = current & ch
        End If
    Next i
    result(n) = current
    ReDim Preserve result(0 To n)
    SplitCSVLine = result
End Function

列顺序不一致的合并

不同来源的 CSV 文件列顺序可能不同（比如 A 文件是「日期, 销量, 金额」，B 文件是「销量, 日期, 金额」）。直接复制粘贴会让数据错列。处理方法：按列名匹配。

Sub MergeCSVByHeader()
    ' 第一遍：扫描所有文件的表头，建立全集字段
    Dim allHeaders As Object
    Set allHeaders = CreateObject("Scripting.Dictionary")

    Dim csvFile As String, folderPath As String
    folderPath = ThisWorkbook.Path & "\"
    csvFile = Dir(folderPath & "*.csv")

    Do While csvFile <> ""
        Dim wbSrc As Workbook
        Set wbSrc = Workbooks.Open(folderPath & csvFile, ReadOnly:=True)
        Dim hdr As Range
        Set hdr = wbSrc.Worksheets(1).Rows(1)
        Dim col As Long
        For col = 1 To wbSrc.Worksheets(1).UsedRange.Columns.Count
            Dim h As String: h = Trim(CStr(hdr.Cells(1, col).Value))
            If h <> "" And Not allHeaders.Exists(h) Then
                allHeaders.Add h, allHeaders.Count + 1
            End If
        Next col
        wbSrc.Close False
        csvFile = Dir
    Loop

    ' 写表头到目标
    Dim wsTarget As Worksheet
    Set wsTarget = ThisWorkbook.Worksheets(1)
    wsTarget.Cells.Clear
    Dim k As Variant
    For Each k In allHeaders.Keys
        wsTarget.Cells(1, allHeaders(k)).Value = k
    Next k

    ' 第二遍：按字段名映射写入
    csvFile = Dir(folderPath & "*.csv")
    Dim destRow As Long: destRow = 2
    Do While csvFile <> ""
        Set wbSrc = Workbooks.Open(folderPath & csvFile, ReadOnly:=True)
        Dim ws As Worksheet: Set ws = wbSrc.Worksheets(1)
        Dim lastRow As Long, lastCol As Long
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        lastCol = ws.UsedRange.Columns.Count

        ' 建立源字段到列号映射
        Dim srcMap As Object: Set srcMap = CreateObject("Scripting.Dictionary")
        For col = 1 To lastCol
            Dim srcH As String: srcH = Trim(CStr(ws.Cells(1, col).Value))
            If srcH <> "" Then srcMap.Add srcH, col
        Next col

        ' 数据行写入
        Dim r As Long
        For r = 2 To lastRow
            For Each k In allHeaders.Keys
                If srcMap.Exists(k) Then
                    wsTarget.Cells(destRow, allHeaders(k)).Value = ws.Cells(r, srcMap(k)).Value
                End If
            Next k
            destRow = destRow + 1
        Next r

        wbSrc.Close False
        csvFile = Dir
    Loop
End Sub

这种「按字段名对齐」的合并对多源数据极其重要，纯按列号合并会让字段错位。

大文件（每个 CSV 上百万行）

VBA 单元格逐个赋值的性能瓶颈是 COM 调用。10 万行的 CSV 用单元格 .Value 赋值要 30 秒，用 Range.Value = 数组一次性赋值只要 0.5 秒。优化：

' 把整张表读到二维数组
Dim dataArr As Variant
dataArr = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol)).Value

' 一次性写到目标
Dim destRange As Range
Set destRange = wsTarget.Cells(destRow, 1).Resize(UBound(dataArr, 1), UBound(dataArr, 2))
destRange.Value = dataArr

destRow = destRow + UBound(dataArr, 1)

这种「数组中转」的写法对大文件性能提升 50-100 倍。

合并到不同工作表（每个 CSV 一个 sheet）

有时不希望按行追加成一张大表，而是希望每个 CSV 独立成一个 worksheet 便于查看：

Sub CSVToSheets()
    Dim folderPath As String, csvFile As String
    folderPath = ThisWorkbook.Path & "\"
    csvFile = Dir(folderPath & "*.csv")

    Do While csvFile <> ""
        Dim wbSrc As Workbook
        Set wbSrc = Workbooks.Open(folderPath & csvFile, ReadOnly:=True)
        Dim sheetName As String
        sheetName = Replace(csvFile, ".csv", "")
        If Len(sheetName) > 31 Then sheetName = Left(sheetName, 31)

        wbSrc.Worksheets(1).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = sheetName

        wbSrc.Close False
        csvFile = Dir
    Loop
End Sub

注意 Excel sheet 名长度限制 31 字符，超过会报错。

Power Query 现代方案

为什么 Power Query 是更优解

VBA 宏一次性写好后跑一次。Power Query 是「数据连接 + 转换步骤」记录，文件每次刷新会自动重新加载源。这种「数据管道」思想对定期任务（每天合并昨天的 CSV）极其有用。

操作步骤

Excel 2016+ 已内置 Power Query。打开新 Excel 文件，菜单「数据 - 获取数据 - 自文件 - 从文件夹」。
选择存放 CSV 的文件夹（D:\merge\）。
弹出对话框显示文件列表，点「合并 - 合并并加载」。
选择「示例文件」（用第一个 CSV 当模板），确认表头识别正确。
Power Query 自动生成合并步骤，点「关闭并加载」。

下次有新 CSV 加入文件夹，只需在 Excel 里「数据 - 全部刷新」，Power Query 自动重新合并。

Power Query 的 M 语言代码

右键 Power Query 编辑器里的查询，「高级编辑器」能看到 M 语言代码：

let
    Source = Folder.Files("D:\merge"),
    FilteredCSV = Table.SelectRows(Source, each [Extension] = ".csv"),
    LoadAll = Table.AddColumn(FilteredCSV, "Content", each Csv.Document([Content], [Encoding=65001, Delimiter=",", Columns=null, QuoteStyle=QuoteStyle.Csv])),
    PromoteHeaders = Table.AddColumn(LoadAll, "Data", each Table.PromoteHeaders([Content])),
    ExpandData = Table.ExpandTableColumn(PromoteHeaders, "Data", List.Distinct(List.Combine(List.Transform(PromoteHeaders[Data], each Table.ColumnNames(_)))))
in
    ExpandData

M 语言中 Encoding=65001 是 UTF-8。指定编码就解决了 VBA 里 ADO 处理的复杂度。

Python pandas 方案

处理百万行级、跨数百个文件、需要二次清洗的场景，Python 是更专业的工具：

import pandas as pd
import glob

# 读所有 CSV 到 DataFrame 列表
files = glob.glob('D:/merge/*.csv')
dfs = []
for f in files:
    df = pd.read_csv(f, encoding='utf-8')
    df['_source_file'] = f.split('\\')[-1]  # 加来源文件标记
    dfs.append(df)

# 按列名对齐合并
merged = pd.concat(dfs, ignore_index=True, sort=False)

# 写入 Excel
merged.to_excel('D:/merge/result.xlsx', index=False, engine='openpyxl')

print(f"合并 {len(files)} 个文件，共 {len(merged)} 行")

pandas 的 concat 自动按列名对齐，比 VBA 简单得多。处理 1 GB 级数据时性能比 VBA 高 100 倍。

合并后的数据校验

必做的三项校验

行数核对：合并前每个 CSV 的行数总和（减去重复表头）应等于合并后总行数。
来源标记：合并时给每行加一列「来源文件」，事后能追溯。
关键字段非空：检查关键字段（订单号、商品 ID）是否有空值，定位编码或解析问题。

VBA 加来源列

' 在主合并循环里，复制完每个文件后填充来源列
Dim sourceCol As Long
sourceCol = wsTarget.UsedRange.Columns.Count + 1
wsTarget.Cells(destRow, sourceCol).Resize(lastRow - startRow + 1, 1).Value = csvFile

常见故障

故障 1：宏运行后 Excel 卡死

多半是 ScreenUpdating 没关或者 Calculation 是 xlCalculationAutomatic。每写一个单元格 Excel 都重画屏幕 + 重算公式。把这两个开关关闭通常就解决。

故障 2：CSV 中文乱码

CSV 编码与 Excel 区域设置不匹配。三种处理：让导出方加 UTF-8 BOM；用 ADO Stream 显式按 UTF-8 读；在 Workbooks.Open 时用 OpenText 方法指定 Origin 参数（OpenText 接受 65001 表示 UTF-8）。

故障 3：日期变成数字或文本

Excel 解析 CSV 时按区域设置识别日期格式。如果 CSV 里是 2024-01-15 但 Excel 区域是 m/d/yyyy，可能识别失败变成纯文本。解决：合并后用 TEXT(A2, "yyyy-mm-dd") 公式或者宏里显式 CDate 转换。

故障 4：科学记数法把订单号变形

订单号 12345678901234 这种 14 位数字会被 Excel 自动识别为数值，超过 15 位精度会丢失末尾。强制按文本读：在 OpenText 时给该列指定 xlTextFormat：

Workbooks.OpenText Filename:=path, _
    DataType:=xlDelimited, _
    Comma:=True, _
    FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlGeneralFormat))

FieldInfo 里 Array(列号, 格式) 指定每列。1 是文本，2 是常规。

故障 5：合并后总行数比预期少

多半是 lastRow 计算错了。.Cells(.Rows.Count, 1).End(xlUp).Row 只看 A 列。如果 A 列有空行而其它列有数据，会少算。改用 UsedRange.Rows.Count 或者按所有列扫描最大行数。

故障 6：宏运行慢且文件多了之后越来越慢

每次 Workbooks.Open / Close 都有较大开销。改用 ADO Stream 直接读文件文本，性能更优。或者直接上 Power Query / Python pandas。

故障 7：宏被禁用无法运行

Excel 安全设置默认禁用 VBA 宏。开启：文件 - 选项 - 信任中心 - 信任中心设置 - 宏设置，选「禁用所有宏，并发出通知」（推荐）或「启用所有宏」（不推荐，有安全风险）。

常见问题解答

VBA 宏与 Power Query 怎么选？

一次性合并选 VBA 宏（写完跑完就丢）；定期任务（每天/每周合并）选 Power Query（一次配置永久刷新）；处理超大数据量选 Python。三者并不互斥，可以根据场景组合。

合并后想给每行加来源标记？

VBA 在主合并循环里给每行写入文件名作为额外列。Power Query 默认就会保留 Source.Name 列。pandas 用 df['_source_file'] = filename 添加。

CSV 文件超过 100 万行 Excel 装不下怎么办？

Excel 单 sheet 限制 1048576 行。超过用 Python 处理或者 Power Query 加载到数据模型（不显示在 sheet 上但可以做透视分析）。或者拆成多个 sheet 每个 100 万行以内。

能否合并 xls 与 xlsx 文件而不是 CSV？

能。把 Dir 模式从 *.csv 改成 *.xlsx，其它逻辑相同。要同时处理 xls 与 xlsx 写两次循环，或者用 Dir 加通配符 "*.xls*"。

合并时如何过滤特定文件名模式？

VBA 用 Like 操作符：If csvFile Like "sales_2024_*" Then ...。Power Query 在 Folder.Files 之后用 Table.SelectRows 加条件过滤。

合并时如何按日期顺序排列？

VBA 不保证 Dir 返回的文件顺序。先把所有文件名读到数组，再按文件名排序，最后按排序后的顺序合并。或者合并完成后按某列排序：wsTarget.UsedRange.Sort Key1:=wsTarget.Range("B2"), Order1:=xlAscending, Header:=xlYes。

合并完成后能否自动保存？

能。在宏末尾加 ThisWorkbook.Save 或者 ThisWorkbook.SaveAs Filename:=path, FileFormat:=xlOpenXMLWorkbook。

VBA 与 macOS Excel 兼容吗？

大部分语法兼容，但 Workbooks.Open 的某些参数与 Dir 函数在 macOS 上行为略有不同。Mac 路径分隔符是 / 而不是 \。如果跨平台共享宏，避免硬编码路径分隔符，用 Application.PathSeparator。

怎样把宏分享给同事？

把 .xlsm 文件直接发给同事，对方信任中心需要允许宏。更优雅的做法是把宏代码导出为 .bas 文件，让同事在自己的 Excel 里 import。或者打包成 Excel Add-in（.xlam）一次安装到对方 Excel。

合并后的 Excel 文件比预期大很多？

Excel 文件包含所有未删除的格式与样式记忆。合并后调用 ActiveSheet.UsedRange 看是否包含空区域；用 Sub Reset() 重置 UsedRange；保存为 .xlsb 二进制格式比 .xlsx 文件小 50%。