PDF

Introduction

The Portable Document Format connector transforms static documents into dynamic, queryable data. It allows users to extract structured information from complex PDF files—such as reports, invoices, statements, or tables—using standard SQL, without manual data entry, copy-paste, or custom parsing scripts.

At its core, the connector treats each PDF as a virtual table. It uses configurable extraction algorithms to identify tabular regions on the page and infer rows and columns, even across multiple pages. When you query a PDF, Metaform reads the document’s text layout, reconstructs its logical table structure, and exposes each cell as a field in a structured dataset. With this approach, you can join PDFs to spreadsheets, databases, or JSON files just as you would any other source.

This connector is built for flexibility. For cleanly formatted reports, automatic detection may be enough. But for more complex documents—where headers span pages, columns shift, or numbers include formatting characters—you can fine-tune extraction using advanced options that control how pages are combined, how headers are interpreted, and which extraction algorithm (spreadsheet or stream) is applied.

In short, the PDF connector bridges the world of static documents and live data analysis—turning any table trapped in a PDF into something you can query, aggregate, and reason about.

Scenario

Imagine receiving state-by-state donation reports as multi-page PDF exports from an external system every month. At first glance, these reports look simple enough—rows of donors with addresses, cities, states, and contribution amounts. But once you open them, the challenges become obvious: formatting shifts from page to page, headers repeat inconsistently, numeric formats vary, and even city names appear with multiple spellings.

What you really need is a way to analyze all regions together—aggregate donations by state and city, rank markets by performance, and identify where contributions are strongest. Doing that manually, though—copying rows into spreadsheets, cleaning each page, and reconciling totals—is slow and error-prone.

The table below is a representative excerpt from one of these monthly reports. It reflects exactly how the external system exports its data. The PDFs you receive contain many pages of rows formatted just like this sample—structured, but locked inside a document format that wasn’t designed for analysis.

This excerpt illustrates the core problem: the data is structured but trapped. It exists in rows and columns, but only visually. Until now, turning this into something you can query required manual labor.

Putting It Together

With Metaform, the friction described in the scenario simply disappears.

What once required hours of copy-and-paste cleanup becomes a single, repeatable SQL query you can run in seconds. Instead of wrestling with PDFs, you can immediately see which cities and states are contributing the most—and repeat the analysis every month without ever touching the source files again.

Once Metaform’s PDF connector is configured, each document is treated as a collection of structured records: rows and columns embedded inside a static PDF layout. The connector’s extraction parameters tell Metaform how to detect tables, interpret headers, normalize values, and produce a clean, typed schema that behaves exactly like a SQL table.

To convert this visually structured—but operationally unstructured—data into a queryable dataset, you can define an aggregation query like the following:

SELECT 
  State,
  City,
  SUM(
    CAST(
      REPLACE(REPLACE(Amount, '$', ''), ',', '') AS DOUBLE
    )
  ) AS `Total Amount`
FROM 
  table(
    pdf.`data.pdf` (
      type => 'pdf',
      combinePages => true,
      extractHeaders => true,
      extractionAlgorithm => 'spreadsheet'
    )
  )
WHERE 
  State IS NOT NULL 
  AND TRIM(State) <> ''
GROUP BY 
  State,
  City
ORDER BY 
  State ASC,
  `Total Amount` DESC;

Metaform then returns a clean, structured result—ready for dashboards, downstream analysis, or further enrichment:

By treating PDFs as first-class, queryable datasets, Metaform turns document-driven reporting into a reliable, automated analytics workflow—no manual cleanup, no brittle exports, and no loss of fidelity across months or markets.

How It Works

Within the SQL query, each option in the table function tells Metaform how to interpret the structure of the PDF and transform it into a clean, queryable dataset:

Inside the SQL query:

• The nested REPLACE() calls strip currency symbols and thousands separators from the Amount column, preparing the values for numerical operations.
• The CAST(... AS DOUBLE) ensures that those cleaned values are typed as numeric data, making them eligible for aggregation.
• The GROUP BY and ORDER BY clauses summarize totals by location—grouping at the state and city level, then ordering states alphabetically and ranking cities by contribution amount.

Together, these components allow Metaform to transform a static, multi-page PDF into a fully typed relational dataset—ready for analysis with the same SQL you use everywhere else.

Working Example

To explore how Metaform handles real-world PDF reports—like the multi-page donation files in the scenario—walk through the steps below in order.

Each step introduces a key part of the workflow.

# Download the connector JSON and pipe it directly into Metaform's connector API
curl -sSL https://docs.metaform.com/resources/examples/pdf-connector.json \
  | curl -X POST -H "Content-Type: application/json" -d @- http://localhost:8047/storage/pdf.json

# Download the connector JSON and pipe it directly into Metaform's connector API
curl -sSL https://docs.metaform.com/resources/examples/pdf-connector.json \
  | curl -X POST -H "Content-Type: application/json" -d @- http://localhost:8047/storage/pdf.json

# Download the connector JSON into a string
$json = Invoke-WebRequest `
  -Uri "https://docs.metaform.com/resources/examples/pdf-connector.json" `
  -UseBasicParsing |
  Select-Object -ExpandProperty Content

# Upload the JSON to Metaform's connector API
Invoke-RestMethod `
  -Uri "http://localhost:8047/storage/pdf.json" `
  -Method Post `
  -ContentType "application/json" `
  -Body $json

https://docs.metaform.com/resources/examples/data.pdf

SELECT
  State,
  City,
  SUM(
    CAST(
      REPLACE(REPLACE(Amount, '$', ''), ',', '') AS DOUBLE
    )
  ) AS `Total Amount`
FROM
  table(
    pdf.`data.pdf` (
      type => 'pdf',
      combinePages => true,
      extractHeaders => true,
      extractionAlgorithm => 'spreadsheet'
    )
  )
WHERE
  State IS NOT NULL
  AND TRIM(State) <> ''
GROUP BY
  State,
  City
ORDER BY
  State ASC,
  `Total Amount` DESC;

Advanced Configuration

You’ll often want greater control—such as defining how pages are combined, how table headers are interpreted, and which extraction algorithm best suits your document layout.

All of these advanced options are set at query time, allowing you to fine-tune how Metaform reads and structures PDF data without altering the source file itself.

The table below summarizes the complete set of available configuration parameters: