How to Import Excel Data in Laravel and Insert into Database

hello friends this is one more video tutorial on Louisville and in this video tutorial we are going to learn how to import excel file data in Louisville framework that means how insert excel file data in my SQL database in laravel framework we have already seen how to export my SQL data to excel file by using Mott website excel package so this package also we will an import excel file data also we all know Louisville is enterprise-level PHP framework so if we have built any enterprise level application then we have to require importing and exporting of data from our web application for importing and exporting of data in our Louisville application here we will Mott website excel package will be used for import excel file data in my SQL database in our Louisville application now let's start discussing this topic so this is our testing database and in this we have one customer table in this table we have already inserted some data first we will fetch data from customer data and display on web page after this we will import data from excel sheet and insert into this customer table which will be visible on web page this is sample excel sheet in which we have store some customer data we will import this data in customer table in this Louisville tutorial this is our Louisville working folder first we want to set database connection so for this we have go to config folder and here we have open database dot PHP file in this file we have defined my SQL database configuration like host name set to local host username set to root password set to blank and database name set to testing after this we have open dot env file and here also we have defined my SQL database configuration like host name set to local host database name set to testing username set to root and password set to blank now we want to create import Excel controller so we have go to command prompt in which we have already run composer command and here we have run this command that command will make import Excel controller file under app folder / HTTP folder / controller folder which we can seen here so we have opened this file under this file here we have right used statement with database it has been used for database operations in this level application under this controller first we have defined function index this function will be called if we have directly called base URL slash import excel in browser then this function will be called under this function we have right dollar data variable as equal to database class with table method under this we have right customer table this method will make query like select star from customer table after this we have right order by method with two argument in first argument we have right customer ID table column and in second argument we have right descending order it will add order by clause in select query lastly we have right get method this method will run select query and return query execution data in array of object which has been store under dollar data variable below this we have right return statement with two argument in first argument we have right view file name import Excel and second argument we have right compact function with data argument this code will load import Excel view file in browser and post table data will be fetched from this dollar data variable now we have go-to resources folder / view folder and open import Excel dot blade PHP file and in this file we have already include jQuery and bootstrap library first we want to make form for upload excel file so we have write form method is equal to post and encryption type is equal to multi-part form data for upload file in action attribute we have right larval expression with URL method and under this we have right import Excel slash import so when we have submit form then it will send request to import method of import Excel controller under this form first we have right larval expression and between it we have CSRF field function we have used this function because we have post data to server after this we have rightt input type is equal to file and attribute name is equal to select file by using tag we can select file from our local computer [Music] and lastly under this form we have right input type is equal to submit with attribute name and value is equal to upload by clicking on this button we can submit form data to server below this form we want to display existing customer data on webpage so we have make one table with six table column like customer name gender address city postal code and country after this we have right at the rate for each loop with condition like dollar data variable as dollar row variable by using this loop it will fetch data from dollar data variable foreclosed at the rate for each directive we have right at the rate end for each directive between this in first column we want to display customer name so we have right Louisville expression with dollar row customer name object in second table column we want to print gender details so here we have right laravel expression with dollar row with gender object in third table column we have right Louisville expression with dollar row with address object it will print address details same way for print city details here we have right Louisville expression with dollar row with city object after this for print postcode details so we have right Louisville expression with dollar row with postal code object and in last table column we want to print country details so we have right Louisville expression with dollar row country object this way it will print customer data on webpage now we have go to import Excel controller and here we have make import method with dollar request argument it will receive Excel file upload request for import data first want to set validation rules so here we have right dollar this with validate method with two argument in first argument we have right dollar request variable and in second argument we want to define validation rules for defined validation rules in key we have right select file and in value we have right validation rules like required file extension X SL and xlsx this condition will check we have select any file or not if we have select any file then that file must be excel sheet file if this validation rules not follow then it stop execute code display validation error but suppose all validation rules passed successfully then it proceeded for import excel sheet data so below we have right dollar path variable is equal to dollar request variable with file method with input file name select file with get real path method this method will return temporary path of selected file which has been store under dollar path variable now here we will use mod website excel package for import excel file data in my SQL so first we want to download package and install in our eval application so first we want to download this package so we have go to command prompt and here we have write composer required mod website slash Excel and press enter this command will install this package into our Louisville application once it has been downloaded and installed in our Louisville application so we have open config folder / app dot PHP file and here we have add this line at the bottom of providers array after this we want to again at excel facade to the bottom of the aliases array after adding this line we have now saved this page and now we have can use this excel package in our application for user Excel library under this controller here we have right use statement with Excel by using this code we can use excel package under this controller now we have go to import method and here we have right dollar data variable is equal to Excel class with load method and under this method we have right dollar path variable with get method here this load method will load a file and by using get method it will return all sheets and rows data which has been store under dollar data variable after this we have right if statement and under condition we have right dollar data variable with count method this method will return true if dollar data variable has some data under this loop we have right for each loop and under condition we have right dollar data with two array method as dollar key with dollar value method this two array method with convert array of object to array under this loop again we have right for each loop and under condition we have right dollar value variable as dollar row variable under this loop we have right dollar insert data variable is equal to array with first key customer name with value get from dollar row customer name variable in second key we have right gender with value get from dollar row gender variable in third key we have right address with value get from dollar row address variable in fourth key we have right city with value get from dollar rose city variable in fifth key we have right postal code with value get from dollar ro postcode variable and in last key we have right country with value get from dollar row country variable this way we have store excel sheet data in dollar insert data variable now we have right if statement and under condition we have right not operator with empty function with dollar insert data variable if dollar insert data variable has some value then it will execute if block of code under this block we have right database class with table method under this we have right customer table with insert method with dollar insert data variable this code will make insert query with data get from Dollar insert data variable and insert data into customer table lastly we have rightt return statement with back method this method will redirect page to last visited page after this we have ad with method with two argument in first argument we have right success and in second argument we have right message like Excel data imported successfully this message will be displayed on webpage for display validation error and success message so we have go to import excel dot bleed dot PHP file and here we have right at the rate if directive with condition like count function with dollar error variable value greater than zero for close this if block of code here we have right at the rate and if statement this condition will check if dollar error array has some value then it will execute this if block of code suppose a true then it will execute this block of code and under this we have right at the rate for each directive with dollar errors with all method as dollar error by using this directive we can fetch error from this dollar error variable and display on webpage and foreclose this directive we have write this at the rate end for each under this block we have right list tag and between this tag we have right to bracket and between this bracket we have right dollar error variable it will print all validation error on this page after this we want to display success message on webpage so we have right at the rate if statement and under condition we have right dollar message is equal to session class with get method with success argument if this condition true then it will execute if block of code and for a display success message here we have right larval expression with dollar message variable it will display success message same way for clothes if Directive here we have right at the rate end if this way we can close if directive in Louisville lastly we want to set route of controller method so we have two routes folder an open web page dot PHP file here we have right root class with get method and under this we have right to argument like import Excel and import Excel controller at the rate index so in browser when we have type base URL slash import Excel then it will called index method of import Excel controller below this we have right root class with post method and under this we have right import Excel slash import and in second argument we have right import Excel controller at the rate import this code will set route for import method now we want to start Louisville application so we have go to command prompt and here we have right PHP artisan serve command it will start Louisville application now we have check output in browser friends here we have type base URL / import Excel so here we can see upload file form and below this we can see existing customer data on web page now first want to check validation working or not so we have directly click on submit button after click on submit' button we can see validation error on webpage now we have select image and again click on submit button so again we can see validation error like only excel sheet file allowed for upload now we have select this excel sheet and again click on submit button after click on submit' button here on web page we can see success message and below in customer data we can see excel sheet data has been imported in my SQL table so this way we can import excel sheet data into my SQL table by using mod website excel package in Louisville framework if you have any query or input regarding this video tutorial please comment your query or input in comment box or if you like this video tutorial please share with your friends or even you can also share on social media also if you want to get more update regarding our video tutorial please subscribe our YouTube channel forget more update regarding release of future video lastly keep watching our YouTube channel thanks for watching this video tutorial

Channel: Webslesson

Views: 52,991

Keywords: laravel, import, excel, data, mysql, database, import excel, import excel file in laravel, import excel laravel, laravel excel, laravel import, laravel import excel, laravel import excel to database, laravel import excel file to database, laravel import data from excel, laravel excel import, excel import laravel, excel import in laravel, excel file import in laravel

Id: mMhjpRLq76I

Length: 23min 20sec (1400 seconds)

Published: Mon Feb 25 2019