Convert PDF to Excel effortlessly (one file or combine many)

barry my accountant called me the other day and said hey chandu can i get a copy of all your credit card statement for the last year i don't want to manually go into 12 pdfs and you know copy paste the data i would rather use one excel table of course i replied say yes sure and i quickly opened up power query combined all my pdf files into one single excel table removed any unnecessary information and sent the data to barry before i finished my morning coffee let us go through the process so you two can do it easily so here is how my typical credit card statement looked like it has some header details and a transaction details table that goes into multiple pages depending on how many transactions happened in a month now let's see how to get a single credit card statement into excel first and then see how we can get an entire list of statements into one single table so we go home and then data from here get data from file from pdf now this option is available in office 365 but if you are using a version of excel where you don't see from pdf option what you can do is you can alternatively open the free power bi desktop software and follow the exact steps i am showing in the power bi get data button that does have pdf option and it works for all um pretty much all versions of power bi and select your credit card statement power query will show you a preview of the data what it found in the pdf so you can it will show any systematic tables that it has identified so here is one state table that's the table number two table number three and it has identified a composite table that spanned three pages so table four this is exactly what i want so i'll click click that and i'll simply say load and tada my credit card statement is in excel as a table now it almost works i see that in the original statement there were five columns whereas here there are six columns so let's just quickly eyeball and see what's going on i could see that here it has somehow split this data into two columns so let's go and fix that right click edit the query from here let's just see what happened so it went to the very first thing navigated to the page and at this point somehow column four and column five got split now we don't really know how power query identifies the table columns within um within the pdf but what we could do is at the end of this process i can take table these two columns and i can just transform and merge them so i'll just say merge columns and that'll pretty much just create a new column we'll name this as transaction detail and our new column is there it calls transaction detail one because there was a column like that and once this is done we'll just go home close and load and our credit card statement is there if you don't want to show any columns for example the card number or the date of processing you can exclude them as well you can go back to the query editor and from here you can just select the columns that you don't want remove those columns you can also name this table for example cc for credit card and that will be the table name when it gets loaded into excel so that is step one now let's go and see how you can get an entire folder of credit card statements consolidate all of them into a single table so here is my three credit card statements that we will be reconciling they are three months but exactly same pattern some of them have 100 transactions some of them have 80 or 70. go to data get data from file from folder specify the folder path and power query says it found three files there of pdf and what do you want to do now we can go ahead and combine so we'll just say combine and just let's go with combine and transform data so that we can fix any problems in the data so it will show you a sample of one of the files and says what do you want in this file so we'll say i want the table for now what will happen is power query will open the every file in that folder it will look for that table for and then it will load that so we'll click ok this will create this final table it's called multiple and then it has the file name card date of transaction date of processing transaction detail this extra column that is happening every now and then and then finally the amount column now what we will do is we can go and fix some of the problems for example we don't need some of these columns i'm just going to select source name card date processed and right click remove those columns likewise i'll select transaction detail and the column five i'm going to just merge them into simply a new column called detail and now all of the transactions and their details and the amounts are all listed by the date they happened and recorded so this is all good if you would like to exclude any specific transactions by a store or a date or amount you can specify those filter conditions and once you're happy click on close and load and excel will show you all the statement details as i mentioned earlier if you do not have the pdf option what you can do is you can repeat these exact steps in power bi desktop once the data is within power query you can copy the data there you can open a blank excel file and paste it there and it will get it too so that is all for now if you have any specific questions on the combining pdf option or merging data through this route please post a comment so that i can help you out if you want to learn more about power query check out the video that is linked on the screen and if you want some quick wins to clean your data check out my 10 ways to clean data easily in excel video see you again in another video bye

Channel: Chandoo

Views: 28,760

Rating: 4.9479165 out of 5

Keywords: convert pdf to excel, convert pdf to excell, convert pdf to excel without losing formatting, combine multiple PDFs to one Excel file, how to use Power query to extract data from credit card statements, combine multiple bank statements with excel, consolidate credit card PDF statements with excel, how do I get multi page data from PDF to Excel?, extract adobe pdf data, easy way to get data from pdf, combine data from several PDF files, import PDF data to Excel

Id: 5I0pnj_-LLA

Length: 7min 7sec (427 seconds)

Published: Wed Apr 14 2021