- 2 months ago
Take my Full Databricks Course Here: https://www.analystbuilder.com/courses/modern-data-workflows-in-databricks
Try it for Free Here: https://bit.ly/aa-dbxfree
Get the File here: https://github.com/AlexTheAnalyst/DatabricksIDP/tree/main/final_project
IDP in Databricks is used to help speed up the process of transforming unstructured data into structured, usable data. It's not an easy process, but IDP makes it much easier!
IDP Documentation: https://www.databricks.com/blog/pdfs-production-announcing-state-art-document-intelligence-databricks
____________________________________________
RESOURCES:
💻Analyst Builder - https://www.analystbuilder.com/
📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr
📖Take my Full Python Course Here: https://bit.ly/48O581R
📖Practice Technical Interview Questions: https://bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: https://coursera.pxf.io/5bBd62
Data Analysis with Python - https://coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR
Tableau Data Visualization - https://coursera.pxf.io/MXYqaN
*Please note I may earn a small commission for any purchase through these links - Than
Try it for Free Here: https://bit.ly/aa-dbxfree
Get the File here: https://github.com/AlexTheAnalyst/DatabricksIDP/tree/main/final_project
IDP in Databricks is used to help speed up the process of transforming unstructured data into structured, usable data. It's not an easy process, but IDP makes it much easier!
IDP Documentation: https://www.databricks.com/blog/pdfs-production-announcing-state-art-document-intelligence-databricks
____________________________________________
RESOURCES:
💻Analyst Builder - https://www.analystbuilder.com/
📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr
📖Take my Full Python Course Here: https://bit.ly/48O581R
📖Practice Technical Interview Questions: https://bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: https://coursera.pxf.io/5bBd62
Data Analysis with Python - https://coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR
Tableau Data Visualization - https://coursera.pxf.io/MXYqaN
*Please note I may earn a small commission for any purchase through these links - Than
Category
📚
LearningTranscript
00:00What's going on everybody? Welcome back to another video.
00:02Today we're building our full project using IDP and Databricks.
00:12Now I'm super excited for this project because this is a really great use case
00:16for using IDP.
00:17We're going to be working with financial data and we're going to have invoices,
00:20purchase orders, and receipts.
00:21I'm going to be dumping them into a catalog within Databricks.
00:24There's going to be different data that we want to extract from each document.
00:28And so what we're going to do is we're going to parse it out using AI parse document.
00:31After that, we'll want to extract the data that we want and put it into an actual table.
00:36I'm going to show you how to do it.
00:37It's super easy to write the code, but I don't want to put all the data
00:40from all these different files into one table.
00:42So we're going to use AI classify to kind of group these and categorize these.
00:46So we know what data to put in each table.
00:49At the very end, we'll drop in some more files.
00:51We'll run through the entire process and you can see how now we have this entire system set up
00:55where you just have to upload the files, run your code,
00:57and you have your new data within your tables.
01:00This is a fantastic use case, not just for financial data,
01:03but basically any type of data that you'd want to do this with.
01:05And so I'm really excited to show you how to do this.
01:08Let's not waste any time.
01:09Let's jump on my screen and get started.
01:11All right. So let's come over here to our catalog.
01:14And of course we've been working within this IDP and this YouTube lesson.
01:19Now we don't want to continue in the YouTube lesson.
01:21We want to create a new one.
01:22So we're going to come over here.
01:24We're going to go to create.
01:25We're going to create a new volume.
01:26And we're going to call this one our final project.
01:30And this is for all the marbles.
01:32You're going to build out this project.
01:33You're going to go tell your mom about it.
01:34You're going to be really excited.
01:36Let's go browse this.
01:37Let's select our files.
01:39And right in here, we have all these invoices.
01:42We have a lot of different purchase orders and we have different receipts.
01:45And within our new files, these will be the files that we actually drop in.
01:49After we've completed everything, we're going to drop in extra files,
01:52run through the process again.
01:53So we're not adding these new files in, but we're going to add in all of these files
01:58right here and we're going to open them up.
02:01We can go ahead and upload these and they should just upload really quickly into our volume right
02:07here.
02:08Now let's go take a look at one of these files just really quickly, just so we can see what
02:12it looks like.
02:13And this is opening up in Microsoft edge looks like there's some text overwrite.
02:19That must be a Microsoft edge thing.
02:21I don't know, but this is what our invoice looks like.
02:24And we're not going to go and look at each one right now, but we will in a little bit,
02:27but we can pull out as much or as little information as we want.
02:31If we just want to pull out the invoice ID and maybe the total amount for each invoice,
02:38that's all we want to do.
02:39Sure.
02:39Why not?
02:40Or if we want to include, you know, their address and all this other information, we can.
02:45In the past several lessons, we've shown how to get data out of tables like this.
02:50And so if we wanted to pull all of this data in and see each of the kind of items
02:54that we had,
02:55we can do that.
02:56For this lesson, because this is a lot of different types of files,
03:00we're probably just going to stick with some basic information.
03:04And then we're going to show how we can extract that data and then put it into a table.
03:07So it's a lot more usable than just sitting in our files.
03:10So this is our invoice.
03:12And all we're going to do is we're going to start with creating a new workspace.
03:17And I actually need to come up here.
03:18Let's just create a new notebook.
03:21And I do want to have it in SQL.
03:23I'm going to rename this as our final project, just in case we need to come back to it later.
03:29But the first thing that we need to do is just read in our files.
03:32That's all we need to do.
03:33So we're just going to say select everything.
03:36And then we'll say from, and then we'll do read underscore files.
03:40And we need to put in our file path here.
03:42Now this should be in quotes, forward slash volumes, forward slash IDP, forward slash default, forward slash final project.
03:52Let's go ahead and run this.
03:54So now we can see all of our files, the modification time of the time that we should put it
03:59in there,
03:59the length.
04:00So we see that there is data in there.
04:02And then we have our content.
04:03Now we haven't used AI parse document yet.
04:06So it's just not reading it in properly.
04:08This isn't the real content.
04:09But now we can see we have all of our files in here.
04:12We have our invoices, our purchase orders, and our receipts.
04:16Now, what we're going to want to do is we're going to pull out and we're going to parse this
04:21first.
04:21And then we're going to classify, and then we're going to extract everything and place it into the tables.
04:27And so what we need to do is come right down here.
04:30And we're going to start out by parsing out the content.
04:33I will be adding it to some kind of temporary tables as we go, just so we can keep using
04:38them in later queries.
04:40But let's start out by just saying select.
04:42And we're going to select the path.
04:44And that's going to be this.
04:46And let me go up.
04:47That's going to be this path right here.
04:48And let's read this in.
04:52So I'm going to say from this volume.
04:54And then I just want the content.
04:55So I'm going to do AI parse document.
04:59Then we'll open this up.
05:01And we're going to put in the content.
05:03And we'll put this as parsed content.
05:07Now, let's go ahead and run this just to make sure it's working properly.
05:10And then we'll put it into that temp table so that we can use it in future queries down below.
05:15Now that we've parsed out these documents, we can come in here and let's actually go over just a little
05:22bit.
05:22We have a lot of data in here, right?
05:25You can see we have tables just like we had in the previous lessons.
05:30We have tables of data.
05:31If we wanted to pull that out, we could.
05:33We're not going to in this lesson, but we know how to do that from previous lessons.
05:37But we have all of our invoice data, all of the website information as well as the total information right
05:44over here.
05:44So we have our total.
05:46And this is the kind of data that we'll be pulling out from each one of these.
05:49And so you can see we got all that from all of our different files.
05:53We parsed out the content.
05:54Let's actually create our temp table because I want to kind of store this so that we can use it
05:58later.
05:58So we're going to say create or replace and I always just do that kind of as a default because
06:04if I run this later and it's already stored in there, I want it to replace it.
06:08Otherwise, I'm going to start getting errors saying this has already been created.
06:11And so I just do that by default, knowing that later I'm going to come back and use it again.
06:15So I'm going to create a table and we'll call this parsed underscore data and then we'll say as here.
06:22So now when we run this, we can reuse this parsed data table that we are creating within this query
06:28right here.
06:29Now, it says no rows returned and that's okay because we're doing a create or replace table here.
06:33If we just come right down here and run it, you'll see it worked properly.
06:38That's just kind of a default output that you're going to get when you create these tables.
06:44Let's just select everything from this table and you'll see it's all there.
06:49Now, while that's running and okay, there it is.
06:53But while that's running, this data right here is in a perfectly fine state,
06:59but it can be a little bit difficult to kind of look through this and know what we want to
07:05pull out.
07:06And I actually think it'll be a little bit easier just for us to be able to see it.
07:09We can kind of loop through this and make it a little bit easier to read.
07:13I'll write out the code.
07:15I may even use AI to help write this.
07:17We're basically going to clean this up before we actually classify it.
07:20And in fact, cleaning it up a little bit might help the classification anyway.
07:24And so we're going to kind of clean this up and then we're going to classify it next.
07:28So let's come right up here and I'm going to start writing it out.
07:32We'll see if AI picks up on it while we're writing it because it often does,
07:35especially within Databricks.
07:37But let's come up here to our select statement.
07:39So we're going to have our path.
07:40We'll put a comma here.
07:41And then we're going to use concat underscore WS.
07:45We're going to put forward slash N.
07:47That's just our new line.
07:48That's going to be our separator.
07:49Then we'll put a comma here.
07:51And then what we want to do is basically take out each of these elements.
07:54And so we're going to go in, we're going to take the elements,
07:56and we're going to transform them just a little bit.
07:58It's nothing crazy complex.
07:59I'm just going to write it all out and kind of talk as I go so I can get it
08:03all written down.
08:04So I'm going to say transform.
08:06And then within this transform, we're going to say parsed content.
08:10So that's this column right here.
08:12And then we're going to do a colon.
08:14We'll say document.
08:15And then we'll do another colon and say elements.
08:18And I need to spell that right.
08:19And that's just right here, our document and then our elements.
08:22And then I'm going to say as array.
08:25And then we'll do this right here, which is a variant, just like that.
08:30And of course, I spelled transform wrong, transform.
08:34There we go.
08:34Once we specify our data, we'll do a comma here.
08:37And then we're going to do something like this.
08:40So we're going to say coalesce.
08:42And then we're going to try to cast this as a string.
08:45So we're going to say try cast.
08:47And e-content as string is exactly what I want.
08:51And then we'll do a comma and put our quotes right here.
08:54We'll enter down.
08:55This is basically everything we want.
08:57And we'll say as doc underscore text.
09:00Let's try to run this.
09:02Now, the syntax I might just be getting wrong.
09:04Let's try to diagnose this error using the AI helper here.
09:10Or the AI assistant, I believe is what it's called.
09:12And it's saying we don't need to put it as an array.
09:15Let's just accept it and try to run it.
09:17And let's see if it works.
09:18And again, we're getting an error here.
09:20Let's try to fix this.
09:22In the error, it's saying I need it as an array variant.
09:24I think I just wasn't using the right, maybe the right, what are these called?
09:30Less than or equal to.
09:31This is the exact syntax as I had before.
09:34But maybe with kind of the right thing right there.
09:37And that's what it was.
09:38I was just using the wrong symbol here.
09:40But this is exactly how I had it written before.
09:43So thanks, AI.
09:44It looks like I had some syntax error in there.
09:46I wasn't using the right symbol or character right there.
09:49But now if we look at this doc text, it looks very different, right?
09:54This looks very human readable.
09:55And when we go to classify this, we don't have to do it this way.
09:59I just prefer it when I've been using it.
10:02I have been doing it like this.
10:03And so it makes it a little bit more easily readable.
10:05And then you can see, okay, here's our table.
10:07This is with our data in it.
10:09Here's another table with our data in it.
10:11And we can go down here to like a purchase order.
10:13We can see we have a bunch of table data right there.
10:17And then in here, we have some kind of header information.
10:20And all this is doing is putting it on a new line.
10:22We're transforming it, basically taking out each element
10:25and then placing it as a string on a new line.
10:28So now this looks ready to go.
10:30I think we're ready to classify this.
10:32And in fact, let's set this as a temp table
10:36or just as a table that we can use.
10:38So we're gonna say create or replace table.
10:43And let's call this one pretty underscore data.
10:48And we'll say as.
10:49So we're gonna set this one as pretty data
10:51just because it looks a lot better.
10:53And we're taking out a lot of kind of the unnecessary characters
10:56that we don't really need.
10:58And so now we're gonna take this pretty data
11:00and let's just confirm it's there.
11:02So we're gonna say select everything from pretty data.
11:06Let's go ahead and run that.
11:07Now what we need to do is classify this information.
11:11So we're gonna come up here.
11:12We're going to take everything,
11:14but then we'll add a classification to this.
11:17So we're gonna say AI classify.
11:19And then we need to pass through
11:21what we want it to classify it on.
11:22So of course, I'm just gonna do a tab here for the doc text.
11:26It's telling us to do these, but those are terrible.
11:29Let's write array here.
11:31And within our array, we want it to be either an invoice,
11:34a purchase order, or a receipt.
11:36So let's write that out.
11:36So we'll say invoice, purchase, order, a receipt.
11:44Am I spelling receipt right?
11:45R-E-I-P-T.
11:46Okay, great.
11:47And then if for whatever reason there was an other,
11:51we'll put an other there.
11:52I don't think we'll need it, but that's fine for now.
11:55And we'll call this as doc underscore classification.
12:01Let's go ahead and run this.
12:03Let's see how it looks.
12:04Now let's scroll down and let's go over.
12:07And we have all these classifications on the far right.
12:10Let's just bring this over.
12:11So now we can see we have invoices and that looks correct.
12:15We have our purchase orders and those also look correct.
12:18And then we have all of our receipts.
12:20Now you have to remember, we're doing this classification based off of the information
12:24within it.
12:25We're not doing it based off of whether it says invoice right here or not,
12:29because sometimes this invoice might just come in as a number,
12:3210119.pdf.
12:33And so we want to actually look at the contents of the document.
12:36And so we looked through here, we parsed through, we said, okay, this is for sure an invoice.
12:41And that was of course with the AI classifying.
12:43And so that looks good.
12:44It doesn't matter what documents we drop in later,
12:47even if it's not labeled correctly with the PDF or with the actual title,
12:51it's still going to read in the contents and classify it properly.
12:54But this table right here has everything that we need.
12:58We have our data in it.
13:00We have our classification in it.
13:01And what we can now do is we can use this classification to filter down which ones we
13:07want, extract that data and put it into a table.
13:10So we'll start with invoice.
13:11We'll say, let's extract the data we want from invoice, place it into the invoice table.
13:16Then we'll filter on purchase order, extract the data from the purchase order,
13:20and put that into a separate table.
13:21And then we'll do the same for receipts.
13:24So let's place this into a temp table, just like we've been doing.
13:28We'll say, create or replace, and we'll say table.
13:32And we'll do this as a classified data, or we'll call it classified data.
13:38It sounds like we're a secret agent over here or some spy agency,
13:42but I assure you we're just classifying using AI classify.
13:45Although I feel like a secret agent.
13:48Let's just confirm that this is in here properly.
13:51So we'll say select everything.
13:53And then we'll start filtering on it.
13:55And then using AI extract to extract our data.
13:57Let's come down here.
13:59Let's go over.
14:00And this looks great.
14:02So here's what we'll do.
14:03We're going to start really simply.
14:04We're just going to say where the doc classification is equal to invoice.
14:10And let's run this.
14:13So now we're just filtered on our invoices.
14:16So with just this data, now we want to use AI extract to pull out the values we want.
14:21And then once we have it done for one of these file types, the invoices to start,
14:25we're going to be able to basically copy down and just alter it a little bit.
14:28It's going to be very quick.
14:30So let's come up here.
14:31We're going to select everything.
14:33We're going to do a comma here.
14:34And now we're going to use AI underscore extract.
14:38We do want to pass through both the doc text and an array here.
14:41So I am going to keep that, but I'm going to kind of format a little bit better.
14:45And within this array, we need to specify what we're pulling out of the document.
14:50We can go to our raw document if we want.
14:53So we can come up here, just look at one of these invoices.
14:56Some of the data that I want to pull out is things like invoice, invoice ID, the due date,
15:01maybe the payment method.
15:02And let's do the total down here.
15:04Let's also pull out who it is.
15:06Now there isn't an actual title for this.
15:09It doesn't say that this is the vendor name.
15:11I'm going to see if it's able to pick this up.
15:13I think it should.
15:15So let's come down here.
15:17Let's do vendor underscore name.
15:21And it's going to do a lot of autocorrect here, but I'm going to do vendor name.
15:26Let's go right down here.
15:28Let's do the invoice number.
15:30So within our quotes, we'll say invoice underscore number.
15:36Then we'll do a comma.
15:39We'll do the invoice underscore.
15:41I need to spell that right.
15:42Invoice underscore date.
15:45We'll go to our next one.
15:47We'll do the due underscore date.
15:51Let's come down here.
15:53Let's also get the payment method.
15:55So we'll do payment underscore method.
15:59And then lastly, let's get our total.
16:02And that's at the very bottom, right?
16:04We have this total right down here.
16:06So this is a little bit more difficult.
16:08We'll see if it's able to extract this.
16:10It absolutely should.
16:11I've used this quite a bit.
16:12I'm almost certain it's going to work very well.
16:16But now we need to close out this parentheses.
16:18Let's say as extracted.
16:22And let's go ahead and run this.
16:24I just noticed I actually did space invoice date.
16:27I'll correct that, but I won't run it again.
16:29But let's go, let's pull this down a little bit.
16:33Let's bring all this over.
16:35And let's look at this extracted information.
16:39Now it's saying null for the invoice date.
16:42And that may be because I actually wrote it wrong.
16:45Let's try running this again.
16:46I think that space may have thrown it off.
16:49Just because I messed it up on my end.
16:52All right, let's come over here.
16:54And that's what it was.
16:55So I had, I had that extra space there.
16:58Just threw it off.
16:59Let's come through and just make sure.
17:01It looks like these look correct.
17:03We can even pull up this document really quick.
17:05So let's pull up that 10119.
17:09We'll pull it up on this side.
17:12Just to see if it's extracting it properly.
17:15So it did pull in the vendor name without any specification.
17:18This doesn't say vendor name.
17:20So it's great that it pulls that out.
17:21So we have marketing, Riverbend Marketing Studio.
17:24There's the invoice dash.
17:28Yep, that one looks correct.
17:29Invoice date, 117.
17:31Due date, 117.
17:32Payment method is the wire transfer.
17:34And then the total doesn't look correct.
17:37That's because I didn't put a comma here.
17:40And that's pure user error.
17:43Let's run this again and see if it gets the total right.
17:46Because that's the one I actually think is gonna be
17:47the most impressive for it to pull out.
17:50Simply because it's in a table already.
17:52And so let's run this.
17:54Let's come over here.
17:56And let's see if it took the total right.
17:58So 456.79.
18:01And everything is correct.
18:02So just a little fixing of this array right here,
18:06purely because I messed up a few times.
18:08But now that we have this all good to go,
18:11now what we can do is we can pull out all this data,
18:14put it in its own columns and its own rows.
18:17And then we can place that data into a table.
18:19And that's actually quite easy.
18:21We're not copying.
18:22Let me actually place this.
18:24So I'm gonna say create or replace.
18:28You guys know the gig.
18:28I'm gonna call this invoice data.
18:31And we'll say as.
18:32And I need to say table here.
18:34So we're creating this invoice data table.
18:38And that's where we're then going to just kind of pull out
18:40the final values into its own columns and rows.
18:42We actually looked at how to do this in the last lesson.
18:46Let's go ahead.
18:47We're gonna select everything from here.
18:50And all we're doing is we're specifying real quick.
18:55Let's come over.
18:56We're just gonna take this extracted information.
18:59So let's come up here to the select.
19:01Let's just do the path.
19:02And then we'll come into each value.
19:04And we're gonna select it
19:06and put it into its own column, essentially.
19:08So we'll do extracted dot.
19:11And then it even says what we can do here.
19:13So we're gonna take out the vendor name first.
19:16And I'm gonna hit tab to auto do a little bit of this.
19:19And due date and payment method.
19:20I'm gonna hit tab as well.
19:21And the total.
19:23It's just auto correcting this all for me.
19:24And then I'm just gonna rename these.
19:26Because I want the actual column headers to be correct.
19:29And we'll do vendor.
19:31We'll do as.
19:33And that's invoice.
19:36Let's call this invoice number.
19:39And then we'll do as invoice date as due underscore date as payment underscore method.
19:49And then last one as, and it should just be total.
19:54Now let's go ahead and run this.
19:55Let's make sure that this is all working properly.
19:58So now we have within each of our documents, we have our vendor name.
20:03Let me pull this down just a little.
20:04We have our vendor name.
20:06We have our invoice number, the invoice date, due date, payment method, and the total.
20:11And this all looks perfect.
20:12We don't have to pull in this path and put this data into it.
20:16But we can if we want to.
20:18But all we have to do is come right up here.
20:20And we're gonna do almost the same thing.
20:22We're gonna say create or replace.
20:24Now this is probably the most important one to do this on.
20:27Because we're actually gonna say a table.
20:30Now we're gonna do IDP dot.
20:32And all this is gonna do is gonna specify where in our catalog to place this data.
20:37Now we have to place this into an actual catalog.
20:41We can't just place it and call it here.
20:43We're gonna create this in just a sec.
20:44We're gonna call this finance dot invoices.
20:47We don't have this finance table yet, right?
20:52We have to create this.
20:53So I'm actually gonna come up here and I'm gonna create this.
20:56So I'm gonna say create schema if not exists and we're just gonna call it finance.
21:03Now we just have to specify where it goes.
21:05So I'm gonna say IDP dot and let's run this.
21:09And that should create it.
21:10Then we're gonna come down here.
21:12And now we're selecting all of this data into this IDP finances.
21:18And then we're labeling it invoices.
21:20So now we're gonna say as, and this is where it's gonna go.
21:23Let's go ahead and run this.
21:25And then let's go and check our catalog to make sure it got in there properly.
21:29So it looks like this worked well.
21:31Let's come over here and we're gonna go to our catalog.
21:35We're gonna go down to our IDP.
21:37And now we have a new schema.
21:39We have finance.
21:40And then within it, we have our invoices.
21:42So now this is sitting just like a regular table would if you uploaded like a CSV file
21:48or something else, except we're working with an unstructured data format.
21:52This is not easy to pull this data in.
21:54And so we now have basically this pipeline where we can drop data in.
21:58Let's actually select our compute real quick and start this just so we can see the data.
22:03But we can select this data in.
22:05We can bring this data in.
22:07We can extract the data and place it into tables just like regular data, which is amazing.
22:11Now we can wait for this to show our sample data, or we could just go back to our workspace
22:17and query this data from this actual table now, which it's reading it in.
22:25Let's go to the very bottom.
22:26It's actually there.
22:28So now we can do code and we can say select everything from idp.finance.invoices.
22:37And let's read this in because now it's just sitting like a regular table.
22:42So now this data is kind of permanently stored within Databricks, just like we would if we were
22:48uploading a CSV to a table, which is fantastic.
22:51This is awesome.
22:52And we have some documentation with the path.
22:54If we wanted to, we could have even included things like a timestamp here of when the data
22:59was here.
22:59But you know, that goes beyond what we're trying to look at in this lesson.
23:03Now what we're going to do is we're just going to repeat this and do the exact same thing,
23:08but for the other type of data.
23:10So let's come and bring this all the way down here.
23:13It's going to be really similar.
23:15Let's just comment this out.
23:17And now instead of invoice, let's do our purchase.
23:22I think it's like this.
23:25Maybe I need to go back.
23:27Now, actually, while that's running, let me go back up to our classification.
23:33It's right up here.
23:36Let me get rid of this just for a sec.
23:38We want to revert this code back because we'll be running through this all again.
23:43But I just want to see what I called this.
23:45Oh, I called this a purchase space order.
23:48Okay, that's totally fine.
23:49I just wanted to make sure.
23:51Now let's go back down.
23:52Let's get our purchase space order.
23:56Now, these are not the things that we need from our data.
24:00And let's actually go into one of our purchase orders and take a look at this.
24:05So we have our buyer.
24:06We have our vendor right here.
24:09And it looks like we have some other information like currency.
24:12We have a date right here.
24:13And then we have our total.
24:15So let's pull out a few different things.
24:18Let's pull out our merchant name.
24:19Let's pull out maybe our PO number right here, as well as our PO date.
24:25And then we'll do our total amount just like we did before.
24:29I think these are all good kind of key things that we want to pull out.
24:34Let's get out of here.
24:35Let's come right here.
24:37Now, we're going to call this a merchant name.
24:39We want the transaction date.
24:44And we'll do it just like that.
24:46And I spelled that wrong.
24:48Let's actually pull up this one more time.
24:52Because we have the PO number, the PO date, and the total.
24:57So let's get that PO date.
25:01Purchase order date.
25:03And then we'll do the total.
25:06And let's get rid of these.
25:08There we go.
25:09And I think we just need to close out this parentheses.
25:13Now, let's just look at this first.
25:16Let's make sure that it's running properly.
25:19And then once we've verified that, we'll just continue on with the process like we did
25:23in the previous one.
25:25So let's come over here.
25:26Let's look at our extracted information.
25:29So we have transaction date as null.
25:32That's not good.
25:33Let's just see if I need to pull it as something else.
25:35But then we have our purchase order date.
25:37And I think the transaction date is the same thing as the purchase order date.
25:42I actually don't want or don't need both of these.
25:46I only need one.
25:47Let's come over here.
25:48Because we have the purchase order date, which is this information right here.
25:52I wanted the PO number.
25:53That's what I need.
25:54So let's actually replace this with the PO underscore number.
26:02And let's run this.
26:04The best thing I love about this is just that it knows kind of what I'm saying.
26:08I don't have to go and like hard code in anything.
26:11It uses AI to go in and extract the information that I need.
26:15So this is looking great.
26:18And let's verify this.
26:20Let's get the Hawthorne 25 document.
26:24So Hawthorne 25.
26:26Let's get rid of this.
26:28Let's come over here.
26:30So we have Riverbend Marketing Studio.
26:33We have the PO 24371.
26:38We have 20, 25, 1, 6.
26:41And then we have 5,219.45.
26:43That is perfect.
26:45So I think we are rocking and rolling with this one.
26:49Now we just need to create this.
26:51So we're going to create this as purchase order data.
26:57And let's run this.
26:58And then what we have to do is extract each value just like we did and place it into a
27:02table.
27:03So let's copy this.
27:04I don't know if I did that before.
27:07Let's do select everything from this table.
27:11And then we'll start extracting each value.
27:15And so we're going to do it just like this.
27:19We're going to select the path and then we're going to do extracted dot.
27:24And then we'll start pulling out each one.
27:26And let's see if it does this for us.
27:28There we go.
27:29This is perfect.
27:31It just tabbed it and kind of knew what I wanted at this point.
27:34Let's just run this just to make sure it gets everything right.
27:39And there we go.
27:40That's perfect.
27:41And let's place this into a new table, which I'm going to create.
27:46So we'll come back up.
27:48We will take this right here.
27:51And actually, we don't because that's the schema.
27:54We just have to create the table.
27:57That's it.
27:57So let's come back down here.
27:59Look, some of this I'm doing, you know, just out of memory.
28:02So I apologize.
28:04I'm going to call this purchase underscore order.
28:08And we have our purchase order.
28:10That's going to be our new table.
28:11Let's go ahead and create this.
28:14And then we'll go check it out.
28:15And then the last one we'll do is receipts.
28:17And then we'll do kind of where we put all of our new data in as well.
28:22Looks like this should work.
28:23Let's go to IDP, go to finance.
28:26And now we have our purchase order in here.
28:29I'm looking at sample data.
28:30We don't need to, but here's our data.
28:33So that's our sample data, but it is all of our data.
28:35But if we had thousands and tens of thousands of files, then, you know, this would just be a small
28:40sample.
28:40So this looks perfect.
28:42We are just cruising through this.
28:44And the very last one that we're going to do is our receipts.
28:48Let's scroll down.
28:50Let's, well, that was our purchase order.
28:53Let's go right here and pull this right here and put it at the bottom because now we just need
28:59a filter on receipts.
29:01And so we'll say receipts.
29:04And I never know if I spell that right.
29:06It's I before E except after C.
29:07Hey, that's kind of the one time it actually works.
29:10All right, let's go down to a receipt right here.
29:13And within this receipt, we got some good information.
29:16One, we have the name of, you know, the parking area.
29:19We have the receipt number, the date and time, and we have a total.
29:23Now, again, all these totals have been within these tables and it's extracted them very well,
29:29which I've been really happy about.
29:30We could also pull the payment method.
29:31We can kind of pull anything we want from these.
29:34I'm just going to try to keep it kind of simple.
29:36But let's come back here because now we need receipts.
29:40So let's come in here.
29:42We'll just keep this as the merchant name.
29:45Let's see what else we want to pull.
29:49Let's do the receipt number instead of the PO number.
29:52So I'll do receipt number.
29:54Let's go back.
29:55Let's just do transaction date.
29:57That's what we're going to call it.
29:58We'll see what it extracts exactly.
30:00And we'll do the total.
30:01So we'll do here.
30:02We'll do transaction date.
30:06And let's go ahead and run this.
30:08Now, which one are we looking at here?
30:10We have the 4115 one.
30:13I'm going to pull this over and do it just like this real quick.
30:17I really shouldn't be doing that.
30:18And in fact, let's get rid of this, even though I already created it.
30:22Yeah, this doesn't look as good.
30:23I'm going to revert back.
30:25You know what?
30:26I'm doing it.
30:27I'm going back.
30:28I'm going to do it like this.
30:29And it looks like we didn't extract anything.
30:32Let's go back up.
30:33This is just part of the process.
30:35All right.
30:36Sometimes we've got to figure out what happened here.
30:38Let's go back up.
30:39We called it receipt.
30:40That's why it's sometimes it's the simplest thing.
30:44I called it receipts here, but it's we filtered on the wrong thing.
30:48Let's go ahead and run this and we should get an output this time.
30:52And there we go.
30:53Let's go over to this right hand side and let's find the 41551.
30:58That's this one.
30:59Let's just see what data it pulled out and if it looks correct.
31:04So let's look at this.
31:05We have a skyline parking, R347861, 201513.
31:11That's just the transaction date.
31:12We didn't do transaction date and time.
31:14We probably could have.
31:15And then we have our total of 288.16.
31:19Perfect.
31:20This is exactly what we wanted.
31:22Now we already created this by accident.
31:25So let's just come down here and we're going to make sure it's in there.
31:31Select everything from this table.
31:33We'll extract the specific things that we want.
31:35Let's run this again.
31:37Because again, I forgot we had receipts before.
31:40We do have to run this actually.
31:42Because before we had receipts, we put no data in this table.
31:46And so it didn't actually work.
31:49But now it should work when we do this.
31:52That's good.
31:53Let's go down and run this again.
31:55We should actually have data in here.
31:57Thank goodness.
31:58All right.
31:58Now we just have to do exactly what we did before.
32:01We're going to take just the path.
32:03Do a comma.
32:04We'll do extracted dot.
32:06And let's see.
32:07Oh, I need to do extracted.
32:08Extracted dot.
32:09And I'll do tab, tab, tab.
32:13And let's see if it does it.
32:15There we go.
32:16Thank you, Autocomplete.
32:17What would I do without you?
32:18I love Autocomplete.
32:20It's great.
32:21We have merchant.
32:22We have the receipt number, transaction date, and the total.
32:26This looks perfect.
32:27Let's go ahead and place this.
32:29I'm just going to go back and copy.
32:31I could just copy this.
32:34Because then we'll place it right here.
32:37We'll call this idp.finance.purchaseorders.
32:46And let's run this.
32:47Looks like it ran well.
32:49Let's go back to our catalog.
32:51We've got idp within our finance.
32:53We now have.
32:54And I put purchase orders.
32:56Oh, goodness.
32:57This is receipts.
32:59Oh, geez.
33:01What a mistake.
33:03What a mistake.
33:04I'm going to have to delete that table now.
33:05Oh, my lanta.
33:07All right.
33:07This is not purchase orders.
33:09I had done its receipts.
33:12And I called it purchase orders on this one, too.
33:15Oh, I should have done receipts here.
33:18Uh, geez.
33:19All right.
33:19I'm fixing this because in just a sec, we're going to drop all the files in there.
33:24I want everything to actually be correct.
33:26You can save this and use this later on.
33:29So we're going to fix it because, you know, that's what we do.
33:31We make mistakes.
33:32We fix them.
33:33And then we call that receipts and from receipts.
33:38Perfect.
33:38Now let's run this because now we just created this receipts table.
33:43And we're placing this in here in our catalog.
33:46We should now see we can get rid of this purchase orders.
33:50And I'm just going to come over here and delete this real quick.
33:55Just because that was an accident.
33:56Now we can go into receipts.
33:58And if we look at our sample data, our data should be in there.
34:02And that is perfect.
34:04Now, this is not just a one-time use for this code.
34:07We can use this as much as we want.
34:09Let's go into our default because this is where we're storing our data.
34:13Let's go to our final project.
34:15And let's upload.
34:17I'm going to go to browse.
34:19Let's upload these new files.
34:21Now, these new files are very similar, but they are not the same.
34:25Let's upload these.
34:28And now we have a bunch of new data in here.
34:31All we have to do, because we just put like 15 new files or 10 new files in there.
34:37All we have to do now is come over here and run all.
34:41That's it.
34:42So all I'm going to click is run all.
34:43It'll run all of our code down to the bottom.
34:46And we have, I guess, 13 different cells that we use.
34:49It's going to run all of these and then it should work.
34:52Now, real quick, I want to make sure fix error here.
34:55Let me make sure that we don't have an error message somewhere.
34:56I think that's just some AI labeling thing.
34:59All right, let's run all and let's see if it works.
35:03This should take a minute or two.
35:05And then we'll have all of our data completely classified and put into our tables.
35:09All right, that took about one minute to run.
35:11It looks like everything ran successfully.
35:14Let's go back to our catalog.
35:19Let's come in here and we have all of our tables.
35:21And now let's look at our sample data.
35:25And we have a lot more data in here now.
35:27And it looks like everything worked perfect.
35:30Let's go to our purchase order.
35:35We've got some more data in here now because we only have three of four.
35:38So we had two more.
35:39And then let's look at our receipts and there's our new data.
35:43And there we go.
35:45I have done similar things to this in the past in real work,
35:48using, you know, PDFs and all sorts of different documents.
35:51It was 10 times harder than this.
35:53This is really just awesome to use.
35:55It's a fantastic tool.
35:57And if you have to do something like this,
35:58this is like the first place that I would go.
36:00I really hope you enjoyed this project.
36:02I thought it was super fun.
36:04I've done stuff like this in the real world in the past.
36:06And IDP just makes it really easy within Databricks.
36:09I wish I had this many years ago when I was doing this kind of work.
36:13Thank you guys so much for watching.
36:14I really appreciate it.
36:15If you like this video, be sure to like and subscribe.
36:18I'll see you in the next video.
36:30Bye.
36:31Bye.
36:31Bye.
36:32Bye.