How to Import PDF Files into Excel with Power Query
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
Пікірлер: 165
You're an excellent teacher and instructor. I can hardly wait for this functionality on my power query. Thanks for sharing
You are first person to show append power queries.
Hi Jon.. thanks for the preview into this new feature. Can't wait for it to be released to the general public. Thumbs up!!
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
This is a game changer for me and I'd like to see more examples if possible. Thanks again!
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.
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.
Your videos are always very useful and with clear explanation. Congratulation from Guinea-Bissau (África)
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. 👍
so thoroughly explained! thank you so much!
I truly don't understand the thumb-down votes for these videos. I suspect it's coming from competing KZread 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! 😃
This is a great video. I love it. Thank you Jon.
Thank you brother, I learned so much from you!!!
Excellent! Thanks Jon!
Excellent Mr. Jon ....Thanks
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.
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.
Great Tutorial,Looking Forward To Try PDF Imports When It Becomes Available...Thank You Jon :)
@ExcelCampus
4 жыл бұрын
Hope you enjoy it! 😊
Excellent explanation. Thanks a lot, Sir. 🙏
well demonstrated! Thank you!
@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?
Thanks for the great video. Already thinking of reports to pull in once its public.
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.
Is there any way to connect two or more data models from external files (including all relationship and measures ) in a new excel file?
append as new! thats what i needed. thank u.
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! :)
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
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?
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.
You are awesome - Thank you!!!!
Do you know if the stand alone version of Office will ever be available for the PDF feature?
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
Many thanks for the info. Good job
Your Videos are fantastic! Great new feature too!
@ExcelCampus
9 ай бұрын
Glad you like them @chanelgabb2040! 😀
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.
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.
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.
I love this content because no other KZreadr demonstate APPEND QUERY like this. Thank you trillions!
@ExcelCampus
5 ай бұрын
Thank you for your feedback! 😀
I need to try this!! 🙂
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.
Thank you Excellent
Fantastic!
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?
Thanks John - cool feature, thanks for highlighting it and running through how to use it.
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.😃👍
I have excel 365 but this feature was not displayed how to get this options of importing pdf
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
Hi Jon, Is it possible to fetch only limited fields from a PDF file like Vendor name, amount and address?
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.
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
Well Explained! thank you for your efforts here :)
@sasda1234s
Жыл бұрын
I have a quick question, can we export data from password protected PDF file?
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?
Brilliant job. Great presentation. . You are great teacher . Thanks a ton 🙏🙏🙏
@ExcelCampus
4 ай бұрын
Thanks and welcome! 😀
Hi John this is great
thank you very much Jon, but how does it work when you have multiple pdf files?
very useful!
May ALLAH Bless you Sir, Thanks so much
@LcTricker
4 жыл бұрын
Allah blesses all Excel / PowerQuery users
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!
Befitting video found finally which solved my problem.
@ExcelCampus
8 ай бұрын
Glad it helped! 😀
is there an easy way to import data from pdf forms straight to excel spreadsheet???
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 ! 😀
Dear Sir your videos are very helpful for me could u confirm me when this feature pdf will be available in office 2019
Thank you for the amazing tutorial. Is it possible to remove the Super URLs if the PDF content includes them?
hello Jon, how to import a folder with mutiple FDF files which each PDF file with at least two tables into Excel?
Thank you
how can we use xlookup on pdf data? it won't work for me.
Thank you Sir, Excellent
@ExcelCampus
5 ай бұрын
You are most welcome 😀
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
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.
Awesome!
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.
Thank you. How are we going to do if we need all the tables? Thanks
Hi Lovely! When I tried to import the pdf file, power query says nothing available on the navigation tab although there are 3 pages of tables in my pdf invoice. Is there anyway we could upload the pdf file that bypasses this situation? I’m not sure it’s a pdf settings issue that stop us to upload the info into excel that makes it “unreadable” or something? I have no idea just thoughts. Please tell us if you found a way?
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.
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
Hi I'm having issues with bulk converting into excel using pdf from folder. I'm bringing in my bank statements for analysis each month. I have jan-aug ready in my folder to bulk install initially and then want to update sept nov dec etc on month by month basis from there on. Unfortunately some of the pdf files have differing numbers of column headers, depending on the file month. They range from 3 columns to 8 columns? extra columns occur randomly between promoted named headers. There should only be 6 promoted columns I want. I cant just remove the other columns because they contain my data. Its proving almost impossible to do a single query to automate the process. Any tips on this?
Hi. Is it possible to load in multiple pdf dokuments?
Thank you sir for your informative video. Is it possible to make a video tutorial about PDF file like bank statement
Sir I have installed Office365 but import PDF option it doesn't show kindly help me in this regard,thanks
Hi John, in my office 365 excel, when I go to data, get data, then file, I don't see the pdf option. How do I get the PDF option
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
Is this feature available in excel 2013, because I don't find it
Nice, really cool
@ExcelCampus
4 жыл бұрын
Thank you! Cheers! 😀
Hello, could you please why after export pdf to excel sum formula does not work. And in the end of sheet not show as usual sum and avarage
When I try to do this I see multiple pages, but no preview. Am I doing something wrong?
Awesome really help
@ExcelCampus
4 жыл бұрын
Glad it helped 😊
Greetings John, does the pdf file need to be in a certain format or permission settings? I was told that the pdf files that I wanna import via Excel are not importable because they are locked down.
@ExcelCampus
4 жыл бұрын
Hey Kurt, Great question! I don't believe the PDF feature supports password protected files yet. You would need to unprotect the file and save it without the password before importing. I tried to import a password protected PDF but got an error message. Hopefully they will make it possible to input the password in the future. Thanks again and have a nice day! 🙂
Thanks
Really thank u sir.
@ExcelCampus
4 жыл бұрын
Thanks Rahul! 🙂
@RAHULGUPTA-qb9cw
4 жыл бұрын
@@ExcelCampus sir my excel not showing explore PDF
Would you show how to put a pdf bank statement into excel as searchable
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.
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.
I have Bank statement with 50 pages. The issue I am facing that in Power query editor there are separate 50 pages and I have to do all settings 1 by 1 on every page which is a lot time consuming. Is there any way I can make all the pages as 1 page and apply the settings?
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.
Good for very simple pages - not for power users.
I dont have the "get data" option in my excel?
How to select all the pages of the pdf in powerquery?
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.
great
How do I remove the page breaks in pdf while loading in excel ?