基于VBA解析pdftotext生成的TXT文件，提取SKU与数量数据

一、提取SKU与数量的实现代码 #

针对SKU行紧跟数量行（数量格式如2 of 2取数字2）的规则，可通过拆分文本行+正则匹配实现精准提取：

Sub ParseSKUAndQuantity(itemInfoBlock As String)
    Dim lineArray() As String
    Dim currentIndex As Integer
    Dim targetSKU As String
    Dim targetQty As Integer
    
    ' 按换行拆分商品信息块为单行数组
    lineArray = Split(itemInfoBlock, vbCrLf)
    
    ' 遍历行（预留最后一行位置，防止越界）
    For currentIndex = LBound(lineArray) To UBound(lineArray) - 1
        Dim currentLine As String
        currentLine = Trim(lineArray(currentIndex))
        Dim nextLine As String
        nextLine = Trim(lineArray(currentIndex + 1))
        
        ' 跳过空行，判断当前行是否为SKU行（需结合实际SKU特征调整，比如排除数量行格式）
        If currentLine <> "" And Not IsQuantityLine(nextLine) Then
            ' 检查下一行是否为数量行
            If IsQuantityLine(nextLine) Then
                targetSKU = currentLine
                targetQty = GetQuantityFromLine(nextLine)
                ' 此处可将结果写入工作表或存储到集合，示例：
                ' Sheets("结果").Cells(Sheets("结果").Rows.Count, 1).End(xlUp).Offset(1, 0) = targetSKU
                ' Sheets("结果").Cells(Sheets("结果").Rows.Count, 1).End(xlUp).Offset(0, 1) = targetQty
                Debug.Print "SKU: " & targetSKU & " | 数量: " & targetQty
            End If
        End If
    Next currentIndex
End Sub

' 判断是否为数量行（匹配"X of X"格式）
Private Function IsQuantityLine(lineText As String) As Boolean
    Dim regexObj As Object
    Set regexObj = CreateObject("VBScript.RegExp")
    regexObj.Pattern = "^(\d+) of \d+$"
    IsQuantityLine = regexObj.Test(lineText)
End Function

' 从数量行提取目标数字
Private Function GetQuantityFromLine(lineText As String) As Integer
    Dim regexObj As Object
    Dim matchCollection As Object
    Set regexObj = CreateObject("VBScript.RegExp")
    regexObj.Pattern = "^(\d+) of \d+$"
    
    Set matchCollection = regexObj.Execute(lineText)
    If matchCollection.Count > 0 Then
        GetQuantityFromLine = CInt(matchCollection(0).SubMatches(0))
    Else
        GetQuantityFromLine = 0 ' 无匹配时返回0，可根据需求调整
    End If
End Function

代码说明 #

1. 先将商品信息块itemInfoBlock拆分为单行数组，逐行遍历；
2. 通过正则判断下一行是否为数量行，避免误判；
3. 提取数量时直接捕获正则匹配的第一个数字组，确保准确性；
4. 可根据实际SKU的特征（如固定前缀、格式）调整currentLine的判断条件，进一步提升精准度。

二、现有解析方案的优化建议 #

1. 文本预处理：pdftotext的-raw选项可能产生冗余空格、不规则换行，预处理时可统一换行符为vbCrLf，合并连续空格，去除空行：

   Dim processedText As String
   processedText = Replace(rawText, vbLf, vbCrLf)
   processedText = Replace(processedText, "  ", " ") ' 合并连续空格
   processedText = Replace(processedText, vbCrLf & vbCrLf, vbCrLf) ' 去除空行
   

2. 正则对象复用：现有代码若多次创建正则对象，可提前全局声明复用，减少资源消耗：

   Private regexQty As Object
   Sub InitRegex()
       Set regexQty = CreateObject("VBScript.RegExp")
       regexQty.Pattern = "^(\d+) of \d+$"
   End Sub
   

3. 批量文件处理：若需处理多个TXT文件，用FileSystemObject批量读取，避免手动操作：

   Dim fso As Object, file As Object, folder As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set folder = fso.GetFolder("C:\PDF_TXT_Files")
   For Each file In folder.Files
       If LCase(fso.GetExtensionName(file.Name)) = "txt" Then
           ' 调用解析函数处理当前文件
       End If
   Next file
   

4. 精准特征匹配：提取PO号、地址等信息时，优先用固定前缀匹配（如PO#: ），而非模糊查找，提升效率和准确率：

   Dim poNum As String
   Dim poStart As Integer
   poStart = InStr(rawText, "PO#: ")
   If poStart > 0 Then
       poNum = Mid(rawText, poStart + 5, InStr(poStart + 5, rawText, vbCrLf) - (poStart + 5))
   End If
   

5. 错误捕获与日志：增加On Error Resume Next或On Error GoTo错误处理，记录解析失败的文件行或内容，便于排查问题。

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