How to Import PDF Files into Excel with Power Query

Подписаться 575 тыс.

Просмотров 147 тыс.

50% 1

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
This video teaches you how to import tables from a PDF file into Excel with Power Query. Table data that spans multiple pages in the PDF can be appended together into one cohesive Excel Table.
If you’d like to view the accompanying blog post on my website, you can access it here: www.excelcampus.com/powerquer...
Upload your own example PDF file for a future video tutorial:
www.excelcampus.com/power-que...
From PDF is a new feature in Power Query that is currently available of the Beta Channel for Microsoft/Office 365 subscribers. It will be rolling out to other channels in the coming months.
Importing PDF files into Excel has always been a challenge. This new feature of Power Query detects structured data tables within pages, making it easy to cleanup and prepare the data in the Power Query Editor.
We can also append (combine or stack) multiple tables from multiple pages in the PDF file. Power Query allows us to automate this entire process, making it fast and easy to import multiple PDF files into Excel.
Related Videos:
How to Combine Excel Tables or Worksheets with Power Query:
• How To Combine Excel T...
Power Query Overview - Automate Data Tasks in Excel & Power BI:
• How To Automate Data T...
How to Install Power Query in Excel 2010 or 2013 for Windows:
• How To Install Power Q...
#MsExcel #ExcelCampus
00:00 Introduction
01:11 Import from PDF
02:48 Append Data

Опубликовано:

6 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 165

@amitdagli3361 Год назад

You are first person to show append power queries.

@nonoobott8602 4 года назад

You're an excellent teacher and instructor. I can hardly wait for this functionality on my power query. Thanks for sharing

@wayneedmondson1065 4 года назад

Hi Jon.. thanks for the preview into this new feature. Can't wait for it to be released to the general public. Thumbs up!!

@80andromeda08 4 года назад

Really you are one of the best instructors I have ever know them. All thanks to you dear for your amazing efforts. Hope this function to be released very soon

@markbui8093 3 года назад

I truly don't understand the thumb-down votes for these videos. I suspect it's coming from competing RU-vid sites. Thank you Jon for taking the time to put together these tutorial videos for us. Please keep up the great work!!!

@ExcelCampus 3 года назад

Thanks Mark! 😃

@michaeljones2843 4 года назад

This is a game changer for me and I'd like to see more examples if possible. Thanks again!

@tomwelt1825 4 года назад

Thanks, Jon, super content. I can't wait to try it when the From PDF feature is made available. Then I'd really be interested in your planned video on refreshing the data.

@lucasamorim5506 3 года назад

so thoroughly explained! thank you so much!

@gladiadordoexcel2358 4 года назад

Your videos are always very useful and with clear explanation. Congratulation from Guinea-Bissau (África)

@dhunpagla3871 4 года назад

Excellent Mr. Jon ....Thanks

@jongskicastro3119 4 года назад

Thank you brother, I learned so much from you!!!

@kicksandblowingguns7555 4 года назад

Dear Jon, Good Day, I'm a new subscriber, your explaining method is simple and extraordinary. Thanks for spreading your knowledge across the world. Looking fwd to see your more tutorials.

@axcolo 4 года назад

Excellent! Thanks Jon!

@RonLeedy 4 года назад

Thanks for the great video. Already thinking of reports to pull in once its public.

@Amy732173 2 года назад

This is a great video. I love it. Thank you Jon.

@JagadeeshBharadwaj-1954 Год назад

Excellent explanation. Thanks a lot, Sir. 🙏

@aemero7477 2 года назад

well demonstrated! Thank you!

@lwinnekins4303 Год назад

Thanks, Jon, for this tutorial as it was a great help in understanding how to download PDF files using power query.

@ExcelCampus Год назад

Thanks for your feedback, L! :)

@jaimealmenaravino9173 Месяц назад

Many thanks for the info. Good job

@johnmonroe6206 4 года назад

Excellent video, Jon, thanks for sharing. Can't wait for this functionality!

@ExcelCampus 4 года назад

Thanks so much John. I appreciate your support! Looking forward to everyone getting their hands on it too. 👍

@willchung7811 Год назад

append as new! thats what i needed. thank u.

@ebeaucourt 2 года назад

Is it possible to make a video tutorial to import multiple pdf's (all with the same structutre) but with multiple tables? Thanks for your help and excellent tutorails.

@MrBileDuct Год назад

You are awesome - Thank you!!!!

@esthersuh3388 5 месяцев назад

I love this content because no other RU-vidr demonstate APPEND QUERY like this. Thank you trillions!

@ExcelCampus 5 месяцев назад

Thank you for your feedback! 😀

@jarisiri9766 2 года назад

Thank you very much ! This is, so far that i saw showing upto Append. My excel don't give composite table Option for all table together. This help a lot.

@darrylmorgan 4 года назад

Great Tutorial,Looking Forward To Try PDF Imports When It Becomes Available...Thank You Jon :)

@ExcelCampus 4 года назад

Hope you enjoy it! 😊

@chanelgabb2040 10 месяцев назад

Your Videos are fantastic! Great new feature too!

@ExcelCampus 10 месяцев назад

Glad you like them @chanelgabb2040! 😀

@user-vt5ln7qq4j 4 года назад

Fantastic!

@krishnannair4068 4 месяца назад

Brilliant job. Great presentation. . You are great teacher . Thanks a ton 🙏🙏🙏

@ExcelCampus 4 месяца назад

Thanks and welcome! 😀

@Soori8 Год назад

Thank you Excellent

@karenallen1193 4 года назад

Thanks John - cool feature, thanks for highlighting it and running through how to use it.

@emmab7658 2 года назад

I need to try this!! 🙂

@stevennye5075 4 года назад

very useful!

@vinaynagaonkar8592 Год назад

Thank You Mr. Jon, that was a wonderful demonstration of using a Power Query and its Editor very effectively, This will help me a Lot😊

@ExcelCampus 11 месяцев назад

You're very welcome, @vinaynagaonkar8592 ! 😀

@ahmkowser5888 4 года назад

Hi John this is great

@beverlygilalta 2 года назад

Thank you

@danyalahmedrasheed4216 2 года назад

Awesome!

@sasda1234s 2 года назад

Well Explained! thank you for your efforts here :)

@sasda1234s Год назад

I have a quick question, can we export data from password protected PDF file?

@knowledgetransfer7460 9 месяцев назад

Befitting video found finally which solved my problem.

@ExcelCampus 8 месяцев назад

Glad it helped! 😀

@user-rm4cg2zn6x 5 месяцев назад

Thank you Sir, Excellent

@ExcelCampus 5 месяцев назад

You are most welcome 😀

@altafamirali4946 Год назад

Thank you sir for your informative video. Is it possible to make a video tutorial about PDF file like bank statement

@veryutils 23 дня назад

Thank you for your informative video! I recommend trying the VeryPDF PDF to Excel Converter. This Windows software automatically converts PDF files to Excel spreadsheets and can also combine multiple PDFs into a single Excel file without losing formatting.

@Debaurin 10 месяцев назад

Thanks for explanation. I have a question. If i have same data template with different entry. Is there any method to use import all files in same table

@ibnhamid5838 2 года назад

hi sir, thanks for the knowledge. really appreciate it! do you familiar with stock broker statement in pdf? mind if to a tutorial how to table it and find p/L and risk/potential ratio.. really appreciate it sir. thanks again.

@mohamedadjal8502 3 года назад

Hi, Professor, you have provide in a lot of effort for these videos, thank you, I have a question if we have for example in cell "a1" the number 10.00 m, how to have this number with the same format in cell "b1 "using a text function or some other function, thank you very much.😃👍

@muhammadjaved5131 4 года назад

Dear Sir your videos are very helpful for me could u confirm me when this feature pdf will be available in office 2019

@AnbarasuAnnamalai 4 года назад

Nice, really cool

@ExcelCampus 4 года назад

Thank you! Cheers! 😀

@suki9860 2 года назад

Thanks, Jon. Very clear tutorial. Quick question. Any inspiration to give for where Column 2 data in the first table is shown in Column 1 in the second table bc Column 1 data is blank in table 2. In fact table 2 columns all get shifted bc of two blank columns in table 2 i.e. Col 1 -> Blank Col 2-> Col 1 Col 3-> Col 2 Col 4-> Blank and Col 5-> Col 3. Thanks, Jon.

@damusandy Год назад

Thanks

@frankiecao442 7 месяцев назад

Thank you for the amazing tutorial. Is it possible to remove the Super URLs if the PDF content includes them?

@loismathius8747 4 года назад

Awesome really help

@ExcelCampus 4 года назад

Glad it helped 😊

@heuteld 4 года назад

Thanks Jon...excellent video. Just curious - what tool are you using to draw the outline around a mouse selection in your video. Great presentation tool.

@muminjonurmanov9911 4 года назад

Hello Jon. Thanks for the tutorial. Which software do you use to zoom the window during your tutorials? I want to make tutorials on some other topic but also need that software. Thanks

@syedmukram9332 4 года назад

May ALLAH Bless you Sir, Thanks so much

@LcTricker 4 года назад

Allah blesses all Excel / PowerQuery users

@user-js8vd9nr3n 4 месяца назад

Good for very simple pages - not for power users.

@smilejs 2 месяца назад

Thank you. How are we going to do if we need all the tables? Thanks

@marlonbarrion4040 4 года назад

Thank you Jon! Does it work if let say the table in the PDF is in picture format? sometimes we can do copy data from pdf and paste it in excel but when the format is in picture, we cannot copy it and paste in excel, so I think it goes the same way if we use power query.

@sankhabiswas6356 4 года назад

Is there any way to connect two or more data models from external files (including all relationship and measures ) in a new excel file?

@RAHULGUPTA-qb9cw 4 года назад

Really thank u sir.

@ExcelCampus 4 года назад

Thanks Rahul! 🙂

@RAHULGUPTA-qb9cw 4 года назад

@@ExcelCampus sir my excel not showing explore PDF

@rjobaanable 3 года назад

thank you very much Jon, but how does it work when you have multiple pdf files?

@the_starlinkway7821 2 года назад

@Excel Campus - Jon great video - question, in my case, only my first table and remaining 21 tables dont have headers. Following your steps, the appended output table does not append the tables correctly. For example, table 1 has 10 columns (with headers), table 2 is then appended but at the bottom of the table starting at column 11, then table 3 starts at column 21, and so on. how do you tell excel to stack them vertically and make it recognize that you have the columns in each tables in same locations?

@sheetalbarapatre6808 4 года назад

Hi Jon, Thank you for making such informative videos on excel. I know that my question is not relevant to above but I am not able to see the formatting options button besides the cell. Ref video from Dec 14 2018. Can you please help. The version of excel I am using is Microsoft Excel for Mac Version 16.37. Waiting for your reply. Thank you so much.

@LarryWright2 2 года назад

I have a complex example that would be very useful. I have 4 years ov invoices from vendor in PDF's. I want to merge them into single XLS, then have a combined data set to see queries on the data. Like item searchable charges, taxes, etc.

@heyalbee 3 года назад

I have a pdf with the same table formatting for numbers and text across 50 pages. I cannot get it to import. Excel keeps trying to connect to the file but never does. Are there restrictions to file size or complexity? Thank you.

@rajahtkanagarajah1258 Год назад

Hi Jon, great video. How do we do the same thing for multiple pdfs. Im not able to replicate this when extracting from a file with many pdfs and each pdf has different number of pages with same table columns. Do share if this is possible

@alighasemi1141 4 года назад

great

@aperson1181 3 года назад

It is NOT on MS Office Professional Plus 2016. Microsoft wants your subscription fees and it seems to roll the feature only to Office 365.

@kenmcmillan2637 4 года назад

Do you know if the stand alone version of Office will ever be available for the PDF feature?

@Spreigs Год назад

I have a particularly difficult set of data - when power query imports it its not only splitting the data in the cells from the PDF table across columns but also across rows. and in some cases the cell data is split both ways - some of the column to the right is on the left and split across the row below. I've tried a number of the MVPs videos and its better but I'm still needing to go line by line. Thoughts?

@DigitalMindQuest Год назад

You can use the same example and use the page instead of tables, that would be fantastic if you can

@walidahmed209 4 года назад

Hello guys What would be the reason for this error message when inserting pivot table into connection only query (DataFormat.Error) We couldn’t convert to Number

@preethisussankoppula2229 2 года назад

Hey..thank you for the video..can you please help with my question. I need page number details from pdf file to reflect in Excel sheet. is it possible?

@nagendravishwamitra3652 4 года назад

I have excel 365 but this feature was not displayed how to get this options of importing pdf

@jamesryandraper5476 2 года назад

I’m curious to know if we can count how many tables there are in a pdf file? Currently working on automation project which needs a power query. Source file is a pdf. Please share if this is possible.

@deepikaanand8241 2 года назад

Hi Jon, Is it possible to fetch only limited fields from a PDF file like Vendor name, amount and address?

@ihavenoklou4773 3 года назад

I did the same thing but the pages are empty after transforming. I also did OCR but still the same. I can't figure out what did wrong.

@evelyncoleman7476 Год назад

Would you show how to put a pdf bank statement into excel as searchable

@jacquesdoyon1043 3 года назад

I have a file that contains many tables (over 40+ pages) but each line may contain a variable number of entries, 2 to 4. Depending of the data present in the pdf file, some tables may show data in two columns but they may be column 1 and 3; others will show data in columns 1 and 4, and so on. All together, the data is a sparse matrix or hollow matrix. Is there a way to force Power Query to consider all tables the same way from the start, i.e. from where the data is showing up in the page?

@KATSIGOAL 4 года назад

is there an easy way to import data from pdf forms straight to excel spreadsheet???

@alterchannel2501 2 года назад

Thank you for the video, but I really need help for this problem: I have multiple pdf files in a folder and each file has multiple pages containing tables I need, but every month , depending on the lenght of the data in the tables, the tables might be on a different page (pdf page number) than last month. Every table in each pdf is always formatted the same every month but each table in the pdf is different from the other. I need to create a separate query for each table (example if I have a total of 20 pdf tables, I need 20 queries to elaborate and import in 20 separate data models). How can I achieve that, making sure that if next month the pdf has more pages, it incluedes every table I need. Please help me.

@eexmann 4 года назад

Hi Jon, Eddy here from the Netherlands, Can't wat to get the new functionality, so I can start using Power Query. Would like to send you a PDF file with the results of my Sun-power collectors which I have on my roof. Until Now I manualy copy the data into an excel sheet to follow the power consumption as well as the power I save using the electricity from my Sun-Cells.

@MaiTran-xf3op 3 года назад

If file pdf has lots of page (over 10 pages), loading pages to excel by using power query is really a disaster since I have to wait 2 hours or more just for excel to creat connection.

@kenaun8268 Год назад

How can I import a monthly pdf file with a dynamic table? For example, in month1 the table is on page 100, and in month2 is on page 500. BTW this pdf file has over 1,000 tables. Many thanks, love your video.

@naseershaikh2414 3 года назад

Sir I have installed Office365 but import PDF option it doesn't show kindly help me in this regard,thanks

@lawrence5304 Год назад

hello Jon, how to import a folder with mutiple FDF files which each PDF file with at least two tables into Excel?

@rayzucchero9478 3 года назад

Hi. Nice presentation, but why isn't Excel recognizing my decompressed pdf files? I split some bank statements by months, unzipped them on explorer, but Excel doesn't see them when I try to import - only sees the zipped folder. Thank you

@helbox 3 года назад

Hi. Is it possible to load in multiple pdf dokuments?

@granmastern 4 года назад

how long do you think it will take for this to get out of beta? this is a long awaited feature!

@ExcelCampus 4 года назад

I don't have an ETA but hoping it's soon too!

@cassandraterry3171 3 года назад

When I try to do this I see multiple pages, but no preview. Am I doing something wrong?

@KM-co5mx 4 года назад

Hi Jon, one more question please... if the pdf file has an image of a table then I assume power query can not scan the table. Correct? I also have pdf files that contain images of tables that I want to bring into Excel. I assume Excel 365 doesn’t have a scan feature. Thanks Again!

@alokranjan7323 2 года назад

When will this be available for us?

@workingmumkitty 4 года назад

Hi Jon, I don't have 'From PDF' option under 'get data', is this something to do with my Excel version pls?

@AronieroDYami 3 года назад

I have the same problem. I have Microsoft Office 365 ProPlus and I dont have "From PDF" option. Did you solve this somehow?

@tomtomdad 3 года назад

I have the same problem. "From PDF" icon does not exist in my Excel.

@AronieroDYami 3 года назад

@@tomtomdad I forgot to update my comment here :P atm I have option "From PDF" I think i got this option 1 month ago. So you should get this option after 1-2 months of using Excel :P

@tomtomdad 3 года назад

@@AronieroDYami Noted. thank you

@hitenumrania 3 года назад

Hey Jon, Thanks for the informative video, unfortunately I do not have the "Get Data" option in my data tab on my MAC. I have seen some videos on the "Get Data" PDF to Excel conversion in MAC but can't figure out how to get the option on my system. I am running office 365 sub. Pls help

@subjectline 3 года назад

Hi, I don't think Power Query exists for Mac. It's Windows only.

@noufalc.m458 2 года назад

How to import pdf files from a folder that pdfs contains more than one page...while generating the query it pics only 1st page of all pdf..if any solution reply please

@jeffreymichaelwakeli Год назад

I'm trying to convert bank statements from pdf to Excel then use power query to organize all the bank deposits and label the sources of income, like if it was a cash deposit. Has anybody had success with this ?

@arfaali1092 3 года назад

Is this feature available in excel 2013, because I don't find it

@zzzrajzzz 3 года назад

How do I remove the page breaks in pdf while loading in excel ?

@paulhitchman2312 2 года назад

I have hundreds of PDF files from BIM360 and I'm looking to search all the files in a folder for a specific text Serial Number and bring all the number into one chart with the file name and document number... not easy as although the PDF's are from one source and share similarities the form differ slightly depending on test assets