How to import excel file's data in Database in Laravel by using Maatwebsite/excel Library.

hey guys what's up in today's video we gonna learn how to upload excels data in our database in laravel project so first thing we have to do is to install mod website Excel lab ready in our laravel project you can easily install mod website ex-celebrity by using this command just copy this command and paste it in your terminal hit enter so after some time it would be downloaded or installed in your laravel project I have already installed it so I don't need it I gonna close it as of now if you want to check whether this mod website Excel lab ready is installed in your project or not so just go to composite or JSON file and look for mud website mar 21 of this library is 3.1 now get back to our functionality and now we want to upload our laravel excel starter in our database so just click on imports and click on 5-minute qk startup you have to create a file for import functionality in your laravel project just copy this whole code and paste it in your working folder but you can see here it's already giving me name of model if you don't want any specific model in your file you don't need to use it just remove it and you want different name for your file you can do that as well such as Excel import I have already created this file as you can see here Excel import so I don't need to use this command again but to show you guys I'm gonna hit this command and as you can see it says that import already exists so as you hit enter it would create a import folder import folder in your F folder and inside import folder there will be a file Excel import dot PHP here we have and one more thing that I have already created a controller for this functionality upload Excel and I have written code here how to get the submitted form excel data in our controller so now let's jump to our view file and here we are this is our view file and I have declared a form in this view file contains input tag this one here right and I have given name of this input tag as file you can see name equal to file right now what I gonna do I gonna upload an excel sheet in our laravel project by using that input tag of HTML so this file contains all this information name of employee email of employee and the employee ID so we have John Doe and Marty Stark and the first row contains the name of column right so I have already created a database for this functionality if I go back to my local host PHP my admin and if I click here you can see I have already created a table with the name employee records and this contains three column actually four column IDs primary key and the other columns are name email and employee ID this one already has some data so what I gonna do I gonna remove or empty this table now you can see it's already blank because I was just practicing on this functionality so before giving to check whether this feature is working perfectly or not so now you can say this table is completely blank and now what I gonna do I gonna upload this excel file in my project an excel file is inside pictures and this is the name of fine for dot CSV I'm gonna just do this Fallon I'm gonna submit this fat but before submitting I want to check watch the data we have inside excel file so what I gonna do you can see I have maintained that the route for action attribute is route excel in my web dot PHP file excel this is route name of the route so this is that it is submitting this information inside excel function of upload excel controller right so let's go to upload excel controller appreciate if we upload excel controller and here is the name of function excel so you can see that I am getting information by using dollar request file if you want to check here we can check here today and variable name and I hit submit so you can see all the information regarding this excel file and one more thing that while defining forms information you have to mention it mention encryption type multi-part form data if you don't mention this in your form then you won't be able to get all the information you will just get the name of file if you want all this information then you have to mention encryption type form data now we are inside this and we can see that this variable contains all this information and now what I gonna do I gonna pass this variable in our newly created excel file so you can see here I have used this import function and I want to tell you that this excel is coming from here might website Excel ability right and in this function I am passing object of this excel in put class from this one this is our newly created Excel import file and it is available inside this import folder go inside here and then you create this file you will already get this collection method automatically right so now let's check what are the information we are receiving here read okay guys and and this is a variable we are passing this variable inside this import function and we have also the object of this export import class this one right now again I go back to form and I get import this file and submit here so you can see we get this array and this is 0 index 1 index into index right so as of now we are here if you want to check again that are we in right controller or right function I just type testing and reload this fit again yeah you can see we are at the right and yes we are at the right function ok now let's check what are the information this array contains in 0 at 0 index this contain name of the column of excel file I am talking about this column name email in and employee ID right and why first index contain information of first row of excel sheet this one don't know John at gmail.com employees employee ID is employed zero-one-one right and the second index contain information about second row of axles it so you can see here if you upload your excel sheet using my website Excel lab ready you will get information in this way always zero index contains information about the first row second index contain information about the second row and the third index contain formation about third row but as you know we don't need this name employee ID employee email or employee name because we have already created this in our table so what we gonna do we gonna use or we gonna upload all this information in our table so let's get back to our function and what we gonna do we gonna run for each loop in this function and dollar as dollar key value right and let's check what we have in dollar key and dollar value if I reload this page again it says that dollar key this one is having value 0 which is index zero of error and it contains information of Row 1 of excel sheet so you know it guys we don't need this information because we want to upload information about employee id's like these like this we don't need this information so we want information after Row 1 of excel sheet like row 2 + Row 3 what we gonna do we gonna escape information of 0 index of array so if I write if dollar key is not equal to 0 then I want to perform operation on those indexes of array so what I want to do here I will use query builder you can use elephant model as well if you want but I am more into query builder stuff what I gotta do I'm gonna use use DB and you have to mean sin in which table you want to insert your records and name of table is employed record and you have to write insert command I have already written this command in another file so you can use this one also or that one also it's up to you you have to just maintain the right condition so it says that employee records is name of table and by using yes but you want to know what does value we have at value 0 or value 1 so let's check here duty value so as you can see in this condition it says that if the if the key doesn't have value 0 which means if key does not have this information then I am allowed to insert records in my table because I don't want this record I don't want this record of index 0 in my table because it is not worthy to store this information in our table because I have already these columns in my table ok so I am escaping all the information inside 0 index of array that's why I use condition greater than 0 or you can also use not equal to 0 it's up to you you just have to mention read condition now let's check what are the information we have here so I have used DD to stop the code here to check what are the information we have I again reload it so it says that we have this information if you want to check what's the a key value then you can also use as we know it is row first of excel sheet because at key one we have information about this row so I again refresh this and it says that at this position at key one we have information about Row 2 of excels 8 so these are the information so you can see at 0 we have name of employee at 1 we have email of employee and add to index we have employee ID so on the basis of this index number I have put this variable according to its respective column name so in this way we will upload our information in our table and so now what I gonna do let's check again this table is empty if I show you whole functionality from scratch let's go back and less whether I have removed discord or not yes I have removed it so there is no obstacle now in our controller there is no DD function and in our exilim import there is no duty so we can be assured that our code will run smoothly without any impediment so what I gonna do again just upload this in photo CSV and hit submit it says that inserted successfully because I have mention here a favorite things were works perfectly here inside air then so this method in eco inserted successfully and now let's go back to table and if I check here so you can see all the information of this excel sheet formation about Row 2 and Row 3 has been successfully inserted in our table I hope guys you like this functionality thanks for watching please don't forget to subscribe

Channel: Web Tarang

Views: 5,890

Keywords: Laravel, Excel, PHP

Id: yXN6fg4SPRo

Length: 15min 48sec (948 seconds)

Published: Sat May 23 2020