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.
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!
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?
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 :)
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.
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
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.
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
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
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 ua-cam.com/video/PiFAa_jjaEI/v-deo.html Will be adding a detailed video on DAX & Power Pivot in the month of May or June.
@@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
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.
@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?
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.
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!
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🙏🙏🙏
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 ☺
Hello, I've been listening to your excellent UA-cam 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.
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!. 🙏✨️
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.
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.
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?
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?
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.
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
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.
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 - ua-cam.com/video/PiFAa_jjaEI/v-deo.html
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
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 🙏 😢
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.
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.
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
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....
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!!
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 ?
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
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/
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?
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. ua-cam.com/video/k_ugshJ4wIw/v-deo.html
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.
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 - ua-cam.com/video/k_ugshJ4wIw/v-deo.html
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
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.
Hi, May I know if the data I want is located on a different page for the pdf file (sample file in pg12, but some pdf only got 10 pages and it located in pg3) so it shows error, are there any solutions for this?
Thank you very much Chandoo for sharing your knowledge with the UA-cam 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!
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. 😀
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.
When I choose a folder as source excel does not give an option to choose multiple tables. How can the solution be customized to accommodate when there is more than 1 table in the PDF?
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.
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.
Hi Chandoo, Excellent video. However I have one question to ask. I am reading multiple pdf. from Folder. In "Combine files" - my pdf file has god 2 pages, however xls. split them into two separate pages and I am not able to merge them. Any idea with workaround I can send you example if you provide me with the contact. Thank you very much in advance
Thanks Petr. I suggest using Append option to combine two pages to one table. I am not so good with replying to emails. I suggest checking out my PQ video and figuring the necessary steps out. Intro to Power Query video - ua-cam.com/video/PiFAa_jjaEI/v-deo.html
Good videos...there are many on this topic, but I am always asking how to detect and import information from PDF scanned Images, or PDF's that are "per-hand" written, not Word to PDF transfered documents... that is a challenge =)
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?
This is simply superb! I had 184 PDFs which needed to be copy pasted in excel. Special thanks for specifying power bi because I was hunting through youtube and everybody spoke only of this Microsoft feature. Just one more thing I would like to ask for.... Is there any way to select multiple tables?
Wow... that is awesome to hear. You can customize the *transform sample query* to do this. It is tricky to explain in a comment. I suggest seeing this article - chandoo.org/wp/combine-excel-files-using-power-query/
I understood the question to be, "How to do multiple tables within the same file?", not multiple spreadsheets. I have the same question because it only allows us to pick one table at a time.
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, 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.
Thank you very much!
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!
Great to hear!
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?
I haven't even seen the video and I already liked it because I know it will be something great
Aww.. thank you.
Chandoo this is amazing thank you - I love your advanced excel tips
Chandoo sir you are gem at excel and visualisation tools... Love you sir❤❤
Thanks man :)
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.
Glad it was helpful!
@@chandoo_
How to open a password protected PDF file with power query as mostly bank statements are protected with a password?
Thanks Chandoo. I did not realize I already had PDF to Excel. I need to do this quite often!
Thanks again and again, Chandoo, for all the help. Love the t-shirt
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 :)
Hi Chandoo. Great example! Thanks for showing how to do this. Will put it to good use immediately :)) Thumbs up!!
Thanks Wayne... I am glad you will be using these ideas soon.
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.
thank u sir , i have seen many videos related to "get data" but solve my problem after view this videos.
Hi Chandoo, thanks for the video, is there a way to do the if you don't have 365 or power, just normal excel
I am just falling in love with your videos.
Wow, thank you!
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
For some I simply use copy paste with other cleaning tricks. But for structured PDF files, this option is really good.
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.
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
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.
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
Wonderful tutorial! So easy to understand. I learned several new options. Thank you Chandoo!
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
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 ua-cam.com/video/PiFAa_jjaEI/v-deo.html
Will be adding a detailed video on DAX & Power Pivot in the month of May or June.
@@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
very nice & useful..can we export PDFs to excel from power BI after editing...
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!
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.
@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?
Sometimes statement formats change. In that case, can v choose different tables for different pdf in one single go?
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.
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?
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!
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🙏🙏🙏
Plz help me how to copy column wise data from pdf and pasts that in excel in row form any shortcut ksy
Thanks, Chandoo!
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
☺
Hello, I've been listening to your excellent UA-cam 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.
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!. 🙏✨️
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.
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.
@@chandoo_ I will try and let you know the outcome soon. Thanks for the tip.
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?
I will.
Thanks bro. Love your channel.
Thank you Noob.. 😀
Really very useful
Glad to hear that
Yo Chandoo, you beautiful evil genius you!!
Does it work with MS Excel 2019 version?
Great time saver. Thanks
Wow! Excellent video!
Chandoo awesome one 👏
My pleasure Gagan...
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?
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?
Yes, exactly
Superb, as always!
Glad you think so!
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.
Sorry, but as far as I know, Power Query for Mac Excel doesn't yet have PDF option. May be in future...?
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
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.
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 - ua-cam.com/video/PiFAa_jjaEI/v-deo.html
Thank you so much. How come my excel don't give composte table, i got a separated table for each page.
You are welcome Jari. The table detection depends on the PDFs you use and the version of Excel.
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
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 🙏 😢
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.
Hi candoo ..it's great information
I have one query..i want to convert multiple excel file into PDF format
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.
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
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
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
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....
See this page for more information how to use Power BI - chandoo.org/wp/powerbi-introduction/
@@chandoo_ Thank you so much Sir ... I'll go through the same ....
Very good, thanks for sharing. Regards Chandoo to New Zeland
Sir it will work scan pdf file also.?
Sir, can you please prepare video on lamda and how to use it.
What you ll do if you have different table names in different PDF files
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!!
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 ?
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/
@@chandoo_ thank you! ☺️
finished watching
As always, Thanks Chando!!!
My pleasure!
Super helpful!
You are welcome Max :)
@chandoo
How can we open a password protected PDF to Power Query?
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
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/
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?
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. ua-cam.com/video/k_ugshJ4wIw/v-deo.html
Thanks bro for your help. Can you tell me will it work if PDF is password protected. Thank you
Give it a try. I suspect, it wont.
my pdf had 1000+ pages, how to select all tables in all pages in single click? Thanks for you're response and time.
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.
Hey can you tell me how to open power bi
Thank you Sir...thanks a lot for sharing this awesome trick. This trick will help me a lot at work.
Always welcome
Valid information chandoo. But, the volume is bit low
Thanks for the feedback. I am changing the audio process now and except to have better volume in future videos
Hi Chandoo,
What if I want to combine all of tables in Pages 1,2,3 and 4 for all of PDfs. Thanks.
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 - ua-cam.com/video/k_ugshJ4wIw/v-deo.html
@@chandoo_ there are mismatch columns when I append tables. Any solutions? Can I send the PDF files to your email?
How to get bulk of horizontal pdf data to vertical format in excel
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
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.
@@chandoo_ thank you all is good now
Anna, Video super but try to record the video with more volume please..
Thank you Sai... I will keep this in mind for future videos.
How to open power bi desktop software in laptop bro ?
Hi, May I know if the data I want is located on a different page for the pdf file (sample file in pg12, but some pdf only got 10 pages and it located in pg3) so it shows error, are there any solutions for this?
Chandoo, Now a days these CC statements come with password protected. Will PQ prompt for PW while run the query?
Yeah, but unfortunately, as of now Excel can only read unprotected PDFs. You need to first unlock them somehow.
Thank you!
Thank you very much Chandoo for sharing your knowledge with the UA-cam 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!
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. 😀
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.
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.
When I choose a folder as source excel does not give an option to choose multiple tables. How can the solution be customized to accommodate when there is more than 1 table in the PDF?
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.
Unfortunately, this feature is not capable of OCR (optical character recognition). You would need special software or web tools for that.
@@chandoo_ yes sir. Thank you.
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.
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
You need Excel 365.
Awesome as always
Hi, if statement is protected bu password, Is their any shortcut rather then opening and printing as pdf.
and from different Banks.
Unfortunately, this method only works with unprotected PDFs for now. May be in future MS will add password field / prompt in the import process.
Hi Chandoo, Excellent video. However I have one question to ask. I am reading multiple pdf. from Folder. In "Combine files" - my pdf file has god 2 pages, however xls. split them into two separate pages and I am not able to merge them. Any idea with workaround
I can send you example if you provide me with the contact.
Thank you very much in advance
Thanks Petr.
I suggest using Append option to combine two pages to one table. I am not so good with replying to emails. I suggest checking out my PQ video and figuring the necessary steps out.
Intro to Power Query video - ua-cam.com/video/PiFAa_jjaEI/v-deo.html
This is not available online 365. Little or no documentation from Microsoft.
Good videos...there are many on this topic, but I am always asking how to detect and import information from PDF scanned Images, or PDF's that are "per-hand" written, not Word to PDF transfered documents... that is a challenge =)
Great suggestion! Unfortunately, as of now none of the PDF import options are capable of OCR. May be in future.
How do you combine multiple tables?
How to work enable pdf option in Microsoft 2013 version
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?
Once you have the data in Power BI, you can open the Query Editor and then copy paste data to Excel.
This is simply superb! I had 184 PDFs which needed to be copy pasted in excel. Special thanks for specifying power bi because I was hunting through youtube and everybody spoke only of this Microsoft feature.
Just one more thing I would like to ask for.... Is there any way to select multiple tables?
Wow... that is awesome to hear.
You can customize the *transform sample query* to do this. It is tricky to explain in a comment. I suggest seeing this article - chandoo.org/wp/combine-excel-files-using-power-query/
@@chandoo_ thanks a lot!
I understood the question to be, "How to do multiple tables within the same file?", not multiple spreadsheets. I have the same question because it only allows us to pick one table at a time.
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.
It can. I would use the Page option instead of table and apply some transformations in power query to get what I want.
@@chandoo_ thank you
I'll tell my friend
konsa version h yea
Quick Tips...Big Fix !!
Boom!
Pdf option not showing in power query when select the file.