Bulk Combine PDF files to Excel without losing formatting & NO 3rd party software

Поділитися
Вставка
  • Опубліковано 11 гру 2024

КОМЕНТАРІ • 385

  • @LeilaGharani
    @LeilaGharani  10 місяців тому +2

    Get access to the complete Excel Power Query course here 👉 www.xelplus.com/course/excel-power-query/

  • @ugabrew
    @ugabrew 2 роки тому +71

    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!

  • @bill9912
    @bill9912 10 місяців тому +2

    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!

  • @besthobbit
    @besthobbit Рік тому +1

    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.

  • @GarthShaner
    @GarthShaner 2 роки тому +9

    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!

    • @rajkumarbharathi3139
      @rajkumarbharathi3139 2 роки тому

      Same, i had to wite a vb sctipt that extarct invice data from some PDF's. It took me many months to do.

    • @dougmphilly
      @dougmphilly 2 роки тому

      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.

  • @Meolimo
    @Meolimo 2 роки тому

    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

  • @joshuablack3163
    @joshuablack3163 2 роки тому +3

    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.

  • @clashwithketan9130
    @clashwithketan9130 2 роки тому

    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 ❤

  • @ZAHIDHUSSAIN-ri5kg
    @ZAHIDHUSSAIN-ri5kg 2 роки тому

    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!

  • @leksdic
    @leksdic 2 роки тому +3

    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.

  • @excellover7172
    @excellover7172 2 роки тому +3

    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.

  • @rfdc
    @rfdc 2 роки тому

    I need to try this. You know the countless hours I've lost over the years manually typing info from .pdfs to excel!

  • @williambell4591
    @williambell4591 2 роки тому

    Leila, you are THE MAGNIFICIENT! I appreciate you, and your channel so much!

  • @chh8860
    @chh8860 2 роки тому

    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!

    • @LeilaGharani
      @LeilaGharani  2 роки тому

      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.

  • @Manas285
    @Manas285 Рік тому

    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

  • @aprashant1048
    @aprashant1048 2 роки тому +3

    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

  • @islamv880
    @islamv880 2 роки тому

    Well explained, i rarely comment on videos, but you deserve it, thanks for sharing.

  • @pohanaacademy2640
    @pohanaacademy2640 2 роки тому

    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.

  • @MrSupernova111
    @MrSupernova111 2 роки тому

    OMG! Every reporting period I have to go through a client PDF report with multiple pages that is a complete mess. I didn't know that I could import the pdf file to Excel using Power Query so I can manipulate as needed. This is a huge time saver!! Thank you!!

  • @timsulzberger6658
    @timsulzberger6658 2 роки тому +2

    Nicely done, Leila! Excellent explanation of a complicated problem. Thank you for explaining this in such a straightforward way.

  • @tristangeoffroy9286
    @tristangeoffroy9286 2 роки тому

    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

  • @arineems6059
    @arineems6059 2 роки тому +1

    This is therapeutic. I feel calm watching this video ❤️

  • @notmeitsyou6913
    @notmeitsyou6913 2 роки тому +1

    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.😱

  • @Thatien
    @Thatien 2 роки тому

    Amazing. That's very helpful. I ran a test with my PDF data, and it worked perfectly.

  • @villiamo3861
    @villiamo3861 2 роки тому

    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!

  • @Jay-Atom50
    @Jay-Atom50 2 роки тому

    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.

  • @goldendice2254
    @goldendice2254 2 роки тому +2

    Excellent job Leila. Thanks for making our tasks super easy 🙂

  • @saleemahmad-mk8yg
    @saleemahmad-mk8yg 2 роки тому

    The first genious lady in the world.

  • @bizholdburg1576
    @bizholdburg1576 Рік тому +2

    Very nice explanation , clear and profesional. Thank You!

  • @mikelight2008
    @mikelight2008 2 роки тому

    Wow, you're amazing and think of major potential issues and resolve them. Amazing.

  • @Lotus0305
    @Lotus0305 9 місяців тому

    Your videos are definitely the best!!! Thanks for your contributions!

  • @Ticky66MN
    @Ticky66MN 2 роки тому +2

    Wow! That was amazing. Thank you so much Leila! I'll be applying this today.

  • @mr.c6674
    @mr.c6674 2 роки тому +20

    This really depends on the layout of the PDF.

    • @BangaloreYoutube
      @BangaloreYoutube Рік тому +2

      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.

    • @muhammed9971
      @muhammed9971 9 місяців тому

      @@BangaloreUA-cam link

  • @markgreenwood1105
    @markgreenwood1105 2 роки тому

    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.

  • @shivnarayan8532
    @shivnarayan8532 6 місяців тому

    This is extremely helpful especially when looking at multiple invoices

  • @BenCarter
    @BenCarter 2 роки тому

    I use this to help process test results for large fiber installations. Saves me hours of work. :D

  • @rosy3078
    @rosy3078 Рік тому +3

    This video is fantastic! Thank you so much for creating it. I have a question, my original PDF files have multiple pages, hence, PowerQuery created multiple tables in the "Combine File" dialogue. How do you select multiple tables to be included in the sample file, as if I don't, the combined file will only have the first page of each file. Thank you!

    • @alamshar
      @alamshar Рік тому +1

      I have the same issue

  • @chrism9037
    @chrism9037 2 роки тому +2

    Wow! I didn’t know this could be done. Thanks Leila!

  • @rajkumarbharathi3139
    @rajkumarbharathi3139 2 роки тому +1

    Another way to do it is by making a function out of the transformation that is applied to one of the pdf, then apply the funcion to all the pdf, files. Then expand. In that way you do not have to worry about the sample data thing.

    • @KVNVY
      @KVNVY 2 роки тому

      Can you please elaborate. Beacuse i have no Get Data option in my excel

  • @edwardhuang2475
    @edwardhuang2475 2 роки тому

    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!

  • @doctorchem1945
    @doctorchem1945 2 роки тому

    Hello Leila! I am a great fan of your videos, very understandable and knowledgeable.

  • @thenotidevil
    @thenotidevil 2 роки тому +2

    Super awesome as usual. Thanks a lot for sharing this with us all. it's really helpful

  • @atsrikumar1307
    @atsrikumar1307 2 роки тому

    Very easy to understand🌹🌻Maam I want to talk with you
    You r one of my favourite Excel instructor..

  • @KentDyer-as-a-leader
    @KentDyer-as-a-leader 2 роки тому

    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..

  • @hichamjawhari9012
    @hichamjawhari9012 2 роки тому +1

    This is exactly, what I was looking for. THANK YOU!!!

  • @becometheexcelguy4197
    @becometheexcelguy4197 2 роки тому +8

    Great video Leila! Just curious, why did you opt to use “ends with” instead of an exact match on the extension filtering?

  • @dougmphilly
    @dougmphilly 2 роки тому +1

    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.

  • @cartheym
    @cartheym 2 роки тому

    Brilliant! Wonderful explanation, even better with the tips & tricks to avoid the usual pitfalls associated with the default actions.

  • @APG_Creativity
    @APG_Creativity 2 роки тому

    Thanks Ma'am.....this option save my lot of time.... 😍😍😍😍😍👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻👍🏻

  • @prasanth5289
    @prasanth5289 Рік тому

    Excellent and You saved my two days 🎉🎉🎉🎉❤

  • @safuwanch
    @safuwanch 2 роки тому

    after a long time got a video from you 🌹

  • @roshanimaharjan4448
    @roshanimaharjan4448 Рік тому

    very grateful to your tutor. Millions thanks for this.

  • @Jill_Liu
    @Jill_Liu Рік тому +1

    Hi Leila Thanks so much for your video, i have a question, if the pdf has several pages and several tables, and want to just combine specific tables from different pages can this be done by power query? thanks so much.

  • @thepashaacademy
    @thepashaacademy Рік тому

    Excellent informative and experienced training tutorials very very easy to understand Thanks

  • @rppr8
    @rppr8 2 роки тому

    This is an amazing feature, and this video clearly demonstrates it. Awesome job. Thanks.

  • @ciuhutasorin
    @ciuhutasorin 2 роки тому

    Great video! I'm trying these with access files, its works perfectly.

  • @ZeeFinanceAndbusiness
    @ZeeFinanceAndbusiness 2 роки тому

    It's awesome tricks. Save the time and work smoothly

  • @stevecocks3294
    @stevecocks3294 2 роки тому

    I have never tried it like this I have tried however to convert from .pdf to xlsx with mixed success so this is very promising 🙂

  • @g-reef
    @g-reef 2 роки тому +1

    This is a wonderful feature I didn’t now about, thank you 🙏 ❤️

  • @charlesxix
    @charlesxix 2 роки тому

    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.

  • @akusins
    @akusins 2 роки тому

    Great tutorial with showing possible mistakes and how solve.

  • @tommacabulostoledo
    @tommacabulostoledo 2 роки тому

    i love you, Leila! You are the best!

  • @mikeberry9932
    @mikeberry9932 2 роки тому

    This is mind blowing. Your videos are always so clear and thorough. Thank you!

  • @mohammadayubkhan
    @mohammadayubkhan 2 роки тому +1

    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 ?

    • @jeronimolegua6606
      @jeronimolegua6606 Рік тому

      Hi! answers microft has a solution that worked for me: Power Query / Get & Transform -Get data from PDF missing

  • @romulusmilea2747
    @romulusmilea2747 2 роки тому

    I was the first who commented :-))). Another great lesson !

  • @rafatali6058
    @rafatali6058 2 роки тому

    Really You Are Matchless ❤

  • @ryanyang2486
    @ryanyang2486 2 роки тому

    Very clear. Very beautiful teacher.

  • @luisfernandocastillo4045
    @luisfernandocastillo4045 2 роки тому

    Great video...!!! I would like to know how to manage error when source folder is empty.

  • @user-tm3mu3gg9d
    @user-tm3mu3gg9d Рік тому

    I was so happy to have found this tutorial video! Thank you! BUT I have a problem - I click on 'transform data' and then the 'double down arrows' and a window pops up saying 'we didn't recognize the format of your first file...' can you help me?

  • @sourabhchaudhari
    @sourabhchaudhari 2 роки тому

    Thanks a lot Leila! You made my big problem go away!

  • @xyclos
    @xyclos Рік тому

    Fantastic as always. Thanks Leila!

  • @AbsolutelyGeek
    @AbsolutelyGeek 2 роки тому +2

    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 page\order, can you, is there a way filter the tables you want by header maybe?

  • @otiliam2306
    @otiliam2306 Рік тому

    Great ! I am looking for a bit more complex case: multiple pdf's with multiple pages having the same format, data to be extracted for each page. Would you make a vid on this case, thank you.

  • @giorgidvali951159
    @giorgidvali951159 2 роки тому

    Your videos are awesome as usuall!!!

  • @lorimiller623
    @lorimiller623 2 роки тому +1

    I work for CPAs. I haven't had good results using Query to import pdfs because records client submit to us take up multiple lines and the pages have headers and footers. Even if Query sees a table, it sees different things on different pages.

    • @chispas4861
      @chispas4861 2 роки тому +1

      I feel the same. These examples are so cherry-picked. No one has a series of pdfs that look like this. I have tried to replicate many of these videos with REAL pdfs and have never had any success. Too many other items on the reports to actually make them work. That is even considering additional query adjustments to filter-out unnecessary data. Looks great, but hardly ever useful.

    • @lorimiller623
      @lorimiller623 2 роки тому

      @@chispas4861 Query (along with some fairly complex formulas) can work wonders combining Excel spreadsheets and emails. But for internal pdfs, couldn't you just ask for the data as a .csv file?

    • @LeilaGharani
      @LeilaGharani  2 роки тому

      @chispas - I know what you mean. This is the way I felt when I watched tutorials during my corporate job. Here's the thing though with teaching through standalone UA-cam videos. We have to keep it short and concentrate on one key takeaway. Maybe 2 in each video. In this video it was the functionality itself and potential errors with file types - specially the common trap many fall into with the "first file" as sample (this is the realistic part 😉)
      There are of course so many other traps - like column headers being different or you need a mapping table for column headers etc. Each of these are separate videos - that's why my Power Query course is over 18 hours long and I feel like I still haven't covered many things.
      I also agree with Lori that these tools have limitations and if there isn't a logic we can add to our Power Query steps, then it will not work the way we want and we'll have to find other ways to reach our goals.

  • @aawadood1y
    @aawadood1y 2 роки тому

    Thanks, it works if the file has one page. We need to follow the same step get rest of the pages

  • @nonoobott8602
    @nonoobott8602 2 роки тому

    Wow! Thanks so much for this video. I needed this and it's so helpful. Thanks for sharing

  • @denconstutube
    @denconstutube 2 роки тому

    Great video!
    I agree it is like magic, and you explain its use to make it even better.

  • @aaddss9846
    @aaddss9846 2 роки тому +1

    That's so useful! Thank you for sharing.

  • @FranciscoVegagranmusica
    @FranciscoVegagranmusica 2 роки тому

    amazing Leila, thanks for explain and teach us

  • @tombrown1377
    @tombrown1377 Рік тому

    Amazing! You just solved a major issue for us! Quick question... How do I filter out pdf files that don't contain "Table002" in the very beginning?

  • @infaziqbal44
    @infaziqbal44 9 місяців тому

    Hi Leila,
    At this step 1:15, i am getting two tables as the data in the PDF is spread out in two pages in PDF & i am unable to select both tables, how do I fix this?

  • @mirailyani
    @mirailyani 5 місяців тому

    This is really helpful! Thank you

  • @att7912
    @att7912 Рік тому

    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 😉

  • @asimkshafi
    @asimkshafi 3 дні тому

    What if each pdf has multiple tables that we want to bring and then combine? This example shows only one table per pdf.

  • @nnamdiofodile2507
    @nnamdiofodile2507 2 роки тому

    As always, Leila comes through. 🤗

  • @excelkiclass3424
    @excelkiclass3424 2 роки тому

    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

  • @yp4577
    @yp4577 4 місяці тому

    Great explanation! However, I now need to import multiple PDF's wand they each have multiple tables. Power query only lets me select one of the tables in a given PDF file. Is there a way around this?

  • @sheilaababio5934
    @sheilaababio5934 Рік тому

    Thanks, easy steps. However, all the text in column A combined together without any spaces between them. Is there a way to stop that from occurring? For example ,"Numberofinitiativescompletedfromtherecentin-housesatisfactionsurveyon HQfunctions"

  • @osbaldoalbornoz1859
    @osbaldoalbornoz1859 5 місяців тому

    Hi Leila, this video is great, But How can i import the files if each file has several tables? this only works if each file has only 1 Table.

  • @madoffroader
    @madoffroader 2 роки тому

    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

  • @Sylvester.vanWelij
    @Sylvester.vanWelij 2 роки тому +3

    What to do if the information is split across several pages. For example I need to consolidate the second table on each page in each of the files, but the number of pages varies.

  • @sharontang5173
    @sharontang5173 Рік тому

    may I know what to do, if my Excel under Microsoft 2019 did not recognize PDF after I click the double down in 1:13?

  • @youtich9762
    @youtich9762 2 роки тому

    I love this very useful tutorial, thanks 👍

  • @RahulJauhari
    @RahulJauhari 2 роки тому

    u r genius. Thanks for this amazing information👌🙏🏼🙏🏼🙏🏼

  • @raimundojs9547
    @raimundojs9547 Рік тому

    Great! Thank you so much for sharing. Very useful!

  • @johnmckown1267
    @johnmckown1267 2 роки тому

    Thanks!

  • @ansongeorge6122
    @ansongeorge6122 2 роки тому

    Hi
    Thanks for sharing this.
    any way to take the pdf file bookmarks to excel?

  • @wildecorrea6477
    @wildecorrea6477 2 роки тому +1

    🇧🇷👏🏻👏🏻👏🏻 Awesome! I love your videos!

  • @ernestomontenegro1368
    @ernestomontenegro1368 Рік тому

    Excelente Leila. Solo que a mi no me da con los estados de cuenta de Banco de America porque un solo estado de cuenta tiene varios cuadros con diferentes columnas. Puedes hacer un ejemplo con ese tipo de Estado de Cuenta? Gracias