Power Query PDF文本提取异常问题排查及批量URL下载PDF转文本的VBA实现需求

Hey there, let's break down your two questions—you mentioned prioritizing the second one if the first can't be resolved, so we'll start there:

Problem 2: Batch Download PDFs from URLs & Convert to Text via Excel VBA #

Here are two practical VBA solutions to handle your batch download and conversion needs, depending on whether you have Adobe Acrobat installed:

Approach 1: Using Adobe Acrobat (Requires Full Acrobat Installation) #

This leverages Acrobat's COM API to convert PDFs directly to text without external tools:

Sub BatchPDFDownloadAndConvert()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim url As String
    Dim pdfPath As String
    Dim textPath As String
    Dim acroApp As Object
    Dim acroDoc As Object
    
    ' Set worksheet with your URL list (update sheet name as needed)
    Set ws = ThisWorkbook.Sheets("URLList")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize Acrobat application
    Set acroApp = CreateObject("AcroExch.App")
    
    For i = 2 To lastRow ' Assume URLs start at row 2 (header in row 1)
        url = ws.Cells(i, "A").Value
        If url <> "" Then
            ' Define save paths (adjust folders to your preference)
            pdfPath = ThisWorkbook.Path & "\DownloadedPDFs\PDF_" & i & ".pdf"
            textPath = ThisWorkbook.Path & "\ConvertedText\TEXT_" & i & ".txt"
            
            ' Create folders if they don't exist
            CreateFolder ThisWorkbook.Path & "\DownloadedPDFs"
            CreateFolder ThisWorkbook.Path & "\ConvertedText"
            
            ' Download the PDF
            DownloadFile url, pdfPath
            
            ' Convert PDF to text
            Set acroDoc = CreateObject("AcroExch.PDDoc")
            If acroDoc.Open(pdfPath) Then
                If acroDoc.Save(1, textPath) Then ' 1 = Save as plain text
                    ws.Cells(i, "B").Value = "Success"
                Else
                    ws.Cells(i, "B").Value = "Conversion Failed"
                End If
                acroDoc.Close
            Else
                ws.Cells(i, "B").Value = "PDF Open Failed"
            End If
            Set acroDoc = Nothing
        End If
    Next i
    
    acroApp.Exit
    Set acroApp = Nothing
    MsgBox "Batch process finished!", vbInformation
End Sub

' Helper: Download file from URL
Sub DownloadFile(url As String, savePath As String)
    Dim xhr As Object
    Set xhr = CreateObject("MSXML2.XMLHTTP")
    xhr.Open "GET", url, False
    xhr.Send
    
    If xhr.Status = 200 Then
        Dim fso As Object, fileStream As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fileStream = fso.CreateTextFile(savePath, True)
        fileStream.Write xhr.ResponseBody
        fileStream.Close
    Else
        MsgBox "Download failed: " & url & vbCrLf & "Status code: " & xhr.Status
    End If
    Set xhr = Nothing
End Sub

' Helper: Create folder if it doesn't exist
Sub CreateFolder(folderPath As String)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(folderPath) Then fso.CreateFolder folderPath
    Set fso = Nothing
End Sub

Notes:

• Enable the Adobe Acrobat library in VBA Editor: Go to Tools > References and check "Adobe Acrobat xx.x Type Library"
• This requires the full Adobe Acrobat application (not just Reader)

Approach 2: Using pdftotext (Free, No Acrobat Required) #

If you don't have Acrobat, use the free pdftotext tool (from the Xpdf suite). Download it, place the executable in a folder (e.g., C:\Tools\pdftotext.exe), then use this modified script:

Sub BatchPDFDownloadAndConvert_WithPdftotext()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim url As String
    Dim pdfPath As String
    Dim textPath As String
    Dim pdftotextPath As String
    
    ' Update path to your pdftotext executable
    pdftotextPath = "C:\Tools\pdftotext.exe"
    
    Set ws = ThisWorkbook.Sheets("URLList")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        url = ws.Cells(i, "A").Value
        If url <> "" Then
            pdfPath = ThisWorkbook.Path & "\DownloadedPDFs\PDF_" & i & ".pdf"
            textPath = ThisWorkbook.Path & "\ConvertedText\TEXT_" & i & ".txt"
            
            CreateFolder ThisWorkbook.Path & "\DownloadedPDFs"
            CreateFolder ThisWorkbook.Path & "\ConvertedText"
            
            DownloadFile url, pdfPath
            
            ' Run pdftotext via shell command
            Shell pdftotextPath & " """ & pdfPath & """ """ & textPath & """", vbHide
            
            ' Verify conversion success
            Dim fso As Object
            Set fso = CreateObject("Scripting.FileSystemObject")
            ws.Cells(i, "B").Value = IIf(fso.FileExists(textPath), "Success", "Conversion Failed")
            Set fso = Nothing
        End If
    Next i
    
    MsgBox "Batch process finished!", vbInformation
End Sub

' Reuse the DownloadFile and CreateFolder helpers from Approach 1

Problem 1: Word Merging Issue in Power Query PDF Extraction #

Let's troubleshoot why words are merging and how to fix it:

Possible Causes #

• PDF Text Structure: Some PDFs store wrapped text as a continuous string without spacing (visual line breaks don't map to actual text spaces).
• Parsing Engine Version: You're using Implementation="1.3"—different Power Query PDF parsing engines handle non-standard PDFs differently.
• Font/Encoding Quirks: Non-standard fonts or encoding can cause Power Query to misinterpret character spacing.

Fixes to Try #

1. Switch the Pdf.Tables Implementation

Try changing the Implementation parameter to use a different parsing engine:

Source = Pdf.Tables(Web.Contents("https://hpvchemicals.oecd.org/ui/handler.axd?id=621c4f55-ef3c-4b99-bb98-e6aaf3f436dd"), [Implementation="2.0"]),

Or test "1.2"—sometimes older/newer engines handle problematic PDFs better.

2. Post-Process with Regex to Fix Merged Words

If parsing doesn't improve, add a step to split merged words using regex. This targets cases where a lowercase letter is followed by an uppercase letter (common in line-wrap merges):

#"Fixed Merged Words" = Table.TransformColumns(#"Expanded Data", 
    List.Transform(Table.ColumnNames(#"Expanded Data"), 
        each {_, (text) => if text is text then Text.Replace(text, "([a-z])([A-Z])", "$1 $2", [Regex=true]) else text}
    )
)

Tweak the regex if you encounter edge cases (like acronyms), but this is a solid starting point.

3. Use Pdf.Contents for Raw Text Extraction

Instead of Pdf.Tables (designed for tables), use Pdf.Contents to get raw text, which might handle spacing better for non-tabular PDFs:

Source = Pdf.Contents(Web.Contents("https://hpvchemicals.oecd.org/ui/handler.axd?id=621c4f55-ef3c-4b99-bb98-e6aaf3f436dd")),
#"Converted to Text" = Text.FromBinary(Source),
#"Split into Sentences" = Table.FromList(Splitter.SplitTextByDelimiter(". ")(#"Converted to Text"), Splitter.SplitByNothing(), {"Sentences"})

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