Office 365/Microsoft 365 users can now import data from a PDF to Excel using Power Query*.
It’s super handy for importing data tables inside PDF documents because Excel locates them for you ready to import. And if the data is a little messy you can use the Power Query transform tools to clean it before loading it into the Excel file.
*Only available to Office/Microsoft 365 subscribers, find out how to get the latest updates.
Tip: If you don’t have Office/Microsoft 365 then this same functionality is available in Power BI Desktop.
Watch the Video
Download Workbook
Enter your email address below to download the sample workbook.
Import Data from a PDF to Excel Steps
Step 1: Get Data from PDF - It’s as easy as going to the Data tab of the ribbon > Get Data > From File > From PDF:
Step 2: Locate the PDF File you want to import to Excel - Browse to the location the PDF is saved > click Import:
Step 3: Select Tables and Pages in PDF - The Power Query Navigator window opens with a list of pages and tables Excel has identified in the PDF. You can select a table or page to preview in the pane on the right, as shown below with the table on page 7 displayed:
Step 4: Import Data from PDF or Clean - From here you can click the ‘Load’ button to import data directly to the Excel worksheet or to the Data Model. However, you’re most likely to want to do some more cleaning of the data first, in which case click the ‘Transform Data’ button to open the Power Query Editor:
Notice Power Query has correctly identified the column labels and placed them in the header row. If it doesn’t do this automatically, click on the drop down in the top left of the table > Use First Row as Headers:
Not all tables will import as easily. For example, Table2 in this PDF has column headers split over multiple rows. Watch the video to see how to fix them so they’re in a single row and can form the column labels.
When you’re done with tidying up the data, give the query a name in the Properties pane, then go to the Home tab > Close & Load to finalise importing data from the PDF to Excel.
Import Multiple Tables/Pages from PDF
If you have tables that run over multiple pages, for example credit card or bank statements, Power Query will automatically combine then into a single table.
You can also import consecutive pages with the StartPage and EndPage optional parameters like so:
= Pdf.Tables(File.Contents("C:\Sample.pdf"), [StartPage=3, EndPage=5])
More on the Pdf.Tables function here.
Import Multiple PDFs From Folder
If you have multiple PDFs containing data or tables with the same structure, you can use the Power Query Get Data From Folder connector to get them and import the data into a single table.
Import Multiple Tables/Pages from PDF
Dear Minda: Thanks for your great job. Congratulations for all your team!
Is there a way to import several reports from many pages (1, 3, 6, 8, 13, 33 or more pages). I’ve tried From Folder and I got success only for one page. For the bigger one, I’ve tried = Pdf.Tables(File.Contents(“C:\Sample.pdf”), [StartPage=1, EndPage=33]). It’s Ok only for a 33 page. I would like to import everything. A page count solution?
Hi Newton,
Please see this Q&A on our help desk that deals with importing multiple PDFs from a folder containing multiple sheets with Power Query.
Mynda
Hi, How do I extract specific value of each workbook from multiple excel workbooks
You could get all the workbooks using Get data > From Folder, then filter the results until you have the values you want.
The “Data from PDF” submenu only appears in certain limited versions of Excel.
The article never lists them.
We have all the latest updates installed… and we have NEVER seen that “from PDF” menu.
Hi Donna,
Get data from PDF is available if you have a Microsoft 365 license. What 365 license do you have that you’re not seeing it in?
Mynda
Hello,
similar problem – I cannot see this.
I’m running Excel for Mac version 16.52 on a Microsoft 365 Subscription.
(In my Microsoft account it says “Microsoft 365 Family”, for what it’s worth)
Is this a Mac limitation? Or a license limitation, do yo think?
Thank you!
Hi Dominic,
Mac doesn’t support Power Query yet, sorry. It’s in development, but a long way from being able to get data from a PDF.
Mynda
Thanks Mynda.
🙁
Thanks for that info, Mynda. Disappointing, but much better than wasting ages trying to find it!
I’m using Excel 365 ProPlus, and that option to get data from pdf is not available. All updates installed. Not sure why though.
There are different update channels, some slower than others. You might be on a semi-annual channel that hasn’t got the PDF connector yet and because it only updates twice per year, it appears to be up to date. You can find out about the different channels here.
Good post.it was very helpful.
I have searched many site to convert online.
Glad it was helpful, Rohit!
I import a off file into excell but can’t do calculations. Am I missing a step/steps to properly convert it to an excel file. If all works do I save it as an excel file?
Please help
Not sure what you mean, Johan. Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum
same proble for me, i already updated before using this option. please make a video about this, thank you
Maybe you need to update your .Net framework as per this page which clearly says PDF connector is available for 365 users.
i want to import pdf tables to excel and i watched your video but i didt see pdf option under files options, and im using 365 subscribtions. so how to enable pdf option. thank you
my Excel version is 365 and we are active/paid subscribe, but I can’t find “from PDF” choice. what’s the problem?
Hi Gerry, I suspect you need to update Excel to get the From PDF functionality. You can do this via the File tab > Account.
Nice tutorial. But I am facing a problem here. I don’t have a get data option in my toolbox. I don’t know what is the problem. Can you please tell me what is the problem? Thanks in advance.
Hi,
What excel version you have? As mentioned at the beginning of the article, it’s available for Office/Microsoft 365 versions, there is also a link with update details.
Cheers,
Catalin
Well done
Good post. Also showing how to transform headers was very helpful.
Glad you found it helpful, Steve!
awesome….love your website
Thanks so much, Moise!