Import Data from a PDF to Excel

February 21, 2023

Import data from a PDF into Excel using the new Power Query PDF connector. Download the Excel and PDF practice file here:

View my comprehensive courses:

Connect with me on LinkedIn: …(read more)

Convert Word 2 PDF: Word to PDF Converter

Convert PowerPoint 2 PDF: PPT to PDF Converter

Convert Excel 2 PDF: Excel to PDF Converter

Convert an Image 2 PDF: Image to PDF Converter

Convert HTML 2 PDF: HTML to PDF Converter

More Tools: PDF Converter

PDF Converter

| Tags: import pdf to excel, power query

46 thoughts on “Import Data from a PDF to Excel”

If you have Office/Microsoft 365 and don't see the PDF connector then you need to update Office. Find out how to get the latest updates here: https://support.microsoft.com/office/da36192c-58b9-4bc9-8d51-bb6eed468516

So I've set up a Power Query from a PDF, which involved quite a lot of tidying! We have an old system that provides messy PDF outputs for data, and I'd like to add new PDFs and get Power Query to do the same applied steps every time, so I can feed it new PDFs and get clean outputted data as separate tables. Is this possible?

Thanks for the video, why can I not reformat the cells i.e. insert currency symbol and insert formulars to add up columns etc.

You are THE best, Mynda! I can always find what I need with you.

My PDF is a scan of a table and the Power Query just says "no data in table". Thinking the quality of the scan isn't good enough for the software to recognize. Any way around this?

Thanks, very much, I have this issue, I have different pdf files, these have different pages and tables, it may be possible to get the names of the queries so my query does not fail, since the files differ in pages and tables and my query fails

I'm using this method to import data , but when i'm trying to refresh using a diff pdf having similar data , i'm getting errors as Power Query picks up diff tables than i want , kindly help. I liked and subbed by the way , excellent video.

Thanks for the nice presentation, in case the excel navigator is showing pages on left and on Right hand side it shows page is empty? any Advise. Thanks

Why do I get "Table is Empty" error? Is it because the data was scanned to pdf?

It's only available for Office 365 users. Yes. I save your time, you can now go look for different methods.

If you have a pdf that is always in the same format, is there a way to create a template so when you create an import button you don't have to do all the work every time? Is this too much for a macro to do?

Thanks, Mynda! Quick question. Are we able to specify a conditional last page i.e. [StartPage = 3, If(condition, EndPage = 7, EndPage = 8] to cater for variable number of tables.

There is no pdf icon in my excel

Darn, do not have the get data, rom file option??

Can i use in Microsoft Excel 2019? I can't see From PDF in Get Data.

Is there a way to the import data and select all tables automatically? For example, you only have to specify the location with multiple PDFs and the automation does the loading + selecting all tables

Hi,

I just got a recent update Nov 2021 of O365 & sadly it seems that the option for getting data from PDF is no longer available.

Do you know a workaround?

Thank you very much for a clear and concise tutorial. I'm going to try it myself now with a bank statement.

hi, great video! question for you MyOnlineTrainingHub –

I have several tables from PDF, they are basically one long table (22 tables in import) but only the first table has headers.
Excel doesnt recognize or understand that columns from each table are the same so it ends up staggering them over.

so if table 1 ends on column 10, then table 2 starts on column 11, table 3 on column 21, and so on.

how do you make excel recognize or understand that you want it to simply stack all the tables on top of each other and recognize headers from first table you pick?

What version of excel are you using?????

I'm a mailer and sometimes get address lists in pdf. I'm going to try this at work. Probably going to take a few tries but if I can get it to work I'll look like a stud. We usually have to have the client send us a xls or csv to get their addresses imported to our addressing machine.

Hello, I'm not good with Computer so this is a bit hard to understand for me… Let say I have multiple pdf files ( or I can combine multiple pdf files into 1 big file, separate by each page) which have the same format. Now I want to extract some specific data from said files using the "Get data -> From PDF" function. The problem now is I'm having multiple pages extracted and don't know how to apply the same configuration to each page and then combine them into 1 table…
Can someone help me with this problem? Thank you so much in advance.

How can we convert 100 pages pdf. Pls send the email I'll send the document.. I have tried but not happen.

PDF option not show in my excel

Thanks .T
You were too fast

What If you don’t have Excel 365? I have excel 2016. What would be the best solution in this case? I’ve tried several attempts starting from PDF but the results don’t always work.

Hello. I'm wondering if Power Query can be used to import and process larger lists in pdf? F.ex. General Ledger or similar. Sometimes we get longer lists in that format and it would have been very useful to be able to edit them before they are taken over in excel. I have tried but unfortunately without success.

What Excel version is this? There's not Get Data tab on Excel 2016. It only shows From Access, From Web, and From Text.

Please, i have a question. After we import the PDF file via Power bi, how can i select all tables at the same time. when i importe a long PDF file, i get a hundreds tables that i cannot select one by one. that may takes a life time. thank you.

Will this work with the on line version of excell?

I don't have pdf option!!! How do I get it there?

I am using Excel 2019 application in windows 10 I don't have option to get data from pdf . Can you please tell me how can I get that option?

Would be able to convert multiple pdf to one Excel sheet

the Get Data option is not there in my excel-2016.

Another excellent video, nice voice and volume yo explain Power Query, muy claro con tus vídeos Mynda, congratulations! I am learning good!

My office version is office 365 proplus.but I don't have this option under get and transfrom..could you help me solve this..? Or why was it..

I have installed Office365 but import PDF option it doesn't show kindly help me in this regard,thanks

i was wondering, where can i get one of those green excel notebooks?

hi there… great video… But is the option of get data rom pdf available only in O365 or is it possible in older versions too.. pls advice how to do it… thanks