Bulk Combine PDF files to Excel without losing formatting & NO 3rd party software
ฝัง
- เผยแพร่เมื่อ 3 ก.ค. 2024
- Get the Full Power Query Course here 👉 www.xelplus.com/course/excel-...
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.
Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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: www.xelplus.com/bulk-import-p...
🎬 LINKS to related videos:
How Power Query Will Change the Way You Use Excel: • How Power Query Will C...
Properly Convert PDF to Excel: • Properly Convert PDF t...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
Get access to the complete Excel Power Query course here 👉 www.xelplus.com/course/excel-power-query/
Wow! You have a way of designing these examples and walking us through them that deserves a Teacher-of-the-Year award. Possibly also a Medal of Freedom. We all owe you a big thanks!
More than like *Teacher of the Millennium*. 😁😇🙏
🎉
Thank you, Leila! You're the best! After watching several other videos on this topic that were too complicated, too wordy or just "too-something", I discovered your video. What a breath of fresh air! Concise and successful! Can't ask for anything more than that. Your video helped me to accomplish exactly what I needed it to do. Again, thank you!
This is awesome.. A big thanks for your time and efforts. For me this task was impossible until I watched this video. May you live long in good health and peace so that you can enlighten us with your latest contents in excel. Always love to spend time to watch your videos. Thanks once again.
Nicely done, Leila! Excellent explanation of a complicated problem. Thank you for explaining this in such a straightforward way.
Thank you SO SO much. People at work think it's a good idea to save their spreadsheets as MULTIPAGE PDFs and send them around the org instead of just sending the Excel file.... now I won't need to spend so much time making them an Excel file again.
Holy Moley! I am thinking about how much time this would have saved me on a project I did back in 2007. This is amazing! Thank you so much for sharing!
Same, i had to wite a vb sctipt that extarct invice data from some PDF's. It took me many months to do.
you telling me. i look at my work history of so much time wasted typing in data. arghh. well, at least i got time and a half once i hit 50 hours.
This is awesome. I would normally combine the PDFs in Adobe Pro and then export them as an excel, but that's a one-time solution. Having the ability to refresh the file as more PDFs are added to the folder makes the process look much more streamlined.
Amazing. That's very helpful. I ran a test with my PDF data, and it worked perfectly.
This is therapeutic. I feel calm watching this video ❤️
Well explained, i rarely comment on videos, but you deserve it, thanks for sharing.
Hi Leila, …I loved this demonstration, once again you taught me something I through I knew already. For you to have a smile: I was used for years doing this process by making my first file query, then advanced edit to change it into the function I will invoke against the filtered files query.
Then recently, I saw this help query. Saying myself, what’s that noise? I didn’t ask for, it’s weird It looks like something I need but didn’t know how to use it, so I trashed it all, and redo it my own old way of doing. …ridiculous, no?
As you already mentioned this tool is one of the best productivity and solid tool I have ever used for years in the area, tremendous, powerful, fit to a large range of users, from basic to advanced.
Thank you for the knowledge sharing
Leila, you are THE MAGNIFICIENT! I appreciate you, and your channel so much!
Thank you so much!
Wow! That was amazing. Thank you so much Leila! I'll be applying this today.
Wow, you're amazing and think of major potential issues and resolve them. Amazing.
I'm glad I found this channel. I've saved MS Excel Playlist and planning to watch all videos very soon. High quality content is highly appreciated. I will support this channel. From India ❤
Thanks 🙌
Excellent vid. The thing about filtering for the sample file was very good - not very obvious (to me, at least!) when you're looking at an error and thinking you've already filtered to get just pdfs!
Thanks!
Wow! I didn’t know this could be done. Thanks Leila!
This is extremely helpful especially when looking at multiple invoices
This is an amazing feature, and this video clearly demonstrates it. Awesome job. Thanks.
Excellent job Leila. Thanks for making our tasks super easy 🙂
Very nice explanation , clear and profesional. Thank You!
Many, many thanks Leila! Seen your video after a long time as i have lost my job and have no concern with Excel nowadays. Best of luck!
Great video! I'm trying these with access files, its works perfectly.
It's awesome tricks. Save the time and work smoothly
Super awesome as usual. Thanks a lot for sharing this with us all. it's really helpful
This is a wonderful feature I didn’t now about, thank you 🙏 ❤️
Your videos are definitely the best!!! Thanks for your contributions!
Glad you like them!
Very clear. Very beautiful teacher.
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.
Brilliant! Wonderful explanation, even better with the tips & tricks to avoid the usual pitfalls associated with the default actions.
Glad you enjoyed it, Mick!
Tks Leila. I am now retired, but I used Power query a lot to transform PDF files on 500 transactions per day. Long live @ Power query
👍😁
Excellent informative and experienced training tutorials very very easy to understand Thanks
Thanks a lot Leila! You made my big problem go away!
U r awesome maam u saved my daily 3 hrs of time during shift. It was hectic for mr everyday to prepare excel data from 30-40 pdfs
very grateful to your tutor. Millions thanks for this.
Fantastic as always. Thanks Leila!
Thank you Leila Gharani, very interesting and knowledgeable videos of Excel are being shared by you. I always recommend your channel to my training participants in Afghanistan.
Thank you for your support!
Really You Are Matchless ❤
This is exactly, what I was looking for. THANK YOU!!!
Excellent and You saved my two days 🎉🎉🎉🎉❤
Great video!
I agree it is like magic, and you explain its use to make it even better.
The first genious lady in the world.
Hello Leila! I am a great fan of your videos, very understandable and knowledgeable.
Happy to hear that!
Your videos are awesome as usuall!!!
after a long time got a video from you 🌹
i love you, Leila! You are the best!
Thanks Ma'am.....this option save my lot of time.... 😍😍😍😍😍👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻
Really useful! Thanks a lot Leila!
Excellent video! Thank you!
Wow! Thanks so much for this video. I needed this and it's so helpful. Thanks for sharing
With great power query comes great responsibility to watch your fantastic training video to learn its proper use. I like it when software is intuitive and I can figure things out but there is no way I can account for all the great features you point out.
Great to hear! Thank you.
You’re incredible!
This really depends on the layout of the PDF.
True I actually have some structured and unstructured PDFs I’m able to extract the table I want for structured using python but I’m unable to find something for unstructured (basically the ones you can’t copy text from) does anyone have any solution also if someone wants the code for the python extractor lmk.
@@BangaloreTH-cam link
I use this to help process test results for large fiber installations. Saves me hours of work. :D
Thanks Leila! It’s a really nice feature.
Thanks!! Very insightful!
Very easy to understand🌹🌻Maam I want to talk with you
You r one of my favourite Excel instructor..
Thank you Leila for this great video 📹 very helpful 👍
Great! Thank you so much for sharing. Very useful!
Great tutorial with showing possible mistakes and how solve.
Glad you liked it
wonderful feature, thanks a lot Leila
Thanks for sharing. Definitely handy, but a core assumption is that data needs to one level of rows and columns. If not, excel cannot handle multiple levels of rows and columns. For instance, and XML, which has n-depth, cannot be easily parsed by this built-in utility in excel. One needs to code it to handle multi levels
Perfect ,,, Thank you Leila 👍
As always, Leila comes through. 🤗
Awesome! thanks so much for sharing.
Excellent, this is so useful
u r genius. Thanks for this amazing information👌🙏🏼🙏🏼🙏🏼
This is really helpful! Thank you
You're so welcome!
That's so useful! Thank you for sharing.
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!
Leila I would like to suggest a feature I found on power query. It is the ability to anti-join two queries, in order to exclude items that exist in one of them from the other. This was brilliant for me cause I had a list with all my currently open orders and one list with the orders that got invoiced today and would get updated during the nightly system procedure. This would be simple if all entries in both lists were unique, but since the lists had orders with their products, they had the order number multiple times for each product. So appending the two lists and removing duplicates was a no no. Power query came to the rescue and not only it can exclude items that exist in one list from another, but now I can automate it. I can just provide the files with the entries and the managers, that need to watch the order fulfillment closely, have just to hit refresh.
Great! Thanks for sharing.
very useful. thanks
Thank you very much, my dear teacher
I love this very useful tutorial, thanks 👍
Thank you, you are briliant
amazing Leila, thanks for explain and teach us
My pleasure, Francisco!
This is mind blowing. Your videos are always so clear and thorough. Thank you!
great video!
Thanks, it works if the file has one page. We need to follow the same step get rest of the pages
Thank you for the useful tips 🙂
My pleasure 😊
I need to try this. You know the countless hours I've lost over the years manually typing info from .pdfs to excel!
Oh yes. I can relate!
At the 9:00 minute mark " ... let me know if you have ever imported files like this into Excel ...".
No ... not yet. However, I have been thinking of doing something similar with many years of historical data. My concern is that the pdf line items and headings may change from year to year.
Once again ... another intriguing lesson from Prof. Leila. Thank you!
It does have some limitations, but as long as there is a logic to the headers you can add PQ steps. You could for example add a mapping table for the headers or find a dynamic way to pinpoint the first row to import. If you do end up using this feature let me know how it went.
Thanks Leila, I like that you told us what Office versions you could use. I don't have 365 so I followed along and found I couldn't do it.
Same here
Impressive! Brilliant!
Great video Leila! Just curious, why did you opt to use “ends with” instead of an exact match on the extension filtering?
Many thanks 🥰
Very helpful!
Thank You!
🇧🇷👏🏻👏🏻👏🏻 Awesome! I love your videos!
Thank you so much.
Very useful to computer generated pdfs...
👍
thank you!
Thank you so much 😭
Thank you Leila
Excellent 👍👍
Most mind-blowing thing learnt from this is excel file will be prioritised over pdf files as first file in PowerQuery.
I would be doomed and might be furstrated to start the whole project over again.😱
Great video as usual. :)
It is interesting, I just did this two days ago for a client.
Consider the following code snippet. I think this is cleaner and note the use of Pdf.Tables([Content} Custom Column.
let
Source = Folder.Files("C:\Reporting\SalesPDF"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "SalesData")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Pdf.Tables([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Id", "Name", "Kind", "Data"}, {"Id", "Name.1", "Kind", "Data"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Table") and ([Id] = "Table001")),
other code..
Wow! Amazing
Very nice. I tried this and works very fine with single page pdf files or meaning excel extracts the information from one table in the page which one choses in the sample. I have been trying to bulk extract different types of table information from multiple page pdf files. Let us say in one page there are several tables in a multi page pdf and you have many pdf files which have the same table type inside. Is it possible to import all tables from all pages and from all pdf files at once? It would be very helpful if you do one video on with such examples 😉