VBA中用Power Query导入UTF-8 CSV并替换字符（无需指定表头）

问题说明 #

现有CSV文件需通过VBA导入，要求：

• 以UTF-8编码读取文件
• 将文件中“Replace Column 1”和“Replace Column 2”列的逗号（,）替换为点（.）
• 询问能否在Power Query的let公式中不提及表头名称完成替换操作

实现方案 #

1. 带数据转换的CSV导入（保留表头名称写法） #

原录制的宏已经实现核心功能，以下是优化可读性后的VBA代码，确保UTF-8编码（Encoding=65001）和替换逻辑正确：

Sub ImportCSVWithReplace()
    ' 添加Power Query查询
    ActiveWorkbook.Queries.Add Name:="ENG CSV", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Csv.Document(File.Contents(""E:\ENG CSV.csv""),[Delimiter="","", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type datetime}, {""Created By"", type text}, {""Replace Column 1"", type text}, {""Replace Column 2"", type text}})," & Chr(13) & "" & Chr(10) & _
        "    #""Replaced Value"" = Table.ReplaceValue(#""Changed Type"","","",""."",Replacer.ReplaceText,{""Replace Column 1"", ""Replace Column 2""})" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Replaced Value"""
    
    ' 新建工作表并加载查询数据
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ENG CSV"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [ENG CSV]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "ENG_CSV"
        .Refresh BackgroundQuery:=False
    End With
End Sub

2. 不提及表头名称的实现方法 #

可以通过列索引而非表头名称指定要替换的列，无需依赖表头文字。修改Power Query公式逻辑，先执行替换再提升表头，以下是调整后的VBA代码：

Sub ImportCSVWithReplace_NoHeaderName()
    ActiveWorkbook.Queries.Add Name:="ENG CSV No Header", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
        "    Source = Csv.Document(File.Contents(""E:\ENG CSV.csv""),[Delimiter="","", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & _
        "    #""Replace Commas in Columns"" = Table.ReplaceValue(Source,"","",""."",Replacer.ReplaceText,{3, 4})," & Chr(13) & "" & Chr(10) & _
        "    #""Promoted Headers"" = Table.PromoteHeaders(#""Replace Commas in Columns"", [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type datetime}, {""Created By"", type text}, {""Replace Column 1"", type text}, {""Replace Column 2"", type text}})" & Chr(13) & "" & Chr(10) & _
        "in" & Chr(13) & "" & Chr(10) & _
        "    #""Changed Type"""
    
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ENG CSV No Header"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [ENG CSV No Header]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "ENG_CSV_No_Header"
        .Refresh BackgroundQuery:=False
    End With
End Sub

关键说明：

• 在Table.ReplaceValue中使用{3, 4}指定第3、4列（CSV文件列索引从1开始计数），完全不需要提及表头名称
• 先执行逗号替换操作，再提升表头并设置数据类型，彻底摆脱对表头文字的依赖

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