hey what's up I'm back with another video to level up your larval skills in today's video we are going to learn how to upload really big big CSV file into database so the problem is if you got really big like 30 thousands line of CSV files which we in this case we are uploading we are going to upload this file with God really more than more than 35,000 lines so if you try to import this CSV into your database and at the same time if you have to check if there is already record and if there is no record like for example this policy ID so this is just a simple file so you need to check if this policy ID already existing database if not then insert so while checking and doing all the import tasks it will use so it will use memory and and once it move up to 30 second timeout then you won't be able to upload other recourse it will only upload so we will see the problem and will solve it in this video so hope you are excited let's get started so for this video we are going to take the example of this insolence policy data just I've just used these data for example purpose and let's make some table like now call it PHP artisan make model insurance and then we'll create migration controller which will be the resourceful controller okay so let's go to migration so here we'll need these fields 1 2 3 4 so policy ID county latitude and longitude ok migration is ready we will create these fields so by the way I am using existing project just to save some time I have set up everything like database and all stuff so we are just creating this magazine and then my grid okay it should create so we got this insurance table and now we import all those data in this database okay let's go to web dot PHP and let's create some route so we'll create or out one route to show the form to import that file so that will be the file upload form I'll call it import all right so we will create these two route so import will show the form which will point to the create and will store store will just store that okay so let's go back to incidence controller so we don't need other methods we just need create and store scripts it or everything so this create we'll just so the view now call the import view let's create that let's create that new view so I'll just duplicate this welcome plate and name it imported plate all right so I'll get you top everything inside the body and I will paste our form so I pasted this simple form I won't let you just see me typing all this HTML form so basically we got a simple form with one file upload button by the fluid-filled and then submit button so which will point to import URL that we have defined here post method yeah and that's it now let's go to browser and there's view the master taste import okay we got pretty basic phone this will work for now okay so once we submit it will hit this method inside our insurance controller request all right so let's dine dump request all and see what data we are getting reference to the file so we'll be choosing that file inside and start CSV and then submit there we go so we are getting file and all other stuff so here first we'll do validation so the idea here of or uploading really big file is will make the separate files from one big file suppose we got this 30,000 record in this file we will just sum them down into several files with 5,000 or some that kind of data will create many files we'll just sunk it so for example up to this 500 record we'll make one file then from 500 mm under file and then we'll import will queue them all and then import one-by-one that will prevent us from that 30-second timeout and yeah it will keep uploading so we'll be using readies to upload the file continuously it will just look for the queue and keep uploading so it would like to go to upload script so if you do not understand what I just said the spear with me you get it in our moment so after file is validated we we are sure that we got file and then what we'll do is we'll get the file will front on the file into Eddie by doing so so from request we get file and then we'll get the real part of file gets yellow path so that's die and dump and see what we got inside file yeah so it will get all the content of the file so here we got all the file in Eddie file content in Eddie and then now what we'll do is we'll get with remove the first line of that file because the first line is this header right so the idea we had is we we just want to it's on this down to several file so we'll remove this first line and then we'll proceed further so Eddie slice and we'll slice the first line out okay so after that we will then let's say create the parts of data by chunking array chunk and then data so we'll split every thousand line yeah we can do a thousand or into 5,000 lines so according to your happiness of your query or if you have to execute more carry in queries you might have to reduce this line you might have to chunk them down and create more files to allow PHP to process them one by one yeah basically you can do a thousand five thousand so in our case there is not bigquery we have to execute and then it can easily process five thousand record at once so that's why I'm doing so so at this point we got separate chunk of data and now what we'll do is we'll look through them so parts as part and now we'll put these into our separate file so first we'll get a unique file name so we'll be storing them inside let's see so inside resources I'll create a new folder or you can create programmatically but yeah I will say pending pending files yeah pending files and then we'll access that we can call basepart our resource pot and then from resource pot will access the pending files folder and then we can concatenate current date time to make it unique yeah we are doing like so we concatenate the current date time and then we'll concatenate the key I will say index index dot CSV yeah there we go so now we got unique file name and then we'll put the listen to that bass all content of that song file so we'll use file put contents so we'll put into this file name the data will be that part so this part will be the array of data area of this array that contains did this 5,000 records all right okay so once that is done we can do yeah so we can floss this session queued for import and then we redirect user to import okay so far so good let's go to browser and see [Music] yeah so here we go and others upload this file submit it yeah so cute for importing another school back to PS code and it's inside finding files there we go so we got these files we got seven eight files and those eight file contain this data all right so this junk data and now we'll upload one by one so up to at this point we are done with controller and now we need to write a script that will take that one file and process it and then upload it to table all right so yeah that's it for controller another school back to model file we'll write that logic inside model so we have insurance model okay so here we'll write our import yeah you can call it input 2 dB all right so here fast we'll get the part for CSV files so we're CSV files are located so resource pallet and pending files so this is the directory where we're putting everything and then we can we can direct we can get all this file with this pattern CSP so basically what we're doing is we're getting all the CSV file part for all says we're just pointing the path for all CSV files and here what we'll do is we'll get two files at a time or one file at a time and then process it all right so after that we will use this glove function to get all the files in this fat so this function will just get all the files in our dislocation that we just saved it so it will get the area of all these files all right file path so now what we'll do is we'll go through each of these so these files and we'll just process two files at a time so or you can get two files or just one file at a time so we'll this will get the one file area of one file and then we will call it and then what we'll do is we'll import this to database so fasting fasting we'll get data from that CSV file by using this function so array map so we'll use the PHP function inbuilt function which will get CSV file content and then we'll get the content of file by file function so if you don't know about these you can search for PSP menu file PHP or what this file function do you can go to documentation and such through here the file reads the entire file into area so it converts all the file content into Eddie alright so yeah so here we got all data and we are calling this function to this convert those comma separated values into the readable data so now here we look through data for this data as row and then we'll import this so we'll reference this self class which insulins class and then we'll do update or create so first we'll check if the policy ID exists upon the cid it goes to row so the policy ID exists in our first row you see on csv we got in first row this is actually 0 1 so if you're confused about it just comment it and then just dump it and see what is the structure of your row so by the way this class name should be capital in my case due to the typo while running command it called that capital name so I will do insurance all right so file is renamed so just to test this out in one router this home route we are calling this import to DV method that will enable us to just test this data all right so let's run it in browser so home page yeah so we got this policy ID in 0 1 2 3 all right so let's go to code nice uncomment this out so 0 0 your policy ID and then so if there is existing policy ID will just update otherwise we'll create new data ok so let's see what else field will meet opt it so County let an LNG alright so one will be County so after this importing part is done we'll just delete that file unlink the file alright so this file will be deleted and then again this loop will execute it then this will act on another file because that the file that we are currently acting on will be deleted after it is finished importing then it will look through all files and then import all data so yeah that's how it works so just to tease this out we'll run this and see if it is working or not so it's safe level property we need to go to insurance and then protect date card it will make everyone everything free level that's it so let's run it so it looks like it is importing less your first page yeah so it is importing so we are currently processing through one file ok so this is working so for now I'm in I'll emptied this table so since we have sunk this into smaller data it is working for one file so we got several files like that so it will time out if we try to process everything at once so now next what we'll do is we'll call this method for one file at a time so the way we do it is by running the queue job or there are job in larvan so we'll run them ok so now we'll make a new cue to exit this code so that it will process automatically ok so let's go to terminal and create PHP artisan make job so it is called queue job we'll call it process CSV upload all right there we go let's go to process CSV upload so what we'll do is we'll execute all these accents that is inside loop and instead what we'll do is upload CSB sorry the name of it is process CSV upload and then we'll dispatch so will dispatch this job and we'll put everything inside this handle method alright so what that will do is that will just execute this code over and over again and we can just pass some data like file and then we can accept this file in our job like so so that will be string file please there we go so instead of this file this file so instead of self-will call insurance okay so now it should work but one more thing we want to add here is we'll use the Redis and those so Redis will allow us to throttle our cue jobs so here you can get these information in the documentation cues about how to create hues and all stuff so in order to use Redis cue driver you should configure read each database inside config database okay so let's go to config database so Reds client PHP daddies and all settings so all settings we don't need to alter anything here but in order to use Redis we need Redis okay so we need Freddy's 2 for larval to walk with Redis so let's install this produce so the reason we are using readies for this cue connection is because the Redis allow us to throttle our job so we can process like to job every 60 second and here will be our job logic so that's exactly what we need in this case so we'll allow one file or one yeah in our case one file or one logic for every 60 seconds so what we'll do is let's copy this snippet and let's go to process CSV upload shop and inside handle method so you can give key anything we want like upload CSV and here we'll put our logic so this logic there we go okay so let's review the workflow so fasting what we'll do is we'll go to upload phone and from a platform will upload the file then file it we get divided into separate smaller files and those smaller files will so let's go to this incidence controller so here we are making a smaller chunk of those files and then it will be queued for import okay so once that is done let's review our workflow so first thing we'll go to the upload form let's go there okay so in this form from here we'll just choose a file that we want to upload and then that file will get processed inside this insolence controller and this is store method so this store method will just sum the file in two separate file smaller files and after that files are all summed what we'll do is we'll call that matter that new insurance and we need to import this file yeah class so we'll call import to DB method and this will handle all this logic so it will get all the files and then dispatch this solve several times okay okay so whenever we call this method it'll execute everything here from insurance class so here instead of this we can directly let's say so we don't need to slice this class here we can just run G finds let's call it files files so we process all the list of files and then we'll dispatch because we are limiting that execution of this over here so what will do is will only allow one every per say in 20 seconds okay so for every 20 seconds only one job will be executed so as soon as this will run it will execute like no more files so it will put 4 for example if there are 10 files in a day then there will be 10 files in queue so 10 jobs in queue and water this help us is so it will only execute one queue job for 20 seconds that way we get enough time to process all the files ok so it 1 2 30-second timeout ok so that's it for just our information what we can do is dump so processing this file so we will just dump the file name this file so that we know which file it's processing so once that is done we can yeah we can do here done this file yeah okay so once that is done let's go to dot any file and Q connects and let's put it Redis and hopefully everything is fine so if you are on Windows and using logon what you need to do is just go to this Preferences service and ports just enable this Redis okay and then close it and wait for it to reload so it will stop all the services and then reload and you can see here ready started at this port so that port is exactly ready sport I think that ready support you configure in config database so you might not need to change anything because there is a default ready sports six three seven nine yeah so yeah that's it so once that is done what we need to do is P we need to listen for all the cues by running what's the command for listen I think PSP are think you were cork you listen yeah this piece we are sink you work let's run this command okay so now if every everything goes on cue then it will start processing now it's time to go to our form and then execute then upload the file let's upload that so there are two class that's why it's error okay so now let's upload that file first what we need to do is so while testing we have already created these files we need to just clear them up and start from scratch okay so so we are here in import form and let's do the file let's import it submitted okay so you forget this kind of error looks like we have forgot to object one setting looks like we have Redis client so Redis client should be Redis so of here so this client it calls to Freddy's save it let's go refresh continue there we go okay so in this our queue job we forgot to import this Redis class make sure import this first class as this Redis facade over here so once that is done we need to clear everything out go to ready see li plus TV and then exit so this will clear out any unfinished jobs previously so that we are starting from scratch insurance controller everything looks good make sure you know your door D and B you are doing this cue connection Redis and risk line Redis alright so there's one place we are some cue work so it will listen for any cues offs that we we are going to execute in a moment so let's go to the import form let's choose file let's choose that file big file and submit so cute for importing let's go back to yeah so it is processing so processing this file this one paragon this zero five zero one five one zero all right so we can we can watch our DB if that is getting imported or not yeah we already processed like here five thousand four seven rows so it is still yeah so it keeps increasing everything looks good so yeah done this file and it is processing another file and similarly it will keep executing so now you can import any number of data you want so it doesn't matter so if there is high in intensive tasks or if you need to execute too much query you can just alter here you can make file with just hundred records maybe yeah this there will be a lot of files but it will import that to your database yeah so we finished already we finished two files we are processing third one we already got past you know twelve thousand records and if you see on your resources file pending files one two three four five six so there were eight files now there are only six so once the file processing complete it will just delete that file and then process different files so previously it was processing this one this file now this now this 12 number so now this 13 ok so now there are remaining these files 14 15 16 and 17 all right so all files got processed and now got more than 30,000 records in our database so yeah this is how you import very large yes we find hope you learned something out of this video so I'll see you on next video bye
