Join 300,000+ professionals in our courses and master Power Query:
Easily combine multiple PDF files with one click and get them properly formatted in Excel.
Don’t waste your time copying and pasting because copying and pasting a pdf into Excel doesn’t work. Everything ends up in a single column and you will have to manually correct each field. In this video I’ll show you how to import all PDF files from a folder by using Excel’s Power Query – Get Data functionality. You’ll also learn how to make it automatic so when a new file is dropped in the folder, you just need to refresh your combined result and you get the latest data added. I’ll also show you potential errors you could run into, for example Power Query showing a “corrupt” error when another file type is added to the folder. You’ll learn how to exclude other file types and add additional filters to your query.
You’ll also learn how to apply filters to make sure only files with a certain extension are imported and merged.
00:00 Bulk Import of PDF files into Excel
00:26 Get Data From Folder
02:39 Extract a Text Between Delimiters
03:58 Update Report with New Files
04:28 Merge Columns with a Separator
05:06 Filter Files with Specific Extensions
06:45 Filter File Extensions on Sample Level
08:35 Wrap Up
Read the blog post:
🎬 LINKS to related videos:
How Power Query Will Change the Way You Use Excel:
Properly Convert PDF to Excel:
🎒 Get the Official XelPlus MERCH:
🎓 Not sure which of my Excel courses fits best for you? Take the quiz:
📚 Excel RESOURCES I Recommend:
🎥 GEAR I use:
More resources on my Amazon page:
🚩Let’s connect on social:
Instagram:
Twitter:
LinkedIn:
👉 This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel #powerquery…(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
Can you please help me with how to automatically allign PDFs in an excel sheet… tried running macros but didn't work
Does this work on scanned pdf with tables? I when click.on combine files it it gives me an error saying we didn't recognize the format of the first file please filter the list of files so itcontains only supported types (text, CSV,excel workbooks etc) and try again
this barley works for the most basic examples… I tried 10 pdfs and I got back 1000 rows. this method doesnt work unless your importing the most basic of things.
Very nice , madam, it will work on scanned PDF also ?
Love the schoolteacher fetishist roll-play thumbnail!
Can we import multiple pdf in multiple sheets. Example 1 pdf Input in sheet1
Hi, can you please indicate if this function is only available in the latest office versions?
hello Leila, how to import a folder with mutiple FDF files , which each PDF file has at least two tables into Excel? Thanks!
Nicely done, Leila! Excellent explanation of a complicated problem. Thank you for explaining this in such a straightforward way.
when I am trying to upload new pdf files, it is not sorting properly.
Hi
Thank you very much for the explanatory and educational video
I still have a problem,
The table in my PDF file has both text and a number,
When I work according to all these actions only the text works without the number which is the most important part for me,
Is there a way to fix or fix it?
Thank you
Leila: Your presentation is outstanding! But I have a problem. I've got to be missing something??? When I follow your guidance, when I try to upload data from 2 pdfs (same data table layout and format)…the data uploads to Excel abutted in the same row (or column if I transpose)….rather than having a common header with unique rows for each data set. My goal, is for the data from unique pdfs to populate unique rows. Please…I'm hopeful for a moment of your time. Thanks so much!!!!
Hi! I was trying to convert the bulk pdf files to excel , the pdf which I have contains text and tables , I'm getting the error of "format not recognized ", kindly help on how to resolve this issue
Awesome video, saves plenty of time, thank you!
Is there a way that you know of to import multiple similar, but not exactly identical, pdf together? Like for example, they all have the same tables, but perhaps each has the same table in a different pageorder, can you, is there a way filter the tables you want by header maybe?
I am looking for a scanned pdf to excel solution of bulk data, if you have one. Please share
There's no pdf opt in From File menu
This Video Really Grt For me that I always stuck with pdf to excel conversion. This is a grt learning video for me .. but there a issue comes when I have customer pic in this pdf file with different format and I also want to download in excel then there is ? Comes always how do I can convert into excel with picture .. where I can solve my query to get it done in minimum time ? How can I send the pdf format to you so that u can saw the data in pdf format which i want to Convert in excel? Please reply
Hi Leila Gharani I'm getting this error when I try to load a .pdf file – We encountered an error while trying to connect. Details: "Pdf failed to load with result: Corrupted"
Pdf option not showing in power query when select from get data?
Hi ! What about if I have in my folder files with different pages number ? Which sample file do I have to choose ?
This is great. But i need help.
I have set of word documents in SharePoint. I need a specific table (last one) from the document to be extracted into excel. But i'm unable to do as the document is originally in DOCX format with header and footer. Don't know how to exclude the same. I tried converting DOCX to PDF and then tried importing but looks like it doesn't consider the tables properly.
Hi Leila … The video got me excited as I was struggling to find a way to convert PDF data into excel. I am using Excel 2016 but in power query i saw options were limited to Cvs , text & Excel itself but no PDF , consequently I could not convert the data. Is there i way around this problem ?
Hi Leila, thanks for the cool tip! however, is this tip applicable for certain versions of Excel?
i do this all of the time. very few if any of my colleagues know this. i convert pages of monthly brokerage investment statements into excel while everyone else is typing entry line by line.
Great video! I'm trying these with access files, its works perfectly.
Hello if I have more than 2 tables which item can be select please ur support is highly appreciated
this method only work with all the pdf file have the same table format and properties. Can you try all the pdf file have differences table format and properties?
Thanks, it works if the file has one page. We need to follow the same step get rest of the pages
A request to start sql or oracle queries command sessions
I like all your videos
You’re incredible!
Thank you this is great! Any advise on how to exclude the .ini files in the transform data section?
Hi Leila, Hope you also have a solution for multi-page pdf file.
Thanks a lot Leila! You made my big problem go away!
Hi Leila. Is there a way in a pivot table with filtered columns to get the average (not the average in the sum values) i.e grand total/ sum of columns selected? Eg if I filter out 2 out of 10 values in the column the average to be… grand total/8? Is that even do able?
I came to know about this feature recently and I tied myself one idea to modify only Transform Sample File, not the Sample File because Sample file is helper queries.
thank you
Correction this feature is also available in Office 2016 and also in office 2010 if we install addon provided by Microsoft free of cost.
This is brilliant and it will save me so much time, it is coming back with an issue from me as I am using power automate to add PDF files to the folder but when I refresh my data it says "cannot access because another process is using it" – any idea what might be causing this
Very helpful many thanks for your channel.
I hit a snag. The table did not translate all columns correctly. My work around was to use page then use functions like column type and text filters to get to my goal. A great useful channel and this feature will save me hours.
Thanks!! Very insightful!
Hi Leila,
I have successfully used this method after watching this video but I did have one issue.
Example I want to do some data manipulation/reconciling from a bank statement .pdf document that has multiple pages.
I had to data load each .pdf page into excel one at a time and then copy and paste into a new tab to have all the data together on one tab.
Is there a way to get around this multiple page .pdf conversion to excel issue?
I would really like to know what too you use to create your tutorial videos?
I have Office 2021, but don't have this function…
I am so happy until the last sentence that this feature is available only from 2021 and OF 360. Thank you so much anyway for sharing this excellent lecture!