通过Excel VBA结合SQL导入过滤文本文件时遇字节数据异常问题

我碰到过不少类似的情况——用ADODB导入包含十六进制字节数据的文本文件时，总会出现黑单元格、错误值或者数据丢失，哪怕换了各种Provider都没用。核心问题其实出在ADODB文本驱动的自动类型推断机制上，咱们一步步来解决：

问题根源 #

你的文本文件里的列混合了十六进制值（比如FF、EE）和数值（比如0、1、5），ADODB默认会采样前几行数据来推断列类型。当它看到某列既有数值又有文本格式的十六进制值时，会优先把列识别为数值类型，导致非数值的十六进制值被解析成错误或者直接丢弃。哪怕你加了IMEX=1，也可能因为采样行数的限制（默认是前8行）没覆盖到所有数据类型，导致失效。

解决方案：用Schema.ini强制指定列类型 #

最可靠的办法是在文本文件所在目录创建一个Schema.ini文件，直接告诉驱动每一列的数据类型都是文本，彻底绕过自动推断。

步骤1：创建Schema.ini文件 #

在你的文本文件目录（也就是I:\Codici\Excel\filtra_file_testo）下新建一个文本文件，命名为Schema.ini，内容如下：

[test_2.txt]
Format=Delimited(;)
ColNameHeader=False
Col1=F1 Text
Col2=F2 Text
Col3=F3 Text
Col4=F4 Text
Col5=F5 Text
Col6=F6 Text
Col7=F7 Text
Col8=F8 Text
Col9=F9 Text
Col10=F10 Text

• [test_2.txt]：指定这个配置对应你的目标文本文件
• Format=Delimited(;)：说明文件是分号分隔的
• ColNameHeader=False：你的文件没有表头，所以设为False
• 每一行ColX=FX Text：强制第X列的类型为文本，避免驱动乱推断

步骤2：修改VBA代码 #

调整后的代码会依赖Schema.ini的配置，确保所有列都以文本形式读取，同时优化了一些细节：

Sub FilterFile2()
    Dim strPath As String
    Dim strTable As String
    Dim objConnection As ADODB.Connection
    Dim objRecSet As ADODB.Recordset
    Dim strConnection As String
    Dim strSql As String
    Dim ws As Worksheet
    
    strPath = "I:\Codici\Excel\filtra_file_testo"
    strTable = "test_2.txt"
    
    ' 使用ACE驱动，结合Schema.ini的配置
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & strPath & ";Extended Properties='Text;HDR=NO;IMEX=1'"
    
    Set objConnection = New ADODB.Connection
    objConnection.Open strConnection
    
    ' 筛选F3='FF'的记录，表名用[]括起来避免特殊字符问题
    strSql = "SELECT * FROM [" & strTable & "] WHERE F3='FF'"
    Debug.Print strSql
    
    Set objRecSet = New ADODB.Recordset
    objRecSet.Open strSql, objConnection, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    If objRecSet.State = adStateOpen Then
        Set ws = ActiveSheet
        ' 从指定位置写入数据
        ws.Cells(12, 2).CopyFromRecordset objRecSet
    End If
    
    ' 清理资源，避免内存泄漏
    objRecSet.Close
    objConnection.Close
    Set objRecSet = Nothing
    Set objConnection = Nothing
End Sub

补充说明 #

• 这个方法对30MB的大文件同样有效，ADODB处理大文本文件的效率比逐行读取高很多
• 如果要处理多个文本文件，只需在Schema.ini中添加对应文件的配置块即可
• IMEX=1参数可以保留，虽然Schema.ini已经强制了文本类型，但它能在意外情况时提供额外的兼容保障

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