How to extract data from PDF email attachments into excel


Automation overview

Nowadays you can be inundated with PDF reports and analysis from various sources and after a quick glance they are never viewed again. Here I show you a way of extracting and saving that data from a PDF you receive in an email into Excel.

Automation flow for extracting data from PDFs using Make and inputting into Excel

Data extraction PDF flow

How to recreate this automation

Resources

Step 1: Setup an Outlook rule to move required emails to a folder

Setting this up simplifies the process within Make - as it means we won't need to do any tricky searches on our whole inbox. For this demo I created a folder called Automation and moved any messages from my personal email address containing the subject Channel Stats into it.

Outlook Rule

Step 2: Create a Make scenario that extracts email attachments

This is a 3 step process in itself - first of all you find the emails via a search, then you list out the attachments from that email and then finally you download the attachment.

Three steps to getting the attachment

In the Search Messages node we want to grab emails from Today that are in the Automation folder we set up in Step 1.

Search Messages

In the List Attachments node we want to use the Message ID from the search result in the Search Messages node.

List Attachments Node

In the Download an Attachment node we want to use the Message ID from the search result in the Search Messages node and the Attachment ID from the List Attachments node.

Download an Attachment Node

Step 3: Extract data using PDF.co

You need to create an account on PDF.co and create a document parser. In this document parser you are able to set up objects that will extract text/data from your PDF. Here you can see how it was set it up for the demo (for more detailed information on how to set this up watch the video above).

PDF.co Document Parser

After you save take note of the ID of the Document Parser Template.

Document Parser Template ID

Add your PDF.co node in Make after the Download an Attachment node.

Add your PDF.co node

Set your PDF.co node up so that its Input File is Upload a File and the File is from the previous node. Then add the ID of your Document Parser Template that you set up earlier. PDF.co node setup

Step 4: Write out the data to Excel

The final step is to write the data you have extracted from your PDF to your Excel spreadsheet. Add Microsoft 365 Excel Add a Worksheet Row node as the final node. Microsoft 365 Excel node

Setup your node so that it uses the values from PDF.co to write to your spreadsheet (you need to have created your spreadsheet already). The values you set up in your Document Parser Template will be available in this node to write to your spreadsheet.

Select your Workbook and then your Worksheet and the rows you have setup in your spreadsheet will become available.

Excel node setup

If you enjoyed this we would ❤️ you to retweet or like this tweet