Converting a PDF to Excel Using Python

$ python3 -m venv env
$ source env/bin/activate

Then, run the following command to install the requests library in Python:

$ pip install requests

{
  "fields": [
    {
      "id": "newly_reported_covid_19_cases",
      "anchor": "Newly reported and cumulative COVID-19 confirmed cases and deaths",
      "type": "table",
      "method": {
        "id": "table",
        "columns": [
          {
            "id": "WHO Region",
            "terms": [
              "WHO Region"
            ],
            "isRequired": true
          },
          {
            "id": "New cases in 28 days",
            "terms": [
              "New cases in",
              "last 28 days"
            ],
            "stopTerms": [
              "change",
              "death"
            ],
            "isRequired": true,
            "type": {
              "id": "custom",
              "pattern": "^[0-9 ]+",
              "type": "number"
            }
          },
          {
            "id": "Change in new cases in 28 days (%)",
            "terms": [
              "Change in",
              "new cases in",
              "last 28 days *"
            ],
            "stopTerms": [
              "death"
            ],
            "isRequired": true
          },
          {
            "id": "Cumulative cases",
            "terms": [
              "Cumulative cases"
            ],
            "isRequired": true,
            "type": {
              "id": "custom",
              "pattern": "^[0-9 ]+",
              "type": "number"
            }
          },
          {
            "id": "New deaths in 28 days",
            "terms": [
              "new deaths",
              "in last 28",
              "days (%)"
            ],
            "stopTerms": [
              "change"
            ],
            "isRequired": true,
            "type": {
              "id": "custom",
              "pattern": "^[0-9 ]+",
              "type": "number"
            }
          },
          {
            "id": "Change in new deaths in 28 days (%)",
            "terms": [
              "Change in",
              "new deaths",
              "in last 28"
            ],
            "isRequired": true
          },
          {
            "id": "Cumulative deaths",
            "terms": [
              "Cumulative deaths",
            ],
            "isRequired": true,
            "type": {
              "id": "custom",
              "pattern": "^[0-9 ]+",
              "type": "number"
            }
          }
        ],
        "stop": {
          "type": "startsWith",
          "text": "*Percent change in the"
        }
      }
    }
  ]
}

This configuration file is a nested JSON object. The first level defines a "fields" key that takes a list of JSON objects. The objects under the "fields" key define the section of the text to focus on. In this case, the code is extracting the content of a table. To help Sensible locate the table of interest, the configuration assigns the "anchor" key to a string that contains relevant text in the table's title and defines the field type as "table".

Next, the configuration defines a "methods" key. The value of this field is another object that contains an "id" and a "columns" key. The "columns" key defines all the relevant columns of interest in the table. For every column, the code defines an identifier using "id" and sets the "isRequired" key to true. It also defines the "terms" key as a list of terms that are present in the column name to allow Sensible to locate them more easily. Additionally, it defines "stopTerms" for some columns to prevent Sensible from associating your desired column with other columns that contain those terms.

The configuration also adds a "type" key for columns that contain numeric values without spaces to identify them as numbers. In other cases, a custom type is used to extract only the number from the text using regular expression. Finally, it defines a "stop" key to let Sensible know where the table ends. The extracted result is displayed on the right pane, as shown below:

import requests

document_type = "covid_reports"
environment = "development"
document_name = "pdfs/20230301_Weekly_Epi_Update_132.pdf"

Store the Sensible API key you saved from the Sensible dashboard in a variable in your script, as shown below:

SENSIBLE_API_KEY = "INSERT YOUR API KEY HERE"

Next, in the script, add the following lines of code to define a function for extracting content from PDF files using the configuration you published:

def extract_content(d_type: str, d_name: str, env: str) -> dict:
    url = "https://api.sensible.so/v0/extract/{}?environment={}".format(d_type, env)

    headers = {
        "Authorization": 'Bearer {}'.format(SENSIBLE_API_KEY),
        "content-type": "application/pdf"
    }

    with open(d_name, 'rb') as fp:
        pdf_file = fp.read()
        response = requests.post(url, headers=headers, data=pdf_file)

        print("Extraction Status code: {}".format(response.status_code))
        if response.status_code == 200:
            return response.json()
        else:
            print(response.json())

The code block above defines a function called extract_content. This function takes three parameters—document type, document name, and API environment—and returns the extracted data as a dictionary. It first specifies the URL endpoint to which you will make a request. It then defines the request header as a dictionary containing the authorization details and the content type. The function then opens the PDF file to be scraped and passes the PDF content and the defined URL endpoint and header to the requests.post method. Finally, the function prints the status code and returns the content of the response.

Next, define a function that will convert the extracted content to an Excel file. Sensible supports generating Excel files natively, so to do this, add the code below to the script:

def convert_to_excel(ids: str) -> str:
    url = "https://api.sensible.so/v0/generate_excel/{}".format(ids)

    headers = {
        "accept": "application/json",
        "Authorization": 'Bearer {}'.format(SENSIBLE_API_KEY)
    }

    response = requests.get(url, headers=headers)

    print("Conversion Status code: {}".format(response.status_code))
    if response.status_code == 200:
        data = response.json()
        return data["url"]
    else:
        print(response.json())

This code block defines the convert_to_excel method. This method takes the document ID and returns a link to a URL for downloading the Excel file. The function first defines the URL for the API's generate_excel endpoint. In this case, the document ID is attached to the URL. Then, as before, it defines the request header with the content type and authorization details. Next, the function makes a GET request by passing the URL and the header to the requests.get method. If the response status code is 200 (OK), it returns the URL to download the Excel file. This URL is only available for fifteen minutes.

With the download URL obtained, enter the following lines of code into the script to define a function to download the Excel file to your project directory:

def download_xlsx(url: str, d_name: str) -> None:
    response = requests.get(url)
    filename = "{}.xlsx".format(d_name)
    with open(filename, "wb") as fp:
        fp.write(response.content)

    print("{} downloaded.".format(filename))

This code defines a function to download the file at a given URL. It makes a GET request with the URL and then saves the results to an Excel file.

With these functions defined, you can now extract content from a PDF file and save it as an Excel file.

To test this, download the COVID-19 situation report for March 1, 2023, create a subdirectory named pdfs in your project directory, and save the report there.

Then, enter the code below to run all of the functions you've created on the newly downloaded PDF file:

pdf_content = extract_content(document_type, document_name, environment)

url = convert_to_excel(pdf_content["id"])

if url is not None:
    download_xlsx(url, document_name[:-4])

This code block defines three variables for the document type, environment, and document name. These variables are passed to the extract_content function, which returns the relevant content. The ID of this content is then passed to the convert_to_excel function, which returns the download URL. This URL is then passed to the download_xlsx function, which downloads the Excel file to your project directory.

When you open the file, you should find content similar to the snippet below: