PDF Data To Zapier Tables: Use Interfaces, Tables, and Automations - Complete Guide
Let's build with Zapier and AI (100+ videos)📅 2024-01-02
Transcript
[00:00] today's video we're going to be looking how to take invoices and automatically extract it into Excel like programs so we can automate our data extraction process for our company saving us time and making us work less we're going to be using zapier tables in today's tutorial we will also be using interfaces in today's tutorial by the end of the tutorial you'll be able to get some type of user interface like this drag an invoice into it and have everything you would want relevant to that invoice automatically extracted but also we could technically extract that data and place it elsewhere within our
[00:31] business but without further Ado let's go and jump in so this tutorial can be kind of used in multiple context not just invoices so any context that you typically use data from something like a PDF a word doc and you need to extract it to an Excel like program you can use this tutorial without further Ado though this is Corbin AI y'all make sure to follow me on Twitter if you want to get updates like this on new videos stuff of that nature or just me talking let's go and jump in okay so I got the test data above me here this is just going to going to be a fake invoice the tutorial
[01:03] here though could use any type of invoice so I'm going to show you basically it could be any type of style don't worry if there's a lot of images on it whatever it may be this can still apply here so we're going to use this invoice this fake one right here as our test data here we're going to jump over to interfaces and begin let's go ahead and start off by creating our in interfaces start from scratch here and basically you're going to use this internally idea here is either if you're a one man company one woman company you're going to use this internally and basically go to this page to drag set files you want to extract data from and
[01:33] automate data extraction processes on top of that you can share this link above me to anyone in your team so everyone within your team can start using it and have an idea of the use case of this specific page but all we need to do is this we're going to add a form we're going to create a table based the based off this form on top of that let's just make it look Nic here and we'll just going to go ahead and title it uh you know invoice automation automation right perfect and then as you
[02:04] see here we got a couple stuff we can add to it so technically we can add a ton of stuff to this underlying form here but let's make it as minimalistic as possible for the context that the use case for this is purely just to drag files into it and extract data so to do so I'm going to add another field here and we're going to name this field invoice for our internal table and then the type is going to be file upload and then our label is going to be invoice as well we actually have a Max file size here so let's just go up to 10 MBS which should be sufficient in most used cases
[02:35] as you see here we got email we're going to go and change that and let's just do short text we say yes and the purpose of this we'll just say is client's name maybe we just want client's name associated with the uh the invoice but that's it just for internal reference client's name client's name and then this should be good here we can go ahead and make sure something is required so I'm going to go ahead and save I'm going to make this is required let's go ahead and make this requ required as well and that's it this is all we need to do to make our page so I'm going to click that and then it's going to bring us to our
[03:05] live page this page is live ready to go it already has a table associated with it so let's go and jump over to that table so we can go through the process of renaming this table if we choose to do so for now though we have our relevant information uh one thing that it did mess up on though is it didn't save the fact that I said short text please say yes here let's see there we go okay now it saved it so I come back over to a table here I should reload it and it should be a short text rather than an email field here there we go client name invoice so we can add a ton of stuff here if you choose to do so
[03:36] making it as simple as possible though we're going to go ahead and start here so client's name let's just say actually I think the name was like James in the invoice so we'll put James here and client's name is not valid wonder why let's see oh it's because it was uh under the impression that was an emo field so I'm going to reload this real quick again and this should work James perfect and let's go ahead and upload our pseudo invoice all we need to do is hit browse files and and upload like this okay so we've uploaded our invoice and wheels are probably turning in your head probably well there's probably other stuff we can upload in this context and do a bunch of different
[04:07] stuff when it comes automation this is true you can use this as a starting point in a lot of different contexts for a lot of different stuffs within your business but for this context all we're going to do is upload an invoice PDF in our client's name we're just submit here and this should have worked that's just an error as you'll see it pop up right here there we go and we have our relevant information so we got our client's name and we got the invoice PDF what do we do next we're going to create an automation associated with our PD our invoice column so we're going to go ahead and come down here we're going to say create zap so the purpose of this Z
[04:39] is that when a new invoice is updated that column is updated it's going to be triggered which makes sense anytime there's a submission we expect there to be an invoice associated with that submission therefore zapier's given given us a head start here so we're going to go and say updated record and zapier tables that's fine page that's the name of the actual uh table itself as you see here it's called page and come back over here continue and let's begin here so we're going to test this trigger and then that first uh fake
[05:09] submission should show up here as you see it does but we got to do a little bit magic here so if I would have proceeded with just doing files files by zapier and converted this underlying file it actually won't convert correctly due to the fact that this file has images and isn't just straight plain text therefore and because of the fact that basically probably all of our files or all of our invoices have a little bit like you know aesthetic looking that looks good we're going to have to do an alternative strategy here but it's really simple all we got to do is do
[05:41] Google Drive and what we're going to do is we're going to upload this file to Google Drive so we can convert it as well in order to do so we're going to say upload file continue here we're going to choose our courses account here continue here again and then I'm just going to choose one of our folders that we just do a lot of automations on so I'm going do this folder here I'm going to do test data now if you're having issues with accessing the file come over to your test your folder and ensure that you can share it and then basically anyone with a link actually you don't
[06:12] need that anymore actually looks like that got fixed so ignore everything I just said there but sometimes you do run into issues for other context sometimes you got do anyone a link I don't know if that's going to help anyone it may help you if you're ever troubleshooting and you're dealing with issues with Google Drive that being said though it should be fine how we have it set up now so coming back over to our automation we're going to go ahead and grab the underlying file URL as you see here it's just called URL Zoom it again and from here we're going to go ahead and say
[06:42] convert document we're going to say true it's going to take the underlying document and make it into an editable document edible edible yeah I guess that's how you say it and basically for us we can go ahead and rename it to whatever we want so in this context because of the fact that we have another column here that identified the client name we might as well add James from the row one of that column and we can just say invoice uh text whatever it may be we're going to continue here and we to test this step so what this does for us is
[07:14] this is going to appear a Google doc and the reason we do a Google doc here is it makes the text and everything on this invoice legible for software now this looks like this looks horrible but it doesn't matter because we're not going to be the ones reading this artificial intelligence is going to be the one reading this so we don't really have to care if it doesn't look aesthetically pleasing as the whole purpose of this is to get data extraction so from here once we've uploaded we can use zapier's new tool here so we don't require a code block anymore we can rather use zapier's
[07:44] tool here called files by zapier in order to grab that text from that file so we're going to do text and file then we're going to go ahead and grab the underlying file that we just uh basically uploaded and converted but here's what's important it can't be file exist but but not shown as we file text this is important y'all I got a lot of comments in my past videos having errors this is huge that text part is basically fundamental so from here we're going to continue we're going to test the step and we're going to get all the text associated with that underlying invoice
[08:16] so as you see here we got that text boom but the issue we have with this now is it is just not formatted it doesn't look good so this is where artificial intelligence comes into play in theory we could use code but for consistency at scale we're going to want to use artificial intelligence and then use code after so we're going to do conversation we going to continue here continue here and we're going to say based on this invoice we're going do semicolon
[08:47] parenthesis we do text file boom generate a line by line of the following information more to say following lead or client information so here's a couple things we got to do first thing we got to do is we got to identify and there's a bunch of ways to approach this but I'm going to approach it like this context we are and then I believe from the underlying text we are under the name
[09:19] web Cafe AI let me check there we go okay and we're going to say we are web Cafe AI [Music] ignore we are the seller of services so this is where you're going to give context of information that you do not care about you do not want to show up into the Excel we're about to push this towards due to the fact that if we got an invoice for your business you don't really need that information in the
[09:50] sheet that you're about to push it towards you only need your client information so we're going say generate line by line for the following client information it should be sufficient enough for it to understand that basically don't give any information about webcafe AI just give information about James Brown which is the client in this context we're going to go ahead and say this we're going to say name address see what else we got here email and then we'll just do one last one here and we will do amount do so there's a ton of other stuff you can do
[10:20] here whatever it may be what whatever is typically relevant with your invoices or whatever document that you're extracting data from this is where you're going to put it from here I'm going to go ahead and upper model to gbt 4 and we're going to put in a memory key we're going to try our first go at this we're going to say invoice try random 32 characters So in theory I could do this it doesn't matter but just for the sake of making it look clean we're going to do that we going to continue here and we're going to test this step so we're looking for an output here that essentially identifies James
[10:52] Brown his address his email and the amount associated with the contract so let's go and see what we got all right here we go name James Brown address perfect email perfect amount du perfect so this is everything we wanted okay really good stuff y'all now there is a couple ways we can approach this when it comes to uh text found that we don't like first way we can approach this is that we can keep proctoring chat gbt in order to ensure that the text found is just going to be James Brown etc etc
[11:24] second way is we could use code in theory and do a replace where basically uh references to these this kind of text is removed let's go ahead and first try to Proctor and then maybe we don't have to deal with code so I'm going to do parameter when you output clients information just output the details
[11:55] no um how do I say this no context of the detail we're going to say EG instead of name client name just do client name that might have been a little bit extensive there we'll put one here refresh the memory key start it fresh here continue retest app that might have thrown it off I might have gave it too much of a curveball there but let's see
[12:26] if it's smart enough to handle that kind of inquiry and it's smart enough to handle that kind of inquiry okay so now that we've done that we're going to use a formatter block so formatter block is basically their way of making JavaScript really easy for us to understand so we're going to do text we going to continue here we're going to transform this text we're going to split it you might be saying to yourself why are we splitting text what does that even mean what's going on first thing is this basically when Chad gbt gives outputs it's all one big data block
[12:57] therefore for me to separate the data for example in this context for me to separate James Brown the address the email I I can't just grab it like it doesn't come out like separate little blocks that are nice for us to look at right this is important for when you deal with Excel because the data associated with James Brown and the address want to be put into two separate columns therefore in order to make it simple on us we're going to go ahead and split the text Ander separator here and this is basically how zapier
[13:27] communicates uh their nomenclature for handling this kind of information so if you're more interested in that kind of stuff you can click this link here is going to be new line so every new line we're going to go ahead and separate these as separate fields or what I like to say is separate data points so James Brown will be a separate data point the address should be a separate data point and proceed there we go so we got everything Associated how we like it item one through five let's go and proceed with our final step here which is going to be
[13:58] the data input for our Excel sheet now that being said this could be applied to Google Sheets Microsoft Excel but the example I'm going to show today is going to be tables zapier should probably pay me because this is like straight up their Alleyway like I'm using their ecosystem from front end to back end um so we're going to go ahead and use Zab your tables just for this just for the sake of this video and what I'm going to do here is first off I need to create a table so let me create a table real quick so I'm gonna come over here I'm just going to create a new table here
[14:29] and we'll do blank table we're going to call this table invoice data create table here so if you remember from our little automation there we had a couple of things first thing we had here was a email so we're going to put email here and we'll go ahead and put email there perfect you can even add icons which is cool so in theory I could put email here so make our table more aesthetically pleasing save here and we could do that that so
[14:59] we got email and then we got the second one here which is going to be address for this one we're going to do long text yeah we'll do long text save here and then the third one was the users's name so we'll do short or just do text here I guess that's what they call it and then the last one was amount we'll come over here currency USD decimals now I'm not too sure actually we'll go with text because
[15:31] I'm not too sure if there's a conversion issue on for because we're going to be uploading text it's going to have a conversion sign already there's no maybe point in doing that so we're just do amount create and here we go so we got our four columns we got email address name amount now we can proceed so we got the table name invoice data we're going to do an event here and it should be something as simple as find or create record continue here we're going to choose a table there going be invoice data and as you see here it wants me to find
[16:02] oh actually no create record if record doesn't exist here all right so we're going to choose this option and this is how we proceed here so we've already formatted the data here and as you see if we didn't format the data it would just be one Big Blob we don't want that so we're going to come back to our formatter here and we're going to do output one sorry that is going to be the the email so that's output four and then the name is going to be James Brown the address as see two separate data blocks which isn't actually an issue I'm do a comma here and provide the second half of the address list there and then the
[16:34] amount is just going to be the 750 USD so stay tuned here as I'm going to show you how to do all of this or it's all going to happen live here so that should be situated here and then I'm just going to throw in a random thing here it obviously won't find the email as it's a unique identifier if this record shows here so actually let's just confuse it it'll never find this value so always create a record continue test step and we should create a new record here in our invoice data boom there we go maybe not as fast
[17:08] but as you see here we got test email we got the address we got James Brown and we got 7 the USD really cool publish it and let's watch this live so here here's what we're going to do it's live it's published everything works here is our underlying data record for our invoice data coming back over to our invoice automation it should be as simple as this now and just to prove to you that it works we're going to go ahead and proceed so we'll say client's name again is James I'm going to upload the same PDF here so browse files okay so when I
[17:41] hit submit everything's going to incur we're going to have it so the original data that we had from where just the name and the invoice is going to pop over so I'm going to grab that real quick all right so we're going to go ahead and just change the name to Apple so it looks different but when I hit submit this is going to update and this is going to update and all I had to do is type in apples put my invoice and hit submit so boom I see a pop here boom let's see it pop here this may take a couple seconds as it needs to upload a file convert a file extract a file format a file and pop up
[18:14] right there y'all should be the same exact data record as well due to the fact that is the exact same invoice that has been associated with this entire automation process and there you go so one thing I want to point out though is in the underlying automation process if you're having issues with the formatter block or Chad gbt outputs where maybe it comes unreliable with the email being pushed into the wrong output field and stuff of this nature just be more specific in the way you Pro your Chad gbt be very more specific As in out like basically line one should be this line two should be this do one line on this
[18:45] like more specific more proctoring when it comes to Chad gbt if you're having issues in that context but if you're not having issues in that context then we have successfully just created an automation using zap your tables zap your interfaces and basic basically the whole shabam in order to extract data automatically when it comes to PDF so if you felt like you learned something make sure to leave a like it's completely free and helps me here at Corbin A if you like this style video and you want to see more videos like this check out the playlist at the end here called zapier and open AI approaching all 5,000 apps ton of stuff you can learn when it comes to Automation and specifically
[19:16] automation of artificial intelligence but without further Ado y'all I'll see you in the next video thanks for tuning in and yes surprise I'm an AI Avatar make sure to explore more here at Corbin AI where we demystify AI for your personal and business life until next time