Convert PDF to Excel | Convert Multi-Page PDF to Excel | Import Multiple PDFs Tables into Excel

In this video I demonstrate how to import a table of data into Excel from a pdf file.

Often the data you want to import from a pdf is in a table. If you have ever tried copying and pasting a table directly from a pdf into Excel, you will know it doesn't work. If, however, you first paste into Microsoft Word and then into Excel, it does work. Microsoft Word also allows you to convert a pdf into a Word document, from where you can also copy and paste.

If you are regularly importing from pdfs and you want new pdfs or updated pdfs to automatically update your spreadsheet, then you should be using Power Query. This video demonstrates how to convert a pdf containing a multi-page table as well as importing multiple pdfs within a folder.

Table of Contents:

00:00 - Intro
00:59 - Copy Paste into Microsoft Word and then into Excel
01:44 - Convert PDF to Word and then Copy Paste into Excel
02:47 - Power Query: Import One of Many Tables in a PDF into Excel
04:08 - Power Query: Import Table that Spans Multiple Pages in a PDF into Excel
06:56 - Power Query: Update spreadsheet when PDF changes
07:58 - Power Query: Import Multiple PDFs in the Same Folder into Excel
10:39 - Power Query: Update spreadsheet when new PDFs added to folder
------------------------

[mubafaw]

Brilliant! Saved me time not having to spin my wheels for hours.

[mattsimon]

My current issue is a file a vendor is sending splits each page into its own table, so I have a file with 79 tables, and Power query wants to put each on on its own worksheet in excel. I just want it all in 1 single table. Haven't figured that out yet.

[wayneedmondson]

Great Chester! Super useful PDF conversion tips and tricks. Thanks for sharing :)) Thumbs up!!

[nssdesigns]

Spent a big portion of the weekend learning Power Automate Desktop Flows and RegEx to try and scrape data from PDF's. The solution is this! If only I found this 24h ago :-)

[IvanCortinas_ES]

Superb explanation. Thank you for sharing this valuable topic.

[countduckula]

Thanks Chester, this video has been really useful.

[joejoe]

When you want to load multiples pdf files and each PDF has mutiples tables, it does not work. Power Query seem to load only 1 table from each PDF

[ibrahimnajjar]

Very useful, much appreciated Chester!

[sallyho]

Thank you. I loaded table 1 and table 2. How do I remove header/titles row from table 2 so that the header doesn’t repeat when both tables are combined in one tab? There is no delete row option after the tables are uploaded. I can only hide or group rows

[oleksijm]

Nice. Could you do a tutorial where a PowerQuery extracts the entirety of a PDF of varying page length into Excel dynamically? In other words, without specifying each time how many pages the file has. It's a nice functionality to embed into a VBA subroutine.

[nareshduggal]

This is great. I have a quick question, what about if the table is located on different pages of the PDF?

[jennifermioni]

Love your tutorials! Question though... my pdf files have multiple tables over about 3 pages and it doesnt allow to select multiple... how do you get all of them to import into power query?

[countduckula]

Hi Chester,

Sad news a recent Windows update has removed the from PDF option in Get data.
This has affected Excels on 2019 & O365.
😢

Do you know if there is a way around this?

[andrewlitkie]

Has the from folder moved? I don't see it as an option under get data

[sallyho]

Also after uploaded the tables, the numbers are not adding up. Please advise why

[kidsstories]

How to convert pdf histogram graph into excel in table form?