How to Combine PDFs to Excel easily (one or many files)

[@mymatemartin]

Chandoo, can do ... anything. Absolutely amazing Chandoo. I'm so impressed with the advanced features you demonstrate and the very clear and professional quality of your tutorials.

[@chandoo_]

Thank you very much!

[@nolatari]

I haven't even seen the video and I already liked it because I know it will be something great

[@chandoo_]

Aww.. thank you.

[@RobertAfrick]

Just when I thought I knew everything about Excel...Chandoo comes through again with another great, easy to use, helpful video. Chandoo...keep them coming, my mind is open!

[@chandoo_]

Great to hear!

[@Will.Tru8]

Thanks again and again, Chandoo, for all the help. Love the t-shirt

[@beachlimer]

Chandoo this is amazing thank you - I love your advanced excel tips

[@thomas_didymus]

Thanks Chandoo. I did not realize I already had PDF to Excel. I need to do this quite often!

[@John-qe3ky]

Love your videos as I learn useful tips that may be "known" already by experts but not beginners. This PDF tip will save me a lot of time.

[@chandoo_]

Glad it was helpful!

[@apampatel]

​​@@chandoo_ How to open a password protected PDF file with power query as mostly bank statements are protected with a password?

[@wayneedmondson1065]

Hi Chandoo. Great example! Thanks for showing how to do this. Will put it to good use immediately :)) Thumbs up!!

[@chandoo_]

Thanks Wayne... I am glad you will be using these ideas soon.

[@wendygurl85]

Thank you for sharing your video! I just tried to combine multiple PDFs in PowerQuery- it's working great and will save us a lot of time. Thank you so much :)

[@raghvendrasingh9721]

Chandoo sir you are gem at excel and visualisation tools... Love you sir❤❤

[@chandoo_]

Thanks man :)

[@beckybradshaw6108]

Wonderful tutorial! So easy to understand. I learned several new options. Thank you Chandoo!

[@MehediHasan-hk9mk]

I am just falling in love with your videos.

[@chandoo_]

Wow, thank you!

[@samvtiger9892]

thank u sir , i have seen many videos related to "get data" but solve my problem after view this videos.

[@harveenbajwa5520]

Yo Chandoo, you beautiful evil genius you!!

[@stevezickefoose8563]

Wow! Excellent video!

[@rmmccarthy1240]

Thanks, Chandoo!

[@joseagundis1]

Very good, thanks for sharing. Regards Chandoo to New Zeland

[@gagansingh3481]

Chandoo awesome one 👏

[@chandoo_]

My pleasure Gagan...

[@MyCitabria]

Great time saver. Thanks

[@robinbrightman2322]

Superb, as always!

[@chandoo_]

Glad you think so!

[@ondosiregar1291]

Hi Chandoo, please make a power query videos with very messy data. Your data sample is too simple for some of us case. Thank you Chandoo

[@chandramohansingh7664]

Really very useful

[@chandoo_]

Glad to hear that

[@Noob-ng1of]

Thanks bro. Love your channel.

[@chandoo_]

Thank you Noob.. 😀

[@SRCOMPUTEREDUCATION]

Awesome as always

[@canirmalchoudhary8173]

For some I simply use copy paste with other cleaning tricks. But for structured PDF files, this option is really good.

[@chandoo_]

You Bet. I have been using this trick for more than 2 years now (with Power BI route) to save time on gnarly data compilation situations.

[@pektuspektus612]

As always, Thanks Chando!!!

[@chandoo_]

My pleasure!

[@alijaved9698]

Hey chandoo you the best teacher of excel I have come across so far. Having issues with a particular pdf file. When I import via pdf. The navigator is stating this table is empty

[@shahbazali7463]

it is amazing sir, your every video force me to learn something new...thank you so much... one small question... how can we get data from multiple tables when source of data is folder.. mean a folder contain multiple pdf file and from that files we want to extract multiple tables.

[@maxhealy7155]

Super helpful!

[@chandoo_]

You are welcome Max :)

[@kishangor219]

Thank you Sir...thanks a lot for sharing this awesome trick. This trick will help me a lot at work.

[@chandoo_]

Always welcome

[@amitshah5670]

Awesome tips for conversions of files in excel. Sir one more request is pls make detailed video on power query / power pivot and power bi course. Thank you in advance. Keep learning and keep sharing thank you so much sir

[@chandoo_]

Thanks Amit. I am always adding videos on all these topics. On Power Query I have a full 90 minute class that goes in to great details - see it here kzbin.info/www/bejne/hpqpcpSVn8-Ue6s Will be adding a detailed video on DAX & Power Pivot in the month of May or June.

[@amitshah5670]

@@chandoo_ thank you so much for your quick reply i will definitely view and learn Power query video. Thank you so much for taking us in advanced level of excel. Keep learning and keep sharing thank you

[@shincraft1924]

Hi Chandoo ... this is great ...luar biasa !!!

[@chandoo_]

Glad you like it!

[@TheDawsonhousehold]

Thank you!

[@liannne]

Thanks!

[@akgoyal2402]

Why if I keep adding a pdf file daily to same folder, will the power query recognise and update including that file? The date can be inside the file in the pdf), and there will be similar items whose rates get updated daily.

[@techgalaxy100]

Is it possible to make a basic accounting books like Blance Sheet and Profit and Loss, Receipt and Payments using Power Query? Do you have any video on that ? Thanks

[@sasifexcelsolutions4509]

Hi Chandoo, thanks for the video, is there a way to do the if you don't have 365 or power, just normal excel

[@abdullaanpanp7905]

Thanks man

[@yvoderooij6340]

Hi Chandoo, Thanks for the great video. I have one question however. When I want to convert my bank statement from PDF to excel, excel does not show the relevant tables, therefore, I have to click "Pages" instead of "Tables". But when I click on Pages, I can only select one page at a time and the process gets really frustrating. Can you please help with this?

[@drbones6040]

Did you have any luck with this? I have the same problem

[@karanbirsoin9983]

Hi Chandoo, this will save me a lot of time. The pdfs in my folder need a password to open. How do I go around this?

[@samuelkodjoe1645]

very helpful

[@chandoo_]

My pleasure...

[@JoeLoffredo]

Awesome -- THANKS!!!

[@chandoo_]

Glad you liked it!

[@vv9730]

very nice & useful..can we export PDFs to excel from power BI after editing...

[@taizoondean689]

Sir, can you please prepare video on lamda and how to use it.

[@sagargangar5411]

Quick Tips...Big Fix !!

[@chandoo_]

Boom!

[@vipinsainilol]

Thank you Chandoo for sharing insightful information...I was wondering how come these features are not available while using excel in mac?..is there any other way to do so in mac...thank you for help!

[@sandipansarkar9211]

finished watching

[@utkalmaheshwari]

Sometimes statement formats change. In that case, can v choose different tables for different pdf in one single go?

[@chandoo_]

You can customize which tables are picked by editing the query and going to "transform_sample". If you think what you need is either table3 or table4, extract both and use Power Query filters or some other calculation to figure out which ones to include.

[@senb6735]

Thanks for the video I use this for like 100 of invoices each contains multiple pages to check the rates for the each items . But excel identify the table differently it’s become hard to select table for each file. I used to combine the PDFs and then crop the pages to only the data I need then transform. Not sure there are different / easy way to load.

[@chandoo_]

Consistent formats is the key. If your invoices have different layouts then automating them can be tricky (but not impossible). Try customizing the "transform sample" to tweak the outcomes.

[@senb6735]

@@chandoo_ I will try and let you know the outcome soon. Thanks for the tip.

[@Artistram3d1]

The PDF option isn't available in my Excel and Power BI isn't available for Macs. Is there another option that I can use? Thank you for great videos!

[@tahirjamshed5046]

Plz help me how to copy column wise data from pdf and pasts that in excel in row form any shortcut ksy

[@dilkashgazala831]

Hi Chandoo! first of all a big thanks to you for this video however I am not able to solve my problem 100% as I have to extract tables having same structure but present in different pages in different pdfs. I am really struggling. Please help me how can I solve this problem🙏🙏🙏

[@ranjan4495]

Hello sir, can you make a video on rolling chart/dynamic chart with a set of data in datatable, which displays last 6 data points for sales, revenue, time?

[@chandoo_]

I will.

[@mrprakashraobs]

Sir, I am re-framing my question I have Multiple PDF file in one folder, in some PDF when I see in Power Query it shows as Table 3 and for some PDF it shows the same content in Table 4. so importing becomes problem if I select the sample from table 3 then only data from all pdf from table 3 will be considered for processing. one common thing I have is table has one column Name as " LineNO". First I should find if PDF has a table having the column as LineNO if Yes then consider that as the table to process and move to next PDF and do the same process So Importing of Table having Column Name as "LineNo" is the condition that to be passed before Transform.

[@corriehiepner7735]

@mrprakashraobs I have a similar problem, but in my case, I have multiple invoices that look identical, yet some have 4 tables and others have 3 tables. In each I have to select the table that is one less than the max table no, so where there are 4 tables, I need to select Table003 and where there are 3 tables, I need to select Table002. Any advice on how to achieve this?

[@excellence_is_me]

Hey I purchased complete excel course from you and it was helpful. Can you please tell me if I can copy and paste this iny excel and run macro in normal excel if my workplace do not have office 365. Please please help Chandoo 🙏 😢

[@chandoo_]

Thanks Pooja. VBA cannot record actions performed outside Office apps. As you would be dealing with PDF, this won't work. You need to use either 365 or use other tools such as Power Automate or Power BI.

[@CuriousJessy]

Hi Can you please upload a video for Converting multiple pdf in a folder into JPG OR PNG and save in a different folder via VBA

[@jarisiri9766]

Thank you so much. How come my excel don't give composte table, i got a separated table for each page.

[@chandoo_]

You are welcome Jari. The table detection depends on the PDFs you use and the version of Excel.

[@ncbiradar2976]

Hey can you tell me how to open power bi

[@ravisankar295]

Bro.. Is there any option to extract only required information of customers like name, address mobile number, bill date, etc.. from combined pdf file?. Evenif it has paragraphs. Please upload a video. Thanks muchh!. 🙏✨️

[@Ahmed3854]

Hi Chandoo, Greeting from Pakistan. I have a question. I have converted a pdf table having more than 100 pages into excel after watching your videos. However, there is a problem, a text written in the first row of every ten pages e.g. name of the course, this text is changed after every 5 to 10 pages. I have to put it in a column on left side and then analyze the data. how can I do that. Thank you for providing us such a quality stuff on excel. It is all brilliant. I am thankful to you and appreciate your efforts.

[@chandoo_]

You need to apply some additional transformation steps to extract this. I suggest learning a bit more about PQ. See this video for a start - kzbin.info/www/bejne/hpqpcpSVn8-Ue6s

[@shoaibrehman9988]

Like your's T shirt good video thanks

[@chandoo_]

Thanks 👍 It is my favourite. Got it from Legoland Günzburg when we visited it in 2019.

[@cciotd]

I am running Microsoft Office 365 on a MacBook Pro computer. Can't find how to get Power Query to work. Appears to be an add-in, and I can find the PC downloads for that but not the one (if it exists) for the MAC. Sad, because this is an extremely cool and helpful feature.

[@chandoo_]

Sorry, but as far as I know, Power Query for Mac Excel doesn't yet have PDF option. May be in future...?

[@venkatiyer7459]

Guru I am using Excel 2019...I will go through the process of installing Power Bi .... if you have any video on how to do it please send me the link.... As usual your way of doing things looks very simple ... thanks a lot for teaching something new every now and then....

[@chandoo_]

See this page for more information how to use Power BI - chandoo.org/wp/powerbi-introduction/

[@venkatiyer7459]

@@chandoo_ Thank you so much Sir ... I'll go through the same ....

[@casairamk2790]

Anna, Video super but try to record the video with more volume please..

[@chandoo_]

Thank you Sai... I will keep this in mind for future videos.

[@AbdulRehman-wn4wq]

Converted Sucessfully! But there is an Issue. I have multiple Files and each File has dynamic Tables with Same Header when i converted those files the Results are much worse. Data is Scattered Like Quantity (from Multiple files and Multiple Pages) falls in Column C, G, H, and other Data to. Could you help me in this regard ☺

[@johnnastasi9143]

Hello, I've been listening to your excellent KZbin Power Query lessons. Very good sessions. I have one problem I've been trying to overcome that maybe you can give me some pointers. I have 400 PDF's in a folder and I want to transform the PDF's into a table format and then append each PDF table in one spreadsheet. I've been able to transform the first PDF in the format I need but when I add new PDF files into the folder, the new PDF's the new translated tables appear as additional columns versus the additional rows that I need. Any advice? Thank you for your help.

[@md-mohammed8455]

Sir it will work scan pdf file also.?

[@aravindsuresh4147]

How to get bulk of horizontal pdf data to vertical format in excel

[@aliasgardudhwala8621]

Hi candoo ..it's great information I have one query..i want to convert multiple excel file into PDF format

[@chandoo_]

You can use Power Query to combine the data in multiple files to one and then use the "Save as PDF" option in the file menu. If you want to automate or need to combine sheets that have charts etc, then you can use VBA.

[@knowledgeisstrength4787]

Does it work with MS Excel 2019 version?

[@venkateswarlujetti8735]

Valid information chandoo. But, the volume is bit low

[@chandoo_]

Thanks for the feedback. I am changing the audio process now and except to have better volume in future videos

[@manjukarthik4084]

How to open power bi desktop software in laptop bro ?

[@DiwankarSingh]

how can i extract data from pdf to excel that is not present in a table format. lets say i want to add supplier name kept as a heading from a pdf and put this data to a separate column in excel. Pl ease help!!

[@venkatasivakrishnaabburi3633]

What you ll do if you have different table names in different PDF files

[@LucGervais]

I have a fillable pdf that I was trying to extract the data from using this method, but none of the entered data showed up. The data fields where all blank. Any idea why? I used adobe acrobat pro to design the fillable pdf for distribution. Help

[@taizoondean689]

Thanks bro for your help. Can you tell me will it work if PDF is password protected. Thank you

[@chandoo_]

Give it a try. I suspect, it wont.

[@alterchannel2501]

Fantastic, please Chandoo help me be awsome 😅, I can't work this out, I need help: what if I have a folder with multiple pdf files and each pdf contains multiple tables I need? How can I get all the tables from all the pdf's in the folder and import every single table in different queries? Thank you in advance

[@chandoo_]

Hi Alter, Thanks for your comments. You can do it like this: 1) Follow the video process for one table for all PDFs in the folder 2) Once the query is working and you get what you want, just repeat the process for other tables 2.1) you can do it by doing all the steps again. If you have just 2 or 3 more tables, do this. 2.2) you can duplicate the "transform sample" query and create new types of transform functions to extract the tables. This requires knowing a little bit more of Power Query. So do it if you have lots of tables or just want to learn. I can't explain the process here, but do check out my Power Query mini-course where I talk about the advanced concepts - chandoo.org/wp/power-query-power-course/

[@mooradium]

Hello. Thank you very much for your video. I've a question. I got a workbook with 100 sheets and 1 table in each sheet. I want to rename all the table headers of the 100 sheets at once. How this is done ?

[@chandoo_]

For renaming things like that, you can use a simple macro. Here is a tutorial on getting started with VBA - chandoo.org/wp/introduction-to-vba-macros/

[@mooradium]

@@chandoo_ thank you! ☺️

[@sugathasagar]

my pdf had 1000+ pages, how to select all tables in all pages in single click? Thanks for you're response and time.

[@chandoo_]

Interesting. 1) Connect to the PDF 2) Select any one table in the "navigator" screen 3) Go to PQ Editor, delete all steps except Source 4) At this point PQ will list all tables. 5) Filter out anything you don't need 6) Then use the column "expand" button to expand and combine all tables to one big table.

[@miketringuyen3405]

Hi Chandoo, What if I want to combine all of tables in Pages 1,2,3 and 4 for all of PDfs. Thanks.

[@chandoo_]

You can easily do that. First load the tables to Power Query and then use the "Append" function in home ribbon to combine all tables to one giant table. I did a video on this concept (but for spreadsheets) here - kzbin.info/www/bejne/oZDYmKaef5mqf9k

[@miketringuyen3405]

@@chandoo_ there are mismatch columns when I append tables. Any solutions? Can I send the PDF files to your email?

[@apampatel]

@chandoo How can we open a password protected PDF to Power Query?

[@SKMishra]

Hi, if statement is protected bu password, Is their any shortcut rather then opening and printing as pdf. and from different Banks.

[@chandoo_]

Unfortunately, this method only works with unprotected PDFs for now. May be in future MS will add password field / prompt in the import process.

[@rajahtkanagarajah1258]

Hi Chandoo, Would appreciate if you can help. When i press combine and transform data for a folder(pdfs with mutiple sheets each) im not able to see an option like table 1-4 vs whats on your screen. why is this so? is it an additional add on? tq

[@pablodebiddlybo3771]

Chandoo I have issues whereby the text or numbers in a cell once loaded from pdf into excel are not calculating and I have to manually key in data entries in the cells again . I’m running office 365 could this be coming from the pdf where a glitch may have been in built ? I’m not sure what is happening . Thank you another great video

[@chandoo_]

Hi Paul, It seems when you import the data from PDF, Excel (Power Query) is bringing in the data as TEXT. I suggest adding a step in PQ to convert text to numbers. This should fix the problem.

[@pablodebiddlybo3771]

@@chandoo_ thank you all is good now

[@agrophile2095]

Hello man, My single data table in pdf spanned across 65 pages. Power BI method worked very well and it gave me a single excel sheet to work with! But I was using Tableau and in tableau, it gave me 65 tables and there was not option to merge all of those. Can you suggest how to merge all these tables in a single sheet?

[@balakrishnanp7190]

Chandoo, Now a days these CC statements come with password protected. Will PQ prompt for PW while run the query?

[@chandoo_]

Yeah, but unfortunately, as of now Excel can only read unprotected PDFs. You need to first unlock them somehow.

[@antonysamarawickrema3886]

When I do this I don't get the composite pdf table, that you show in minute 2.00, maybe because I have more than 14 tables

[@bintang13t]

Hi, thanks for the video, but can it do data collection, but the pdf is contains one records for the data, so basically multiple pdf who has the same format.

[@chandoo_]

It can. I would use the Page option instead of table and apply some transformations in power query to get what I want.

[@bintang13t]

@@chandoo_ thank you

[@bintang13t]

I'll tell my friend

[@traceynix2256]

Hi, Chandoo, I love the way you teach. Question. I'm trying to download 2 years worth of bank and credit card statements to track every single income and expense transaction for my household budget. Currently, when I get data, I can choose only 1 table, which might be 1/3 of the page of a bank statement. Is there a way to grab all of the tables at once, then sort later to clean up the file?

[@chandoo_]

Thanks Tracey. I show another technique (with Excel files, but works for PDF too) in my recent video. See it here and apply the concept to PDF. kzbin.info/www/bejne/oZDYmKaef5mqf9k

[@rajappasubramanian]

That's fantastic. One question : Can we do the same for scanned image in pdf files? We have few files with data in image format and need them in excel.

[@chandoo_]

Unfortunately, this feature is not capable of OCR (optical character recognition). You would need special software or web tools for that.

[@rajappasubramanian]

@@chandoo_ yes sir. Thank you.

[@t2p5g4]

I am doing some accounting from bank statements. I have fooled around with scanning and OCR. I was given a kodak scanner and it works fine in most cases. The exception is Scotiabank, where they put a dotted line where the decimal is.

[@mayuronce]

Hi sir.... I am facing an issue when my bank statement PDF was converted to Excel there are some rows (contains Date ) are not proper i.e., like 10Apr and in next row shows year i.e., 2023 like this so please help me how to solve this problem

[@vinayak449]

How to work enable pdf option in Microsoft 2013 version

[@pratikagarwal1082]

Hi Chandoo, Great video but I want to know that if pdf contains data in image form without table then is it possible to convert it into excel.

[@chandoo_]

Thanks Pratik... At the moment PDF options in Excel and Power BI do not support image recognition feature. But you can use Excel app on your phone to scan documents and load data to Excel. Save the file and open it on PC to use such data. I will make a tutorial on this soon.

[@gcorbalan]

Thank you very much Chandoo for sharing your knowledge with the KZbin community worldwide. I have followed the steps in your video! For some reason the PDF report I am trying to convert to PQ despite it seems to have same format… from page 4, row 200 the columns are not aligned from 3rd column. Is there a way to push data to the right from certain columns? I can send you the source report in PDF 😎 Thank you very much!

[@chandoo_]

You are welcome Garry. I think there might be some invisible characters in the PDF which are causing the trouble. You can backtrack the Power Query steps and see where the culprit is. Alternatively, you can split the table to two chunks (one with correct data, one with right shifted rows) and adjust the issue and append both. I am unable to look at the file as I have a busy few weeks ahead. Please try the above suggestions and if the problem happens again try posting for help in Stack overflow or something. 😀

[@AmitVerma-vk4sj]

WHICH MICROSOFT VERSION WE CAN HAVE THIS OPTION ( GET DATA FROM PDF FILE) . I DONT HAVE IN MY EXCEL OPTION WHICH VERSION IS EXCEL 2010. PLZ SUGGEST

[@chandoo_]

You need Excel 365.

[@vv9730]

many thanks for such video..btw..what is the meaning of "chandoo.."...

[@chandoo_]

You are welcome VV. Chandoo is my nickname. It means moon in my native language.

[@vv9730]

@@chandoo_ is youtube allows to create channel in nick name..????😂😂😂 n ur profile pic is ur younger version..???

[@chandoo_]

Yes and yes.

[@vv9730]

@@chandoo_ there used to be a tv ad of electric switches..in which a guy when puts his finger in electic switch gets his hairs high like ur profile pic..seeing ur profile pic..that ad comes to my mind..😂😂😂

[@RonDavidowicz]

Great video. Q: if you add more cc statements to the same folder, will refreshing the query automatically add the data from the new statements?

[@chandoo_]

Yes, exactly

[@kimmiebostick-ramsey1312]

I am having issues with the Power BI portion. I downloaded and it loads into Power BI but it does not go into my excel workbook like yours did and I cannot find a way to get there. Any suggestions?

[@chandoo_]

Once you have the data in Power BI, you can open the Query Editor and then copy paste data to Excel.

[@johnzani6965]

Hi Chandoo I'm trying to Combine and transform pdf files from a folder. It is giving me problems due to each file in my folder having column numbers differing? From 3 columns to 8 columns depending on the month file. Basically I'm wanting to upload my bank statements monthly using an automated pq. I'm sure this must be a common problem but it does not appear well covered on youtube - unless I'm missing something. Thanks John

[@chandoo_]

Hi John... you can customize the "transform sample" to get all the columns instead of just 3. I cover some of these advanced topics in my Power Query mini-course too. See it here - chandoo.org/wp/power-query-power-course/

[@johnzani6965]

@@chandoo_ hi Chandoo I sent you an email with more info. Some of the pdf files seem to have hidden spaces which split into extra columns in pq. This shunts the columns of these files to the right. When stacked over each other the cell data moved with the extra columns. Just looking for a transformation that can solve this and which doesn’t involve having to use complicated M language? I’m not a regular excel user so a course would be a bit excessive for me.

[@GaganDeep-ut4xf]

Pdf option not showing in power query when select the file.