Create PDF Invoice for Banking with airSlate SignNow

Create pdf invoice for Banking

Okay, so what do you do when you have all of  these PDF files and you need to combine the   information from these PDFs into a single Excel  sheet? Well, you can start with copy and paste,   but soon you'll probably find it easier to just  type everything from scratch, but no, don't   do that. No copy and paste and no typing from  scratch. Instead, you're going to do this.   You're going to open up Excel and select Blank workbook. Then, go to Data > Get Data > From   File > From Folder. Now, you get to pick  the folder in which you have all of your   PDFs saved. In mine, it is sitting right here in  the folder "sales pdf". I'm going to select that   and open. Now, in this view that opens up, we  get to peek inside that folder. These are all the   different files that are saved there. Now, just  click on "Transform Data". Here, we can see more   information about the file. We can see the  extension, the date it was modified, date created,   and some more attributes. We don't see the content  of each file until we click on this double-down   arrow. With this, we get to combine the contents  of all these files. So, Power Query opens up   and gives us a sample file to take a look at. And,  by default, it shows the first file in the folder.   But, if you're not happy with the first file, you  can pick a specific file to represent the sample.   But, I'm fine with this. I'm going to keep it  dynamic and go with the first file. This is the   content of the file. I get a table view and the  page view. In this case, these are the same views,   so I'm just going to pick the table one and click  on OK. Now, Power Query is going to go ahead   and create some transform routines based on that  sample file, and then it's going to apply those to   each single file and finally, it's going to append  the results. So, all of the stuff that appeared   here was done automatically by Power Query. What I  get here is the file name, which can become useful   if you need to keep that in your final report. We  get the different columns in the file, so we have   date, properly formatted as date, first name,  last name, our text, quantity, formatted as a   whole number, and sales value is a decimal number.  And, I can update this to the currency format.   All my information is appended together.  All the steps are recorded by Power Query.   All I need to do is send this data to the  workbook. But, wait before I do that. I don't want   this column to look like this. I just want to grab  the store location from this part. So, I'm going   to double-click and update the header to "Store".  Now, to grab this part where it says "East" and   "North", let's use a transformation. Let's go to  Transform > Extract > Text between Delimiters.   My start delimiter is an underscore, but the  problem is that I have two underscores here.   I want the last one. So, I'm going to click  on "Advanced Options" and scan for the start   delimiter not from start of the input but instead  from the end of the input. So, we're going to look   backwards until we get to the first underscore.  For end delimiter, that's going to be ".pdf".   That's it. Click on OK and we've extracted  our store location. We're done here. Home   and send this to the workbook. Let's just adjust  the formatting of our table. Make this bold   and add a bottom border. Okay, so now we have  all of our information - all the 330 rows of data   from multiple PDFs combined into a single Excel  sheet. Now, what happens when I get more data?   So, I just have data until July. Let's bring  in August data. We just received a new PDF file   from our North store, so I'm just going to drag  this and drop it inside the Sales PDF folder.   Now, let's switch to Excel. I'm just going to go  all the way down, right-mouse click, and Refresh.   We should see August data pop up and it was super  fast. Everything was automatically appended.   Now, what if you want to make some adjustments to  this? What if you want to combine first name and   last name together? Well, no problem. Let's just  double-click on our consolidated query and do it   right here. Select the first name column, hold  down control, select last name, right-mouse click,   merge columns. The separator between first and  last name should be a space, and I'm going to call   this "Full Name" and OK. And let's just send this  back to our workbook, and we will have first name   and last name combined into "Full Name".  Let's also update the formatting of this.   Now, what happens if I drag and drop a file in  this folder that's not a PDF file? For example,   I have this Word file here. Let's just drag  and drop it here. When I go to my query here,   right-mouse click and refresh, I  get corrupted. I have a problem.   Obviously, the way to solve this is just to go  here and remove my Word document from this folder,   but I don't want to do that because I know I can't  really trust my colleagues to remember not to save   their other files inside that folder. What I'm  going to do is to improve my query. I'm in my   consolidated query here, let's jump back to the  source step. Here, we can see the file extension   for each file in the folder. Now we can't see  that Word file, let's just refresh the preview,   and it should pop up. It's right here. What I  want to do is to add a filter and only filter for   PDF files. So, notice I'm back at the source step,  and I want that filter to occur immediately before   any other steps are applied. So, I'm going to  click on the drop-down here, go to Text Filters,   Ends With, let's insert a step, and type in  ".pdf." Now, if you want to be on the safe side   that your extension could be in uppercase  letters instead of lowercase, you can apply   a Change Case step as well, but in this case,  I'm only expecting ".pdf" to be in lowercase,   so I'm going to apply that, and then everything  should run smoothly. Let's go close and load,   and our query updates without problems. Right  now, we might be thinking this is foolproof,   nothing can mess up this query, but not too fast,  we have another colleague who happened to put   in this Excel file. So, let's just drag and drop  it in our folder. So, now we have this Excel file   in here as well. We did foolproof this, right? So,  if I refresh this query, I shouldn't run into any   problems, but I immediately get that corrupted  message again. Why is this happening? Let's open   up our query. When I go back to the source step,  let's refresh the preview, I can see Excel there.   Once I filter it, everything is gone, but why is  that error still there? Well, remember, all of   these transformations are based on a sample file.  Originally, we selected the first file in the   folder to be the sample file, which now has become  our Excel file. So, if I refresh the preview here,   it's going to fail to load because it can't  read it properly. We need to filter out any   known PDF files at the sample file level as  well. So, we're going to go to sample file here.   Notice there is a "Source" step, and if I refresh  the preview, we can see that first file has our   ".xlsx" extension. We need to apply a filter for  extension here as well. Let's go to "Text Filters"   "Ends With". We're going to insert a step  and it ends with ".pdf" and click on "OK".   And this way only PDF files are going to show  up. So, when we pick the first file by default,   that's going to be a PDF file. So, let's just  refresh the preview, we can already see that   it's working here. Now, we can be sure that only  PDF files are included. Okay, so I've already done   a separate video that goes into a lot more detail  about importing data from a PDF file. So, in case   you run into any problems when you're importing  your PDF, check out that video. You might find a   solution to your problem there. Now, remember,  this is a feature that's available in Office   365 and Office 2021. So, if you don't have it,  that's probably why. Okay, so that's it for today.   Let me know if you've ever imported PDFs  to Excel like this and what your experience   with it has been so far. I'd love to know. As  always, thank you for being here. Thank you for   watching. Subscribe if you aren't subscribed  yet, and I'm going to see you in the next video.