SSIS脚本任务中VBA转换UTF-8 CSV至标准CSV的逻辑排查

原代码存在的核心问题 #

• 文件筛选逻辑错误：Dir(folderPath & "CO2 *" & fileExt) 中的多余空格会导致仅匹配CO2 （带空格）开头的文件，而非所有CO2开头的目标文件。
• SaveAs文件名错误：filePath & oWorkbook.Name 会生成重复文件名（比如原路径是C:\data\CO2_01.csv，拼接后变成C:\data\CO2_01.csvCO2_01.csv），完全偏离覆盖原文件的需求。
• Excel资源未释放：未关闭工作簿和退出Excel进程，会导致后台残留Excel实例，持续占用系统资源。
• 变量声明不规范：Dim oExcel 未指定对象类型，属于变体类型易引发潜在问题；VBA中对象赋值需使用Set关键字，原代码遗漏该操作。
• UTF-8文件编码识别问题：直接用Workbooks.Open打开UTF-8 CSV时，Excel可能无法正确识别编码，导致内容乱码，影响后续转换结果。

修正后的代码 #

Public Sub Main()
    ' 设置文件所在文件夹路径
    Dim folderPath As String
    folderPath = "<path>"
    ' 确保路径末尾带斜杠，避免拼接错误
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

    ' 设置要筛选的文件扩展名
    Dim fileExt As String
    fileExt = "*.csv"

    ' 初始化要处理的文件（匹配CO2开头的CSV）
    Dim fileName As String
    fileName = Dir(folderPath & "CO2*" & fileExt)

    ' 循环处理所有匹配文件
    Do While Len(fileName) > 0
        Dim filePath As String
        filePath = folderPath & fileName

        ConvertAndOverwriteCSV filePath

        ' 获取下一个文件
        fileName = Dir()
    Loop
    MsgBox "CO2文件转换完成。"
End Sub

Sub ConvertAndOverwriteCSV(filePath As String)
    Dim oExcel As Object
    Dim oWorkbook As Object

    ' 创建Excel对象实例
    Set oExcel = CreateObject("Excel.Application")
    ' 禁用Excel交互元素
    oExcel.Visible = False
    oExcel.DisplayAlerts = False
    oExcel.AskToUpdateLinks = False
    oExcel.AlertBeforeOverwriting = False

    ' 以UTF-8编码打开CSV文件（解决编码识别问题）
    Set oWorkbook = oExcel.Workbooks.Open( _
        Filename:=filePath, _
        Format:=6, ' 6代表CSV格式
        Local:=True ' 使用系统区域设置，辅助识别UTF-8
    )

    ' 覆盖原文件，FileFormat=6为CSV（逗号分隔）格式
    oWorkbook.SaveAs _
        Filename:=filePath, _
        FileFormat:=6, _
        Local:=True

    ' 释放Excel资源
    oWorkbook.Close SaveChanges:=False
    oExcel.Quit
    Set oWorkbook = Nothing
    Set oExcel = Nothing
End Sub

关键修改说明 #

1. 修正文件筛选：将"CO2 *"改为"CO2*"，确保匹配所有以CO2开头的CSV文件；同时添加路径末尾斜杠的判断，避免路径拼接错误。
2. 修正SaveAs路径：直接使用原filePath作为保存路径，实现覆盖原文件的需求。
3. 添加资源释放：在转换完成后关闭工作簿、退出Excel，并将对象置空，避免后台进程残留。
4. 规范对象声明：给oExcel和oWorkbook指定As Object类型，使用Set关键字完成对象赋值。
5. 优化UTF-8打开逻辑：在Workbooks.Open和SaveAs中添加Local:=True，帮助Excel正确识别UTF-8编码的CSV文件，同时确保输出符合系统区域设置的标准CSV格式。

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