In this tutorial learn how to,
- Extract tabular data from one PDF to Excel
- Combine and extract tables from multiple PDFS to Excel
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).
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.
💡 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.
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.
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.
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.
7 Responses to “Extract data from PDF to Excel – Step by Step Tutorial”
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
Hello sir,
How to check two names are present in the same column ?
Thanks and Regards
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
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?
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
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
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.