VBA处理CSV文件的分隔符与区域设置问题求助

核心问题分析 #

你的问题根源是区域设置导致的CSV分隔符识别冲突：法语系统默认CSV分隔符为逗号，但目标CSV用分号分隔，直接调用OpenText时若参数配置不全，Excel会优先遵循系统区域规则，导致整行内容被识别为单个单元格。

修正CSV读取逻辑 #

先解决CSV解析问题，在OpenText中补全参数，强制指定分号为分隔符并禁用区域本地化解析：

Dim fileBt As String
fileBt = SequoiaPath & Left(Filename, InStr(Filename, ".") - 1) & "/BT/NipBtSearch.csv"
            
FileCopy Path & Filename, fileBt
Dim Wbt As Excel.Workbook

' 强制按分号分隔读取，忽略系统区域设置
Workbooks.OpenText _
    Filename:=fileBt, _
    DataType:=xlDelimited, _
    Semicolon:=True, _
    Comma:=False, _
    Local:=False ' 关键参数：关闭区域本地化解析

Set Wbt = ActiveWorkbook

' 此时A1会显示第一列表头，而非整行内容
Debug.Print Wbt.Sheets(1).Range("A1").Value

Wbt.Close True

拆分CSV为两个目标文件的完整实现 #

要完成“拆分输入CSV为两个文件、重命名指定列、删除冗余列”的需求，可按以下流程操作：

• 读取原始CSV并确保正确解析列
• 复制原始工作表到新工作簿，分别处理两个目标文件的列保留与重命名
• 强制输出分号分隔的CSV，不受区域设置影响

完整示例代码：

Sub SplitCSVToTwoFiles()
    Dim originalPath As String, originalName As String
    Dim outputPath1 As String, outputPath2 As String
    Dim wbOriginal As Workbook, wbOutput1 As Workbook, wbOutput2 As Workbook
    Dim wsOriginal As Worksheet, wsOutput1 As Worksheet, wsOutput2 As Worksheet
    
    ' 替换为你的实际文件路径
    originalPath = "C:\YourInputFolder\"
    originalName = "Source.csv"
    outputPath1 = originalPath & "Target1.csv"
    outputPath2 = originalPath & "Target2.csv"
    
    ' 1. 读取原始CSV，强制分号分隔
    Workbooks.OpenText _
        Filename:=originalPath & originalName, _
        DataType:=xlDelimited, _
        Semicolon:=True, _
        Comma:=False, _
        Local:=False
    Set wbOriginal = ActiveWorkbook
    Set wsOriginal = wbOriginal.Sheets(1)
    
    ' 2. 处理第一个目标文件：保留A、C列，重命名表头
    wsOriginal.Copy
    Set wbOutput1 = ActiveWorkbook
    Set wsOutput1 = wbOutput1.Sheets(1)
    
    ' 删除冗余列（示例：删除B、D及之后的列）
    wsOutput1.Range("B:B,D:XFD").Delete Shift:=xlToLeft
    ' 重命名表头
    wsOutput1.Range("A1").Value = "用户ID"
    wsOutput1.Range("B1").Value = "订单编号"
    
    ' 强制保存为分号分隔的CSV
    SaveAsSemicolonCSV wsOutput1, outputPath1
    wbOutput1.Close False
    
    ' 3. 处理第二个目标文件：保留B、D列，重命名表头
    wsOriginal.Copy
    Set wbOutput2 = ActiveWorkbook
    Set wsOutput2 = wbOutput2.Sheets(1)
    
    ' 删除冗余列（示例：删除A、C及之后的列）
    wsOutput2.Range("A:A,C:C,E:XFD").Delete Shift:=xlToLeft
    ' 重命名表头
    wsOutput2.Range("A1").Value = "用户名"
    wsOutput2.Range("B1").Value = "订单金额"
    
    ' 强制保存为分号分隔的CSV
    SaveAsSemicolonCSV wsOutput2, outputPath2
    wbOutput2.Close False
    
    ' 4. 关闭原始工作簿
    wbOriginal.Close False
End Sub

' 自定义函数：强制输出分号分隔的CSV，避免区域设置干扰
Sub SaveAsSemicolonCSV(ws As Worksheet, filePath As String)
    Dim fso As Object, ts As Object
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long
    Dim lineText As String, cellValue As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.CreateTextFile(filePath, True, True) ' True=覆盖文件，True=UTF-8编码
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' 逐行写入，按CSV规则转义特殊字符
    For i = 1 To lastRow
        lineText = ""
        For j = 1 To lastCol
            cellValue = ws.Cells(i, j).Value
            ' 若单元格包含分号或引号，用双引号包裹并转义内部引号
            If InStr(cellValue, ";") > 0 Or InStr(cellValue, """") > 0 Then
                cellValue = """" & Replace(cellValue, """", """""") & """"
            End If
            lineText = lineText & cellValue & IIf(j < lastCol, ";", "")
        Next j
        ts.WriteLine lineText
    Next i
    
    ts.Close
    Set ts = Nothing
    Set fso = Nothing
End Sub

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