Build a Livewire CSV Importer/Exporter
This episode is for members only

Sign up to access "Build a Livewire CSV Importer/Exporter" right now.

Get started
Already a member? Sign in to continue
Playing
17. Handling the import

Episodes

0%
Your progress
  • Total: 2h 18m
  • Played: 0m
  • Remaining: 2h 18m
Join or sign in to track your progress

Transcript

00:00
So to import a CSV we pretty much do the same thing as the export, the syntax is almost identical.
00:08
What we want to do though is make sure that over on whichever model we're working with we have not just an exportable interface but also an importable interface. Now just to get this working let's go and use Artisan to go ahead and make an import, so this time it's an import and we're going to call this transaction import and of course
00:30
passing the model so exactly the same process as creating an export class this time just for our import. Then inside of the imports folder I'm going to do exactly the same thing as I did for the exportable and I'm going to go ahead and create out a importable trait, again this is completely optional or an interface sorry it's completely optional and we're pretty much going
00:54
to do the same thing here so I'm just going to take this exporter and I'm going to say importer. So now we know that over on our model we can just very easily add the importable interface, add in the importer and yeah sure enough we do exactly the same thing here but we are returning the transaction import instead. So now over here using the model we can use model and then importer
01:25
now this is where inside of our import and export classes that trait that we were speaking about earlier comes in so we have an exportable trait and we have an importable trait and we'll pull that in from importable I'm not sure what's going on here I think it's just because we have an interface in here with the same name let's ignore that for now. Now that we've added importable that
01:45
means that what we can do is over in our import modal we can do importer and then we can continue to chain on something like queue so we've used two different variations of this but I just wanted to show you both just in case you decided on either. So we're going to immediately queue this we know our importer is returning that import what do we want to queue well we are just need to pass the
02:08
file in which is remember just the real path to the temporary uploaded file let's just look at this queue method actually so if we go over to transaction import open up the importable trait and look at queue we've got the file path the disk and the reader type so in terms of the disk we don't really care because we just want this to be imported let's go back over to our modal
02:33
we'll just set that to null and then we want the excel csv type in here and let's go ahead and make sure we pull that in and that's just been pulled in from here that's absolutely fine okay so now that we've got this we are pretty much now queuing the process of importing these records let's go over to our transaction import though and start to configure this so the first thing I'm going to do
03:01
is say with chunk reading so let's go ahead and do that and we will add the method stub for this which is the chunk size I'm going to go ahead and return a thousand here so I'll read a thousand rows at time when we import something and of course that's going to work nicely when we get two larger files we also want to implement the should queue interface here so we can go ahead
03:23
and queue this out we don't need to implement any methods for that and we also want to go ahead and fill in this model so the way that this works is when we import a csv for every row that we go ahead and import it will return a new instance of our transaction model so this points to our transaction model so really all we need to do is from this model method return how we want this to be created
03:53
that's pretty much all we need to do so that's why we have this two model interface here there are other ways that you can import these but I find just this is the easiest so the question is what does row can contain well let's go ahead and just dump this out and see first of all so we'll go ahead and start the import process but of course we're just going to go ahead and dump this out
04:13
so let's go ahead and import and choose a file here and let's go ahead and start the import and see what happens so that will have been sent over to horizon let's go over to should be under failed jobs let's have a look we've got q import here and did that work let's have a look here okay so we might just need to log this instead so let's go ahead and log on that row and let's
04:41
head over to our log file clear this out and let's start that import again so let's choose a file here click import head back over to our laravel log and there we go okay great so this is the first row so we know that we've got a header in here for the first item so we're going to need to deal with that as well for the second one let's just go down because this couldn't be filled and we'll
05:10
deal with that in just a second let's go down and see if there's another one if another isn't so the first thing we need to do is deal with the fact that we do have a heading row so let's deal with that first again because this package is very interface driven we have a with heading row interface that will ignore the first row in terms of this and we should get what we need so let's
05:35
clear out our laravel log let's do that again and then we should see what kind of data we're now dealing with always difficult when we are working with a package like this and experimenting with imports and stuff but we should see this roll through now so let's have a look here and let's go down there we go great so row now that we've logged it out is actually just an array
05:59
with all the data in so we just need to read row zero for the transaction id row one for the descriptor and so on and so forth so let's do that now and make sure that this gets created now there are a couple of other things we need to work out here as well so let's say transaction id row and zero and let's go ahead and do this for each of these so we've got the descriptor here
06:28
row one we've got the amount which is row two and finally the date here which is row three we'll take a look at changing this up in a bit let's just make sure this actually works first of all now what we want to do to test this import process now is actually clear out our database so for the transactions that we've got remember we've got a hundred thousand in here let's go ahead and
06:56
truncate the transactions table so there's nothing in there and then we'll try and import this and obviously we should see this filled in so let's try this now with this one which has got 10 we'll click start import of course we're not doing anything after we click start import but let's give this a refresh to make sure that this is working and it looks like it's not so let's go
07:17
over here and see what's happened so we've got undefined array key zero so actually that should be transaction id let's just try this out descriptor amount and date so we might have just logged that wrong let's try this out and see what happens now okay great so let's go ahead and import the same file again click start import this might have changed with the with headings row
07:46
let's check out our log here and see if there's anything in here and let's check out this latest one i'm gonna guess it's to do with that's the undefined key zero so it looks like it's worked let's just check our transaction model here and yeah there we go guarded to false or fillable with the columns we want to fill let's try this again and see what
08:18
happens i'm going to go ahead and clear out the lara file log as well to see this okay so let's go ahead and import a file click start import and let's give that a refresh there we go perfect so actually this has worked out really really well it's pretty annoying when you're importing stuff particularly when you're queuing to log this stuff out but this looks good we've got 10 records in
08:40
here the amount looks like it's been transformed properly using through remember we added to our transaction model the cast here for the amount so it looks like it has taken that dollar value that existed within the csv and it's transformed this into something that can be used by our database and stored really nicely the date looks good to me the descriptor and the transaction id
09:05
are good and of course the idea of the actual records have changed but that's absolutely fine because that's just internal to us so this looks like it is working really nicely there's one more thing though that's really important here now we've got a transaction id which is our kind of unique thing that we want to work with so what we want to do is enable upserts here so if we end up
09:28
with a record or a row that has the same transaction id it updates it or inserts it so as an example if i were to change this transaction id and i would go over to my editor and open up this file here and we were to find that particular transaction let's find that again in here and i were to change the amount here to say three two five seven nine what i would want that to do and of course this depends
09:57
entirely on your application but i would want that to not try and reinsert that id because it's a unique value in our database but i would want either to insert it if it didn't exist or update any of the other associated records or columns alongside of this so let's keep that three two five seven nine let's enable upserts in here so when we re-import that same sheet it goes ahead
10:23
and just updates the amount here doesn't duplicate anything obviously then we're going to get into trouble so to do this we can go once again over to the transaction import and again interfacing here we're going to say with i think it is with upserts great so with this we have a method stub called unique by let's pull that method stub in and we know that this is unique by the transaction id now
10:46
bear in mind when you are using this functionality for upserting when we went ahead and created our transactions table this has a unique index on it so that is absolutely required if this didn't have it like this it wouldn't work so make sure it's either unique or a primary key so you could have the id as the primary key and the unique by if you wanted okay so we've enabled upserts let's go ahead
11:12
and re-import that sheet that we just did it shouldn't duplicate anything it shouldn't completely replace anything but it should upsert that record where we change this value just here so let's give this a go and see if this works and then you can change this up to work slightly differently if you need to okay so that's going on in the background now let's give this a refresh
11:34
and there we go the amount specifically for this one has changed obviously we still only have 10 records in here everything looks good so feel free to change around the process of the import how the actual import works depending on the data you're working with and depending on how your application works but now we have imports working really nicely

Episode summary

In this episode, we dive into how to import CSV files into our Laravel application, which is super similar to exporting – you’ll notice the syntax barely changes! We start by making sure our model is set up for importing, just like with exporting. We generate a new import class using Artisan and drop it in the imports folder. For consistency (and reusability), we also create an importable interface or trait – totally optional, but keeps things neat if you’re into interfaces.

We set up the actual queueing process for the import, using the real path to the uploaded CSV file and specifying details like the disk and file type. To handle large files efficiently, we chunk the import to process 1000 rows at a time, and implement the ShouldQueue interface so the whole import runs in the background.

Next, we debug what a row from our CSV actually looks like by logging it out, and discover how to handle the heading row (the column names) with Laravel Excel's interface. With that sorted, we map each piece of incoming data to our transaction fields – transaction ID, descriptor, amount, and date. We do a quick check by clearing out the DB, running an import, and confirming that everything appears as expected (including transforming currencies and dates nicely through Laravel’s casting feature).

One last important feature: upserts! If you import a new row with a transaction ID that already exists, you’ll want it to update instead of throwing an error or duplicating data. We enable upserts in our import class, with a quick reminder that you’ll need a unique index or primary key on the relevant column (transaction_id in our case). After changing a row’s value in the CSV and re-importing, you’ll see the database gets updated rather than adding a duplicate row!

So, by the end of this episode, you’ve got a streamlined, reliable way to import (and update) records from CSVs right into your app, ready for whatever your application needs.

Episode discussion

No comments, yet. Be the first!