fbpx
Search
Close this search box.

Extract data from PDF to Excel – Step by Step Tutorial

Share

Facebook
Twitter
LinkedIn
get data from pdf to excel

In this tutorial learn how to,

  • Extract tabular data from one PDF to Excel
  • Combine and extract tables from multiple PDFS to Excel
 
We will be using Excel 365 & Power Query to do this. If you have different version of Excel (2016, 2013 or older), read the FAQ section at the end for another way to do this.

How to extract PDF table to Excel

Optional:  If you need a sample PDF to practice these concepts, use the randomly made credit card statements I created. Download them from here.

Step 1: Go to Data ribbon & click on Get Data > File > PDF

From data ribbon, use the PDF option  and point to the location on your computer (or web address).

data from PDF option - power query get data excel

Step 2: Select the table(s) you want in the navigator screen

Power Query will open up a navaigator screen. Just specify the table(s) you want. Refer to below illustration to know more about the navigator screen.

navigator screen for pdf - power query

💡 Bonus tip: Use the composite table if you want to get a data table in your PDF that spans multiple pages. This is excellent for bank or credit card statements.

Step 3: Load or Transform data

If the preview in navigator looks satisfactory, just load it. Otherwise, click on “Transform data” to open query editor to make any final adjustments.

Combine & Extract data from multiple PDFs

Step 0: Place all your PDFs in a folder

Step 1: Folder connection

Instead of PDF option, use the Folder option in the Get Data.

from folder option - get data - power query - excel

 

Step 2: Choose “Combine” in file listing screen

Power Query will show you a screen with a list of all files it found in the folder. Choose any of the combine options here to combine the data from all files to one table.

File listing screen - Power Query - Folder connection option

Step 3: Select the table you want from Transfer Sample Screen

Now, you will see another navigator like screen. Just select the table you want in here. Power Query will go to each file in the folder, get the same table and combine them.

Step 4: Load or Edit the query

And enjoy.

Practice PDF Credit Card Statements

If you need a sample PDF to practice these concepts, use the randomly made credit card statements I created. Download them from here.

Video - Convert PDF to Excel

Still not sure how to extract data tables from PDF to Excel? Watch this short video and get it. See it below or on my YouTube channel.

PDF to Excel - FAQs

I don’t have PDF option in my Excel. What do I do?

You can use free Power BI Desktop to do the same. (Download Power BI for free here)

Once you have Power BI, open it, go to Get Data > PDF and follow the same steps as above tutorial.

Instead of loading the data, copy the entire table from Query Editor and paste it to Excel. See below illustration.

copy entire table - power query in Power BI

I have new files, how do I refresh?

Just place the files in the same folder.

Go to Excel and right click on the extracted table and select “Refresh”. Excel will update the details.

I want to exclude certain files in the folder when combining…

Open the query editor and go to the query that is responsible for your combining PDF process. Go to source step. This will show all the files in the folder. 

Include a filter condition here. Power Query will warn about inserting a step. Proceed and you will be able to exclude files based on conditions.

Examples:

  • Process files that have file name starting with certain letters
  • Files created after certain date
  • Having specific extension.

Remember: Power Query is case sensitive.  

I want to pre-process or clean-up data before loading it into Excel

Open the query editor and add any necessary data transformation steps at the end. 

Examples:

  • Removing all foreign currency transactions from credit card statements
  • Cleaning up account codes
  • Rearranging columns in the PDF data table

For more on what you can do with Power Query, check out this tutorial.

Other questions…

Post a comment and I will try to help you.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

7 Responses to “Extract data from PDF to Excel – Step by Step Tutorial”

  1. Jinesh Vasa says:

    Dear Chandoo,

    Thank you very much for this and it is very helpful.
    However, all the Credit Card Statements are now password protected.
    Please advise how can we have a workaround for that

  2. Sivakumar H says:

    Hello sir,
    How to check two names are present in the same column ?
    Thanks and Regards

  3. Ahmed Mallook says:

    Hi, Thank you for the great tip. One problem, when I click on get data >> from file, I don't see the PDF source option. How can I add it?
    I tried to add it from Quick Access toolbar >>> Data Tab, but again the PDF option is not listed there.
    I am using Office 365

  4. PP says:

    Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. Your video would work except that I can't get the composite table and each of the tables I do get or the pages does not have all the info. what to do?

  5. Jr. H says:

    Dear Chandoo,
    How do we select multiple amount of tables/pages in one PDF and repeat the same for rest of the PDF;s in the same folder and then extract that data only on power query.

    Thank you

  6. antonlagi says:

    Hi, Thank you for your video. I see you used the composite table, but I when I load my pdf, it does not load any composite table. It has 20 tables and 4 pages for one bank statement. I have about 30 bank statements that I want to combine. nice share

  7. One bank statement takes up 20 tables and four pages in this document. I need to consolidate roughly thirty different bank statements that I have. Your video would be useful if I could only get the composite table, which I can't for some reason, and each of the tables or pages that I can get is missing some information.

Leave a Reply