Validate and import data into database using excel file

This article gives you an idea of validate and import data into database using excel file , I have used maatwebsite package. It is easily working with csv or excel file. Also, provide an easy method to get data.

Let’s start to validate and import data into a database using excel file with steps which given below

1) The first step is to install the maatwebsite package in your Laravel Framework.

2) Open project directory in CMD or Terminal, then Add maatwebsite package using below command.

Composer require maatwebsite/excel

Using Reference:-https://packagist.org/packages/maatwebsite/excel

3) After successfully install package then Create datatable, Model, Controller and One view file for table view layout.

4) Here given routes list. Write this Routes in routes/web.php file.

1)Route::get('ImportDetails'['as'=>'import.index','uses'=>'ImportExcelController@index']);
2)Route::post('import-excel',['as'=>'users.import','uses'=>'ImportExcelController@importExcel']);
3)Route::post('export-excel', ['as'=>'users.export','uses'=>'ImportExcelController@getExport']);

– for validate and import data into a database using excel file, the first route is used for listing imported data which is given in below figure.

– “validate and import data into the database using excel file“, First download blank excel file onclick DOWNLOAD TEMPLATE button. After that Fill all data and import file.

– Onclick SUBMIT button it will check some validation of input file.

– In a controller, get file path and load the file path in Laravel excel. Check the data row by row. Maatwebsite package provides $reader function which is used for reading all excel or csv data and make one multidimensional array. It will filter multidimensional array using the Array_filter function which is given in below figure.

– After that, all data are filtered out then check the value of column field one by one using for loop.If some field is blank or repeating then it returns error messages.

– All error messages are pushed in  $err_msg_array variable, which is given in below figure.

– Using validator it will check validation of data in $rules variable which is given in below figure. Here I am using messages() function which will return all message.$messages a variable is one type of multidimensional array.

– After using foreach loop one by one push all the error messages in $err_msg_array variable. You can see a code in the figure which is given below.

– $errormessage variable is return error message which mentions the row index.

– After check $err_msg_array[ ] is empty then data will store in $dataImported variable and unset the incomplete data using laravel unset() function. Then one by one completed data will be inserted into the database using insert() function.

– After check $errormessage variable is empty then it will redirect to the listing page(index page).

Otherwise following code which is given below.

– Here rendering  error message in view file error_list.blade.php using laravel render() function. In this file using foreach loop, print all the error messages which are given in below figure.

– After then it will redirect to view file(index.blade.php) with error data for next to download an error listing view.

– Here is some jquery code which used to display error messages modal and push download data value in a hidden field of a form using session.

– In below figure you can see modal of displaying the list of errors.

– Onclick DOWNLOAD ERROR FILE button it redirects to the controller in which download error file. The code is given in below figure.

Thank You

Happy Coding.