VBA读取DAT文件时字符间出现空格的问题排查与解决

我需要将存储测量值的DAT文件通过VBA导入Access数据库，测试读取单个文件时遇到异常：读取后所有字符间都出现了空格，用Replace函数无法去除。

测试代码 #

Option Compare Database

Sub TEST_ReadFile()
    Dim fso
    Dim txtstream
    
    Dim hf As Integer: hf = FreeFile
    Dim lines() As String, i As Long
        
    Dim tempLine As String
    Dim strResult As String
    Dim tempLineArray As Variant
    
    Dim sampleGroupID As Integer
    Dim sampleGroupDesc As String
    
    Dim sampleID As Integer
    Dim sampleName As String
    Dim liquid As String
    Dim dateTime As String
    
    Dim valueID As Integer
    Dim time As Date
    Dim base As Double
    Dim height As Double
    Dim volume As Double
    Dim angle As Double
    Dim area As Double
    Dim m_height As Double
    Dim sideArea As Double
        
    
    myFile = "C:\Users\XXX\Documents\N4000289.DAT"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set txtstream = fso.OpenTextFile(myFile, 1, True)
 
    line = 1
    Do While Not txtstream.AtEndofStream
        tempLine = txtstream.ReadLine()
        If line = 1 Then
            'get data from the header
            tempLineArray = Split(tempLine, vbTab)
            
            If UBound(tempLineArray) - LBound(tempLineArray) + 1 = 3 Then
                sampleName = Replace(Replace(tempLineArray(0), "ÿþ", ""), " ", "")
                'Get rid of strange characters at the start of the file "ÿþ" (working) as well as empty spaces (not working)
                Debug.Print "sampleName: " & sampleName
                
                sampleGroupDesc = Replace(tempLineArray(1), " ", "")
                'Get rid of empty spaces (not working)
                Debug.Print "sampleGroupDesc: " & sampleGroupDesc
                
                liquid = Replace(tempLineArray(2), " ", "")
                'Get rid of empty spaces (not working)
                Debug.Print "liquid: " & liquid
            End If
            
        ElseIf line > 2 Then
            'get the measurement values
        End If


        line = line + 1
    Loop
    txtstream.Close
End Sub

DAT文件前几行内容（记事本打开） #

N4000289    N. 40g  Ethylene glycol
Time    Base    Height  Volume  Angle   Area    M_Height    SideArea
0,000   0,00    0,00    0,00    0,0 0,00    0,00    0,00
0,010   0,00    0,00    0,00    0,0 0,00    0,00    0,00
0,020   0,00    0,00    0,00    0,0 0,00    0,00    0,00
0,030   2,76    1,15    3,91    77,4    5,99    0,65    2,28
0,040   2,87    1,03    3,80    70,6    6,47    0,59    2,13

调试输出异常 #

sampleName: N 4 0 0 0 2 8 9 
sampleGroupDesc:  N .  4 0 g 
liquid:  E t h y l e n e  g l y c o l 

问题成因 #

这些不是普通空格，而是UTF-16LE（Unicode小端序）编码的文件被当作ANSI编码读取导致的：

1. 文件开头的ÿþ是UTF-16LE的BOM（字节顺序标记，对应十六进制FF FE）；
2. UTF-16LE中每个字符占2个字节，当用默认的ANSI方式读取时，每个字符的高字节（通常是0x00）被解析成了空格字符，最终表现为字符间出现“空格”；
3. Replace(tempLineArray(0), " ", "")无效，因为这些“空格”不是ASCII空格（0x20），而是0x00的空字符。

解决方法 #

方法1：修改FileSystemObject的打开参数（最简方案） #

FSO的OpenTextFile方法支持指定编码，传入-1表示Unicode（即UTF-16LE），替换原代码中的打开语句即可：

' 替换原代码中的文件打开行
Set txtstream = fso.OpenTextFile(myFile, 1, True, -1)

修改后读取的内容会自动解析为正确字符，无需处理BOM和空字符，直接用Trim去除真实空格即可。

方法2：用ADODB.Stream读取（编码控制更灵活） #

使用ADODB.Stream指定UTF-16LE编码读取文件，适用于需要更精细编码控制的场景：

Option Compare Database

Sub TEST_ReadFile_Fixed()
    Dim stream As Object
    Dim myFile As String
    Dim tempLine As String
    Dim tempLineArray As Variant
    Dim line As Long
    
    myFile = "C:\Users\XXX\Documents\N4000289.DAT"
    Set stream = CreateObject("ADODB.Stream")
    
    ' 配置流参数：UTF-16LE编码
    stream.Charset = "utf-16le"
    stream.Open
    stream.LoadFromFile myFile
    
    line = 1
    Do Until stream.EOS
        tempLine = stream.ReadText(-2) ' -2表示读取一行
        If line = 1 Then
            tempLineArray = Split(tempLine, vbTab)
            If UBound(tempLineArray) - LBound(tempLineArray) + 1 = 3 Then
                ' ADODB.Stream会自动识别BOM，无需额外处理
                Dim sampleName As String, sampleGroupDesc As String, liquid As String
                sampleName = Trim(tempLineArray(0))
                sampleGroupDesc = Trim(tempLineArray(1))
                liquid = Trim(tempLineArray(2))
                
                Debug.Print "sampleName: " & sampleName
                Debug.Print "sampleGroupDesc: " & sampleGroupDesc
                Debug.Print "liquid: " & liquid
            End If
        ElseIf line > 2 Then
            ' 处理测量值逻辑
        End If
        line = line + 1
    Loop
    
    stream.Close
    Set stream = Nothing
End Sub

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