Extract Table Data from PDF files in Power Automate

Inventory SharePoint List

The data source we will be populating is an ‘Inventory’ SharePoint list. The list has been configured to capture all the inventory details for each vendor.

For this example, the table is flat, so no lookups to other tables are used (or required).

Power Automate

The Power Automate flow is triggered when an email is received which contains an attachment and has the word ‘Inventory’ in the subject line. I have also specified the sender’s email addresses to filter the trigger further. Each vendor’s email address can be added here (I am using only one for this demo).

Power Automate doesn’t just capture email attachments; it will also capture any images used in the email body or signature. We must filter out the other file formats to only deal with the attached PDF document.

Firstly, we need to obtain the content of the email attachment. We can use the ‘Get Attachment (V2)’ action. The ‘Message Id’ and ‘Attachment Id’ are outputs from the trigger action. When you add the attachment ‘Id’ dynamic content, the action will be placed inside an ‘Apply to each’ loop. This is because there can be multiple attachments identified in an email, each with its own attachment ‘ID’, so we need to loop through each one.

Once we have the attachment content, we also have the file name and its extension. We can use this to determine whether the attachment is a PDF. If the attachment is a PDF, we can continue with the rest of the flow; if it isn’t a PDF, there will be no further actions, and the loop will move on to the next identified attachment.

Once we have the attachment content, we can use the Encodian Flowr action ‘PDF – Extract Table Data‘. With this action, you can specify particular pages from which to extract table data and set whether the table has a header row.

The results from the Flowr action will look like this:

[{"Product ID":"P1234 ","Product Name":"Wireless Mouse ","Quantity":"50 ","Unit Price":"15.99 ","Vendor":"TechCorp "},{"Product ID":"P5678 ","Product Name":"USB Keyboard ","Quantity":"30 ","Unit Price":"25.49 ","Vendor":"TechCorp "},{"Product ID":"P9101 ","Product Name":"HDMI Cable ","Quantity":"100 ","Unit Price":"8.99 ","Vendor":"TechCorp "}]

The result is JSON data contained within a string, which we need to parse so that we can access the values more easily. We can do this using the ‘Parse JSON’ action. The JSON schema can be generated simply by copying and pasting the string result from the Flowr action. The generated schema will look something like this:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Product ID": {
                "type": "string"
            },
            "Product Name": {
                "type": "string"
            },
            "Quantity": {
                "type": "string"
            },
            "Vendor": {
                "type": "string"
            },
            "Unit Price": {
                "type": "string"
            }
        },
        "required": [
            "Product ID",
            "Product Name",
            "Quantity",
            "Vendor"
        ]
    }
}

Now our data is in JSON format, we can loop through the body to add the data to SharePoint row by row. As mentioned, all the data in our JSON will be in string format. This means when we are adding numbers to SharePoint, we need to convert the string to an integer for the quantity value and to a float for the unit price value. We can achieve this using the following expressions:

int(items('Apply_to_each')?['Quantity'])

float(items('Apply_to_each')?['Unit Price'])

Results

This is the email head office has received from their GearInc store:

This is what the inventory PDF looks like for GearInc:

Once the email is received, the Power Automate automatically runs because the filter conditions are met (having ‘Inventory’ in the store line and an attachment in  the email). This is what the results look like in the SharePoint list:

As we can see, the inventory lines have been added correctly.

We hope you enjoy easy automation with one of our newest actions: PDF – Extract Table Data

Video

Check out Sophie’s companion video on YouTube!

What else can Flowr automate?