VBA使用QueryTables.Add导入CSV时避免数据类型自动识别

手动导入CSV时通过「不检测数据类型」能避免"21-01"被自动转为日期格式，但直接设置TextFileColumnDataTypes可能因参数配置不全失效。以下是可实现相同效果的VBA代码，包含固定列数和自动适配列数两种场景。

核心解决方案代码 #

固定列数场景（已知CSV列数） #

Sub ImportCSVAsText_FixedColumns()
    Dim qt As QueryTable
    Dim csvFilePath As String
    Dim targetCell As Range
    
    ' 配置路径和目标位置
    csvFilePath = "C:\Documents\your_file.csv" ' 替换为你的CSV路径
    Set targetCell = ThisWorkbook.Sheets("ImportSheet").Range("A1") ' 替换为目标工作表和起始单元格
    
    ' 清除已有查询表（防止重复导入报错）
    On Error Resume Next
    targetCell.Worksheet.QueryTables.Delete
    On Error GoTo 0
    
    ' 创建并配置查询表
    Set qt = targetCell.Worksheet.QueryTables.Add( _
        Connection:="TEXT;" & csvFilePath, _
        Destination:=targetCell)
    
    With qt
        .TextFileParseType = xlDelimited ' 指定为分隔符解析类型
        .TextFileCommaDelimiter = True ' CSV默认逗号分隔，若为分号则改为.TextFileSemicolonDelimiter = True
        .TextFileTextQualifier = xlTextQualifierDoubleQuote ' 适配带引号的字段
        ' 将所有列设为文本格式，数组长度需与CSV列数一致（示例为3列）
        .TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat)
        .TextFileTrailingMinusNumbers = True ' 保留负数格式
        .RefreshStyle = xlOverwriteCells ' 覆盖目标区域已有内容
        .BackgroundQuery = False ' 同步执行导入
        .Refresh ' 执行导入操作
        .Delete ' 导入完成后删除查询表，避免后续冲突
    End With
End Sub

自动适配列数场景（未知CSV列数） #

如果CSV列数不固定，可先读取第一行计算列数，再动态生成文本格式数组：

Sub ImportCSVAsText_DynamicColumns()
    Dim qt As QueryTable
    Dim csvFilePath As String
    Dim targetCell As Range
    Dim colCount As Integer
    Dim textTypes() As Variant
    Dim i As Integer
    
    csvFilePath = "C:\Documents\your_file.csv"
    Set targetCell = ThisWorkbook.Sheets("ImportSheet").Range("A1")
    
    ' 获取CSV列数
    colCount = GetCSVColumnCount(csvFilePath)
    If colCount = 0 Then Exit Sub ' 列数获取失败则退出
    
    ' 生成文本格式数组
    ReDim textTypes(1 To colCount)
    For i = 1 To colCount
        textTypes(i) = xlTextFormat
    Next i
    
    ' 清除旧查询表并创建新表
    On Error Resume Next
    targetCell.Worksheet.QueryTables.Delete
    On Error GoTo 0
    
    Set qt = targetCell.Worksheet.QueryTables.Add( _
        Connection:="TEXT;" & csvFilePath, _
        Destination:=targetCell)
    
    With qt
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileColumnDataTypes = textTypes ' 应用动态生成的文本格式数组
        .TextFileTrailingMinusNumbers = True
        .RefreshStyle = xlOverwriteCells
        .BackgroundQuery = False
        .Refresh
        .Delete
    End With
End Sub

' 辅助函数：读取CSV第一行计算列数
Function GetCSVColumnCount(csvPath As String) As Integer
    Dim fileNum As Integer
    Dim firstLine As String
    Dim columnArray() As String
    
    On Error GoTo ErrorHandler
    fileNum = FreeFile()
    Open csvPath For Input As #fileNum
    Line Input #fileNum, firstLine ' 读取第一行（表头或数据行）
    Close #fileNum
    
    columnArray = Split(firstLine, ",") ' 按逗号分割，若为其他分隔符需修改
    GetCSVColumnCount = UBound(columnArray) + 1
    Exit Function
    
ErrorHandler:
    GetCSVColumnCount = 0
    Close #fileNum
End Function

CSV示例文件内容 #

ID,Code,Amount
1,21-01,150
2,22-02,200
3,23-03,300

关键注意事项 #

• 分隔符适配：若CSV使用分号、制表符等分隔符，需修改代码中对应的TextFileXXXDelimiter属性（如TextFileSemicolonDelimiter = True）。
• 表头设置：如果CSV无表头，需在With qt块中添加.TextFileHeaderRow = False。
• 路径替换：务必将代码中的csvFilePath替换为实际的CSV文件路径。

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