SQL Server Requires Chilkat v11.7.0+

Extract Tables from a PDF

See more PDF Text Extraction Examples

Demonstrates the "tables" operation, which returns the tables detected on the pages as rows and cells. Each cell has its row and column (counted from 0), its row and column span, and its text. SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @iTmp1 int
    DECLARE @iTmp2 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    --  The PDF used in this example is available at price_list.pdf

    DECLARE @pdf int
    EXEC @hr = sp_OACreate 'Chilkat.Pdf', @pdf OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @pdf, 'LoadFile', @success OUT, 'qa_data/pdf/price_list.pdf'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        RETURN
      END

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @jsonOut int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonOut OUT

    --  The "tables" operation returns the tables found on the selected pages.
    --  Tables are recognized by their drawn cell borders.
    EXEC sp_OAMethod @pdf, 'ExtractText', @success OUT, 'tables', @json, @jsonOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @pdf, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @pdf
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @jsonOut
        RETURN
      END


    EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'count'
    PRINT 'Number of tables: ' + @iTmp0

    --  Used to build each row of output.
    DECLARE @sbRow int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRow OUT

    DECLARE @currentRow int

    DECLARE @row int

    DECLARE @j int

    DECLARE @count_j int

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @count_i OUT, 'tables'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonOut, 'I', @i

        EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp0 OUT, 'tables[i].page'

        EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp1 OUT, 'tables[i].rows'

        EXEC sp_OAMethod @jsonOut, 'IntOf', @iTmp2 OUT, 'tables[i].columns'

        EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'tables[i].detectedBy'
        PRINT 'Table on page ' + @iTmp0 + ': ' + @iTmp1 + ' rows x ' + @iTmp2 + ' columns, detected by ' + @sTmp0

        --  The cells are listed row by row.  Print each row as its cells separated by " | ".
        SELECT @currentRow = -1
        EXEC sp_OAMethod @sbRow, 'SetString', @success OUT, ''
        SELECT @j = 0
        EXEC sp_OAMethod @jsonOut, 'SizeOfArray', @count_j OUT, 'tables[i].cells'
        WHILE @j < @count_j
          BEGIN
            EXEC sp_OASetProperty @jsonOut, 'J', @j
            EXEC sp_OAMethod @jsonOut, 'IntOf', @row OUT, 'tables[i].cells[j].row'
            IF @row <> @currentRow
              BEGIN
                IF @currentRow >= 0
                  BEGIN
                    EXEC sp_OAMethod @sbRow, 'GetAsString', @sTmp0 OUT
                    PRINT @sTmp0
                  END
                SELECT @currentRow = @row
                EXEC sp_OAMethod @sbRow, 'SetString', @success OUT, '  '
              END
            ELSE
              BEGIN
                EXEC sp_OAMethod @sbRow, 'Append', @success OUT, ' | '
              END
            EXEC sp_OAMethod @jsonOut, 'StringOf', @sTmp0 OUT, 'tables[i].cells[j].text'
            EXEC sp_OAMethod @sbRow, 'Append', @success OUT, @sTmp0
            SELECT @j = @j + 1
          END
        EXEC sp_OAMethod @sbRow, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    --  The cells of the first table as JSON:
    EXEC sp_OASetProperty @jsonOut, 'EmitCompact', 0
    EXEC sp_OAMethod @jsonOut, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    --  Sample output:

    --  Number of tables: 1
    --  Table on page 1: 8 rows x 5 columns, detected by ruled
    --    Code | Product | Unit | Price | In Stock
    --    HT-101 | Claw hammer, 16 oz | each | $18.00 | 24
    --    HT-102 | Adjustable wrench, 10 in | each | $14.50 | 31
    --    HT-103 | Screwdriver set, 6 piece | set | $22.00 | 12
    --    HT-104 | Tape measure, 25 ft | each | $9.75 | 40
    --    HT-105 | Utility knife | each | $6.20 | 58
    --    HT-106 | Pliers, needle nose | each | $11.00 | 19
    --    HT-107 | Hand saw, 20 in | each | $21.40 | 8
    --  {
    --    "count": 1,
    --    "tables": [
    --      {
    --        "page": 1,
    --        "detectedBy": "ruled",
    --        "rows": 8,
    --        "columns": 5,
    --        "cells": [
    --          {
    --            "row": 0,
    --            "col": 0,
    --            "rowSpan": 1,
    --            "colSpan": 1,
    --            "text": "Code"
    --          },
    --          {
    --            "row": 0,
    --            "col": 1,
    --            "rowSpan": 1,
    --            "colSpan": 1,
    --            "text": "Product"
    --          },
    --          ...
    --        ]
    --      }
    --    ],
    --    "quality": {
    --      "rating": "good",
    --      ...
    --    }
    --  }

    EXEC @hr = sp_OADestroy @pdf
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonOut
    EXEC @hr = sp_OADestroy @sbRow


END
GO