如何将替换LF为CR的逗号分隔字符串转为VBA二维数组并统计行列数

针对你处理超大型CSV文件（300MB+、百万级元素）并转换为二维数组的需求，我整理了一套兼顾速度与内存友好的解决方案，适配你已经将LF替换为CR的前置操作：

一、先确定列数（核心前提） #

CSV规范中通常每行列数一致，我们只需提取第一行数据统计列数即可。根据你的CSV是否包含带引号的字段（字段内可能含逗号），分两种情况处理：

情况1：CSV字段无引号（纯逗号分隔）

Function GetColumnCount(ByVal csvStr As String, ByVal rowDelim As String) As Long
    Dim firstRowEnd As Long
    firstRowEnd = InStr(csvStr, rowDelim)
    If firstRowEnd = 0 Then
        ' 仅一行数据的情况
        GetColumnCount = UBound(Split(csvStr, ",")) + 1
        Exit Function
    End If
    Dim firstRow As String
    firstRow = Left(csvStr, firstRowEnd - 1)
    GetColumnCount = UBound(Split(firstRow, ",")) + 1
End Function

情况2：CSV字段含引号（需规避字段内逗号）

如果存在类似"John, Doe",30,New York的字段，直接按逗号拆分会出错，需要用严谨的逻辑识别引号状态：

Function GetColumnCountWithQuotes(ByVal csvStr As String, ByVal rowDelim As String) As Long
    Dim inQuotes As Boolean
    Dim char As String
    Dim colCount As Long
    colCount = 1 ' 默认至少1列
    
    For i = 1 To Len(csvStr)
        char = Mid(csvStr, i, 1)
        If char = """" Then
            inQuotes = Not inQuotes
        ElseIf char = "," And Not inQuotes Then
            colCount = colCount + 1
        ElseIf char = rowDelim And Not inQuotes Then
            ' 找到第一行末尾，终止循环
            Exit For
        End If
    Next i
    GetColumnCountWithQuotes = colCount
End Function

二、统计行数（内存友好版） #

直接用Split统计行数会生成巨大的行数组，占用大量内存，推荐用逐行定位的高效方法：

Function GetRowCount(ByVal csvStr As String, ByVal rowDelim As String) As Long
    Dim count As Long
    Dim pos As Long
    pos = InStr(csvStr, rowDelim)
    
    Do While pos > 0
        count = count + 1
        pos = InStr(pos + 1, csvStr, rowDelim)
    Loop
    ' 处理最后一行无终止符的情况
    If Right(csvStr, Len(rowDelim)) <> rowDelim Then
        count = count + 1
    End If
    GetRowCount = count
End Function

三、转换为二维数组（逐行处理，低内存压力） #

针对大文件，避免一次性拆分整个字符串，采用逐行读取+动态数组的方式：

Function CSVTo2DArray(ByVal csvStr As String, ByVal rowDelim As String, ByVal colCount As Long) As Variant
    Dim rowCount As Long
    rowCount = GetRowCount(csvStr, rowDelim)
    
    ' 初始化二维数组（行×列，下标从1开始更符合VBA习惯）
    Dim resultArr As Variant
    ReDim resultArr(1 To rowCount, 1 To colCount)
    
    Dim currentRow As Long
    Dim currentCol As Long
    Dim startPos As Long
    Dim endPos As Long
    Dim rowStr As String
    Dim colArr As Variant
    
    startPos = 1
    currentRow = 1
    
    Do While startPos <= Len(csvStr)
        endPos = InStr(startPos, csvStr, rowDelim)
        If endPos = 0 Then endPos = Len(csvStr) + 1
        
        rowStr = Mid(csvStr, startPos, endPos - startPos)
        ' 拆分列（如果有引号，替换为带引号处理的拆分逻辑）
        colArr = Split(rowStr, ",")
        
        For currentCol = 1 To colCount
            If currentCol <= UBound(colArr) + 1 Then
                resultArr(currentRow, currentCol) = colArr(currentCol - 1)
            Else
                ' 列数不足时补空
                resultArr(currentRow, currentCol) = ""
            End If
        Next currentCol
        
        currentRow = currentRow + 1
        startPos = endPos + Len(rowDelim)
    Loop
    
    CSVTo2DArray = resultArr
End Function

四、完整调用示例 #

Sub ProcessLargeCSV()
    Dim fName As String
    fName = "C:\YourLargeFile.csv" ' 替换为你的文件路径
    
    Dim Buf As String
    Open fName For Binary As #1
    Buf = String$(LOF(1), 0)
    Get #1, , Buf
    Close #1
    
    ' 替换LF为CR
    Buf = Replace$(Buf, vbLf, vbCr)
    
    ' 获取列数（根据CSV是否含引号选择对应函数）
    Dim colCount As Long
    colCount = GetColumnCount(Buf, vbCr)
    ' colCount = GetColumnCountWithQuotes(Buf, vbCr) ' 有引号时启用
    
    ' 获取行数
    Dim rowCount As Long
    rowCount = GetRowCount(Buf, vbCr)
    Debug.Print "行数：" & rowCount & "，列数：" & colCount
    
    ' 转换为二维数组
    Dim csvArr As Variant
    csvArr = CSVTo2DArray(Buf, vbCr, colCount)
    
    ' 测试输出第一行第一列
    Debug.Print "第一行第一列：" & csvArr(1, 1)
End Sub

额外注意事项 #

1. 内存优化：若文件超过500MB，建议分块读取处理，避免一次性加载整个文件到内存导致溢出。
2. 空行处理：如果CSV存在空行，GetRowCount会统计为空行，可在转换数组时判断行内容是否为空并跳过。
3. 数据类型转换：如果需要将数值型字段转为对应类型，可在拆分列后增加类型判断逻辑。

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