In this step-by-step tutorial, learn how to bulk extract data from multiple PDF files into an Excel spreadsheet. To extract data, we use Power Query functionality that is built into Microsoft Excel and this requires no third party add-ins.
👋 Additional resources:
– Sample PDF files if you want to follow along:
– Learn the fundamentals of Excel in just 2 hours:
⌚ Timestamps
0:00 Introduction
0:32 Overview of sample files
1:51 Import one PDF into Excel
4:07 Refresh data
6:31 Bulk import PDF files into Excel
10:03 Power Query Editor
11:30 Transform data
12:15 Transpose
12:49 Use first row as headers
13:05 Add custom column
16:08 Remove row
16:41 Update column header
17:01 Applied steps
17:49 Review & transform aggregate data
19:07 Update data type
19:58 Remove column
20:14 Close & load
20:44 Bring in more data
21:56 Wrap up
📃 Watch related playlists and videos
– Playlist with all my videos on Excel:
– Playlist with all my videos on PDF:
🚩 Connect with me on social:
– LinkedIn:
– Twitter:
– Facebook:
– TikTok:
🔔 Subscribe to my YouTube channel
🎬 Want to watch again? Navigate back to my YouTube channel quickly
🛍 Support me with your Amazon purchases:
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel — the price to you is the same.
#stratvert…(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
in my excel there is no pdf file choice.. please help me.. how to add pdf file choice
Has get and transform gone on the newest 365? i can't find this in my excel
HI Kevin thanks so much! Is there an easy way to work with this method for bank statements? Thanks
Hi Kevin. Using your example, can I rearrange the columns without affecting how Excel reads the PDF file data? This is a great video by the way. Thanks
Hi Thank you for this amazing tutorial. I have one more question I would like to integrate SAGE INTACCT and Dynamics to exchange data on customer through the webservices account. Or any other way as long the two platform will communicate.
I don't have an option to get data from PDF on my Excel. Is it because I'm using Home and Student 2016 possibly?
I've tried to import PDF data with Excel. something is not working, when I select Data, there is no section to import data…what's missing? Very frustrating to say the least!
Can I do this with google sheets?
good
Better to have one web form connected to your Excel instead
Pdf option is not showing in Get Data
This was amazing TY! The pdf I'm working with has paragraph sections for example 1.1 text, 1.2 text, 1.3 text etc that I need to put into a table. This is going to be labor intensive. Your video is brilliant!
I need some help with this. I tried it on my own PDF documents, but get an error when I try to convert the number, shown as text. The rror message is DataFormat.Error: We couldn't convert to Number.
It doesn't work for financial or bank statements and I tried but Only saw Nonsense letters and Characters so it doesn't work. Could you do a video particularly addressing how to do the same thing for bank statements? I have struggled with this for years now and the only method I know which works most of the time is to used third party services says or apps and that can be A little disconcerting to upload her financial statements into another website so if we could get A faster and more convenient method that would be very helpful.
Fantastic explanation! Thanks a bunch!
Hi Keven, fantastic and very help full, is there a way to do this with multiple Tables in a PDF I have around 100 PDFs with up to 400 tables
Great video. Thank You Kevin. I have a question: How can I convert only selected tables (identical structures) from a single PDF document with multiple tables?
I`ve just seen magic
Thank you…I've learned alot.✌
i dont have a option of a from pdf > from file option but from csv , excel ,text
How do we convert pdf data in table format in multiple columns of say 200 pages to excel sheet?
Issue: when I add new responses it changes all the formatting I did on the Excel side. For example I expanded some columns to get it to where the text on the header was wrapped. That is gone when I hit refresh. What am I doing wrong? I am uploading from a CSV file instead of a PDF (it started as a PDF).
Fantastic! Thank you so much!
There should be a love button. I have been looking for two days for a video to explain this. The old Adobe system looked much easier than this newer one. Then I went to flow or whatever it is called now but didn't want to spend money on a system I didn't know would work and then the 3rd party apps started coming into play. UGH. This bypassed all of that. Thank you!
It did work! Thank you for the video!!! Amazing!!
Excellent tutorial ! What if there’s a line that’s added or extracted in every file ?
Thanks a bunch. You are a great teacher. Take respect.
Idnt hv from file otion in 2019 excel. How i get plz guide
There is no mention of which version of Excel you need to do this as the version I am using doe not have that import option…
My excel doesn't have the option of important from pdf
how easy is it to transfer back to the original PDF to forward to a client?