014. Import Data from MULTIPLE PDF into Excel with Power Query! (no 3rd party software needed)

in this tutorial i want to teach you how to import data from multiple pdf files and for those of you who use the manual methodology that i'm going to show you just now you're going to thank me at the end of the video because it's gonna save you a lot of hours so let's look at an example in this folder i have three pdf files representing sales from different countries so let's assume i want to extract the information so what i could do is just open one of the files like here and then try to copy paste so if i try to do this i'm already struggling with copy pasting all the data so i can try from the bottom and then i get it i'm gonna do copy and then i'm gonna do control v here and you can see that i get the data in a terrible shape and soon i'm gonna start writing it manually myself which is not ideal so let me show you how to address this problem basically you need to use power query you go to data under data you have get and transform data if you don't see this it means your version of excel is before 2016 but luckily there is a free add-in called power query you can just add it and then you'll have the same thing so i'm just gonna do get data from file from folder and then i'm gonna select my folder under my folder i have the file but funny enough the way power query is configured it will not show me the files which is okay because i know it's the right folder so i'm gonna do open and under open i can see the files that are in this folder so i'm gonna click on transform data and then you can see the files what do i do with the files to get only those three well i can play with the extension so if i go to the extension i just select pdf and remove the other two and i say okay i get left with only the files that i want once this is okay here you can click on those two arrows to see the data it's for combining the files you will get a window like this one if i click to see what's in my pdf i can see that i have a table which i can work with and just be careful here because what power query asks you to do is to select the sample file you want to make transformations on and then it will apply the same transformations on the other files and append the data so here it selected my first file which is the japan file i can also use the french file or the angolian file so i'm fine with the japan one i'm just gonna select my table i'm gonna say okay and here is my data so you can see it took the file name so here i have japan i'm scrolling down i get friends and then obviously at the end i'll have angola and it took the data so this is my date the color the item and the quantity and all the steps got recorded automatically so just to show you a little bit what happens so we had our source files if you remember then we filtered some rows then whatever got filtered is gone and here you have a custom function so what happens in this custom function basically if you go transform sample file this is what happened so those are the steps if you need to change them you can change them here but if i go back to the main query there are a few additional steps that happened for example here change type means that it has identified for every column whether it's a text like this one or a date like this one and it has selected the right data type so i could make modifications here if you want to do some more complex modifications i have a full playlist on power query i'm gonna put the link in the description and i also have a video on how to treat very complex pdf problems in this case i'm just gonna do something simple so here we have our source.name i don't like the name i'm gonna call it country i double clicked on it and changed it then enter here i want the date not column one i double click write date enter and anything i do will get recorded here so that's fine the other thing that i want to do is if you see here i just want japan i don't want the full file name so what i could do is just click on the column transform extract text between delimiters so what are the delimiters here if you see japan comes after an underscore so i'm going to put an underscore and at the end you have dot pdf so we're going to write dot pdf the only problem i have is that there is another underscore here so it might create some issues to fix this i click on advanced options here it says from the start of the input so it will start until it finds the first delimiter what i'm gonna do is from the end of the input so it's gonna start here my first delimiter will be here and it will take japan in the middle so we're gonna say okay and you can see that now i fixed my country the other thing i want to do is you see here i have the color and the item i want to merge them so i can click on the first one press ctrl keep control pressed then you click on the second one and you can go to transform you have merge columns we click on merge columns what will be the separator between the two so purple t-shirt it will be a space so i'm going to select space and then i can rename this for example selection so this is what i have selected so this that will be the name of the column we're gonna say okay and as you can see now we have purple t-shirt purple shirt etc once i'm done and happy with this the only thing i need to do is to do home close and load close and load to and then i can say okay table either in a new worksheet or an existing worksheet so it's gonna put it here i'm gonna say ok and you can see that the data is loaded here we have 87 rows and this is what happens if you want to make a modification because you're not happy with it just double click here you go back to the editor and you can change anything that you want so this is the whole editor and you can go add steps and so on so i'm just going to close this and now i'm going to show you a case where i'm going to add another file so here i have an extra file i have india i'm just gonna copy it and just drop it here that's the only thing i need to do so i got a new file i do this here i can come right click refresh see now i have 116 rows i go down i can start seeing the india data so this is a quick lesson on how to take data from multiple pdf files the only caveat i need to tell you is that sometimes the pdf files that you have will have harder tables to import so please look at my other video to see how to fix the most common problems and please let me know whether it worked or not and if not what are your challenges in the comment section and as usual it would be amazing if you just like this video and if you didn't do so subscribe to the channel

Channel: EXCEL WIZARD IN MINUTES

Views: 17,081

Keywords: import data from multiple pdf into excel with power query, convert pdf to excel free, how to convert pdf to excel without paying, best way to convert pdf to excel free, export data from pdf to excel, excel power query connect to pdf, combine pdf files to excel, bulk combine pdf, how to convert multiple pdf files to excel at once, how to convert multiple pdfs into one excel with power query, import many pdfs into excel, Import pdf files to excel power query

Id: _S5fFWdZCwE

Length: 8min 12sec (492 seconds)

Published: Mon Jun 27 2022