VBA中UTF-8文件日文数据提取导入乱码问题排查

你的乱码问题源于两个核心编码处理错误：

• 读取UTF-8文件时使用Open Input语句，该语句默认采用系统本地编码解析文件，导致UTF-8格式的日文被错误解码。
• 写入文件时指定Charset = "SHIFT-JIS"，而非目标要求的UTF-8，进一步加剧编码转换错误。

解决方案 #

使用ADODB.Stream统一处理UTF-8编码的文件读写，避免系统默认编码干扰，同时优化写入时机提升效率。

修改后的完整代码 #

Sub ReadTextFileDataInExcel()
    Dim TblNum As String
    TblNum = Worksheets("data").Range("A2").Value
    Dim RowNumber As Long
    Dim TextFile As String
    Dim LineData As String
    Dim newTxt As String
    Dim objStream As Object
    
    ' 定义文件路径
    TextFile = ThisWorkbook.Path & "\" & TblNum & "\CA003\10_RunScript\20.ExpectResult.sql"
    MyFileName = ThisWorkbook.Path & "\" & TblNum & "\CA003\10_RunScript\40.ExpectResult.sql"
      
    RowNumber = 1
    
    ' 使用ADODB.Stream读取UTF-8文件
    Set objStream = CreateObject("adodb.stream")
    With objStream
        .Type = 2 ' adTypeText
        .Charset = "UTF-8"
        .Open
        .LoadFromFile TextFile
        Do While Not .EOS
            LineData = .ReadText(-2) ' 按行读取
            If LineData Like "insert*" Then
                Worksheets("40.ExpectResult_TEMP").Range("A" & RowNumber).Value = LineData
                LineData = Replace(LineData, "_DUMMY", "")
                newTxt = newTxt & vbCr & LineData
                RowNumber = RowNumber + 1
            End If
        Loop
        .Close
    End With
    Set objStream = Nothing
    
    ' 循环结束后统一写入文件，避免重复写入
    If newTxt <> "" Then
        WriteIfFile_utf8 MyFileName, Mid(newTxt, 2) ' 去掉开头多余的vbCr
    End If
End Sub

Function WriteIfFile_utf8(strPath As Variant, str As Variant)
    Dim objStream As Object
    Set objStream = CreateObject("adodb.stream")

    With objStream
        .Type = 2 ' adTypeText
        .Charset = "UTF-8" ' 指定输出为UTF-8编码
        .Open
        .WriteText str
        .SaveToFile strPath, 2 ' adSaveCreateOverWrite
        .Close
    End With
    Set objStream = Nothing
End Function

关键修改说明 #

• 读取逻辑优化：替换Open Input为ADODB.Stream，明确指定Charset = "UTF-8"确保正确解码UTF-8格式的日文数据，用.ReadText(-2)实现按行读取。
• 写入编码修正：将写入函数的Charset改为UTF-8，保证输出文件为UTF-8编码。
• 写入时机调整：将文件写入操作移到循环外部，避免每读取一行就重复写入，提升效率同时避免内容冗余。

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