#2: Laravel Excel Import to Database with Errors and Validation Handling

[@erickwakye4491]

Can you compare an excel file data with a sql view data before inserting from the compared results into a table please. any suggestions

[@QiroLab]

*Don't get left behind! Try Spec Coder: Supercharge Your Coding with AI!* 👉 qirolab.com/spec-coder Support my work: 1. On BuyMeACoffee: www.buymeacoffee.com/qirolab 2. On Patreon: www.patreon.com/qirolab Timestamps: 00:34 artisan make:import & import users data in the database 01:11 import route and form to upload excel file 03:46 Import excel file content into Database 07:13 `Importables` trait 10:00 `WithHeadingRow` interface to handle headings in excel file 10:20 Handling Errors 14:28 Handling validation errors 22:56 Batch inserts 24:56 Chunk reading 25:38 Queued imports 27:35 Import relationship model 31:46 `afterImport` Event

[@samyecheverria4388]

Hello! How are you? I'm Brazilian and I watch your videos, which are always very educational. Congratulations! I would like to know how to import a spreadsheet with sales data from several vendors, with dates and commissions. I followed everything you indicated in the tutorial but I couldn't. I really wish you could help me. Thank you!

[@muhammadridhuanjaafar4291]

anyone can help i use withheadingrow, but got undefined index error

[@MajorAzoulay]

Getting an error when using the command "php artisan make:import" help? laravel version 9

[@yassinenatij1699]

when we switch to ToCollection , apparently the validation doesn't work well, for example when we have a field that doesn't take null. It actually throws a QueryException and doesn't skip this failure. any recommandation ?

[@bl4nknvrlose604]

I have the same problem bro. Did you fixed it ?

[@aramkhorsandi4191]

How can I validate multiple columns for a unique record?

[@nurawadahismail02]

How can I allow user to upload any excel file name then the data will insert into specific table name? you didn't show it here :(

[@QiroLab]

I did not get what you mean. This is exactly I showed in the video, allow user to upload any excel file and insert that into specific table.

[@LuisParrado]

Thanks from Colombia, excellent video, i'll try to make a video like this in spanish.

[@alejopanto]

Compadre una pequeña pregunta, a ti te funciono el recorrido de los errores ?, porque a mi me retorna el error con dd, pero no me muestra la alerta ?, me podrias dar una mano con ello, te agradeceria

[@ragapriyono3820]

Please help sir, can you give example of custom rule validation using withValidator function?

[@royermichaelquispeccoya1036]

`WithHeadingRow` interface to handle headings in excel file does not work in CSV

[@sufeemymon]

thanks for this we got it , but in laravel 8 we getting this , how can we sort out this Call to undefined method App\Imports\QrCodesImport::failures() laravel 8 have this issues ?

[@topevol39]

Hi! Please help I did all what you have said but I get error Target class [UsersImportController] does not exist. What does it mean?????

[@waheed685]

Hey everyone, I'm validating excel file and trying to get All errors after the execution. But I have a problem regarding anyone can help me?? Thanks alot

[@alejopanto]

Excuseme i have a question, i use the function import in my proyect (laravel 5.8, php 7.4) but the alerts errors not function, i try and try but not funtion. now i clone your proyect in git and the functionality is the same , alerts errors not function. not show the alert in the view index ? why ?

[@ahmedalamichebli4862]

hello sir , i hope u are doing will when i sumbit i get this error The POST method is not supported for this route. Supported methods: GET, HEAD i have no clue where it comes from please help

[@erickneverson]

With queued chunk reading, how can we get the status of a particular import to the front end (UI) to display progress?

[@JyothiLekshmiJ-j7h]

I did excel uploading in first methode but the values not get inserting into table... what would be the reason

[@bibhasash7371]

How to remove space from column name like i'm using a column name that is fee category in excel sheet and whenever I'm trying to store the data it returns undefined index because of it ... how can I remove that

[@brainai_academy]

How to upload file excel error column spaces (User Name)please your video

[@josepacheco1063]

Hello! can you do a deep tutorial about the queuing with validation and frontend notification? for example a progress bar? and how we can deploy this in live server? because in this video we always have to run the queue:work command. thank you

[@QiroLab]

Great suggestion! I have noted that. Right now I have some more priority tutorials. At some point, I will definitely create a video on this topic.

[@josepacheco1063]

@@QiroLab Thank you very much! Keep up the good work!

[@riccardotassinari2929]

on the live server you can manage the queue:work command with supervisor laravel.com/docs/9.x/queues#supervisor-configuration

[@juanmarquinaventura3202]

Muy bueno!, Seria bueno que puedas terminar las demas validaciones de importación, gracias de antemano

[@alejopanto]

Compadre una pequeña pregunta, a ti te funciono el recorrido de los errores ?, porque a mi me retorna el error con dd, pero no me muestra la alerta ?, me podrias dar una mano con ello, te agradeceria

[@yesidgarcia8111]

@@alejopanto Debes usar un Try catch y la variable failures tiene los errores try { $import->import('import-users.xlsx'); } catch (\Maatwebsite\Excel\Validators\ValidationException $e) { $failures = $e->failures(); foreach ($failures as $failure) { $failure->row(); // row that went wrong $failure->attribute(); // either heading key (if using heading row concern) or column index $failure->errors(); // Actual error messages from Laravel validator $failure->values(); // The values of the row that has failed. } }

[@rahmatsulistio]

can we access the cell styles and comment with this library?

[@pkeerthana723]

Is there any way to validate unique email when emails are stored in database as encrypted data and return back with error message row wise.

[@talhaqureshi3105]

when i put onError method this error is coming please help Declaration of App\Imports\DatasImport::onError(App\Imports\Throwable $error) must be compatible with Maatwebsite\Excel\Concerns\SkipsOnError::onError(Throwable $e)

[@javedsaleem7322]

I m facing error on my ubuntu server. Zip archive not found

[@josepacheco1063]

Please don't stop creating this videos about laravel and if possible for vue also! people with great skills in teaching like you are very rare this days. you're awesome!

[@QiroLab]

Glad to hear that!

[@akichoe]

do u hv example code import with multiple start and end date?

[@shameulzion9221]

how to import CSV file pivot table data???

[@samyecheverria4388]

Hello! How are you? I'm Brazilian and I watch your videos, which are always very educational. Congratulations! I would like to know how to import a spreadsheet with sales data from several vendors, with dates and commissions. I followed everything you indicated in the tutorial but I couldn't. I really wish you could help me. Thank you!

[@bikashsubedi9965]

$import->failures(); not showing any filed columns. I have removed onFailed() function from Import file but always showing blank.

[@gabriele3678]

Hola, excelente trabajo, me ayudó mucho, quisiera saber si hay alguna manera de mostrar alerts de los filas que si se registraron? muchas gracias.

[@alejopanto]

Compadre una pequeña pregunta, a ti te funciono el recorrido de los errores ?, porque a mi me retorna el error con dd, pero no me muestra la alerta ?, me podrias dar una mano con ello, te agradeceria

[@kovalus]

Thank You for Your tutorial! How can I make 'meth...' shortcut (like You do) to generate function scaffolding?

[@namjin2165]

nice video, you know how to use UpdateOrCreate method using ToModel? or how to handling this errors using ToCollection?

[@davidcastillo7383]

Thanks a lot for this video, it helped me a lot to understand Laravel Excel. Thanks man.

[@codecombination9337]

how to import images?

[@infospeople5502]

Hallo $import->errors() no work

[@timbogdanov3203]

What happens if the heading columns (eg. Last Name) do not correspond to the database columns (sir_name), is there a way to offer manual control to get them hooked up.

[@RikiSipahelut]

How to run php artisan queue:work Automatic ?

[@QiroLab]

For that you can install `supervisor` on Linux system.

[@yakhyomus]

Hi do please part 3

[@NathanBudd]

Why did the last one fail if you were using the `skipOnFailure` trait? Does that not apply to `useRow`?

[@bakayokoyaya225]

Hello, Great Tuto out here! However, how may I import a specific sheet by the sheet Index? I can't seem to find how to pass the sheet index value as a parameter. Any suggestions?

[@letsgozayboo]

Thank you so much, it helps a lot. Can you finish the remaining like failure handling and storing failure errors? Really appreciate. Thanks

[@alejopanto]

Excuseme , nice to meet you, i have a question, to me, not function the errors in the view index ? not show nothing ?. the functionality Import not return errors. why ?

[@surajkewat1253]

Bro how i can validate the heading row is correct .For eg heading is "first name" "last name" . But user enters excel with heading row as "name1" and "name2"

[@amrullahdev8895]

i have 2 row , but why i got null ? but if i coment first line code row[0] , the row[1] read the data , when i coment row[1], the row[0] read the data , but when i uncoment both of them , all data return null

[@djhyll1]

Hello bro excellents videos , I have the .xlsx file with the logo, how can I read the cell where id, name, email is when importing them?

[@himalmajumder2174]

Thanks to QiroLab team for such as helpful video. I have found a issue in laravel/excel. I'm using ToCollection for inserting data. i found that data not inserting in database but primary key are increment. no impact in database. i'm not find any solution. i need your help

[@agamurat3019]

file_put_contents(): read of 8192 bytes failed with errno=21 Is a directory

[@letsgozayboo]

???

[@misterstrange3657]

Thanks a lot sir

[@shumitpradhan]

can you make a video for multiple sheet import, it would be helpful

[@QiroLab]

I have not planned for it, but I will surely create a video on this as well.

[@shumitpradhan]

@@QiroLab please do make, if you are free help me solve it

[@lserranoit]

Hi, how can i do the relationship if i need to insert another table not the country instead the auto_incremental generated in the import for each row?

[@julianfranklin5845]

how to make validation failures using collection()?

[@owdengodson2990]

Hello, what causes the error "Serialization of 'Closure' is not allowed"? Thanks

[@elmomahupil]

Hi sir, thankyou for your great tutorial, but what if someone tries to import a word file or other types of files? How can we prevent that?

[@elmomahupil]

Because I also want to output an error if the file that is imported is not an excel file

[@QiroLab]

You can use Laravel validations for that. Example: ``` $request->validate([ 'file' => ['required', 'mimes:xlsx'], ]); ```

[@elmomahupil]

Thankyou sir

[@dodiyulian1317]

What DB management software do you use?

[@QiroLab]

Currently I am using TablePlus

[@behappey8494]

thanks to mucch sir i have some issue when the database it is relation data with differnce tables how import excel for this case and thanks for you in advance

[@josephprosper6493]

Great tutorial. What is name of Database application (DBMS) you use?

[@tahtatah6573]

when was the third part of this video published? very helpful. thank you

[@jonice4229]

how i can update if data exist in excel? Thanks!!

[@nelsonraj27]

Hi, thanks for explaining the concept in clear and understandable way.. keep going.. i have doubt .. so kindly explain this too..how to ignore empty rows while validating ,bocs wen i import the excel the data imports successfully but i am getting validation failure error for empty rows..

[@dattran4774]

thank bro ! can read handle validate content file xlsx ? ex : data null

[@Paltibenlaish]

Hi guys, One question is it possible to validate one row depending the value from other row when validating each separate row?

[@Gdapu]

Cool tutorial. Loved it.

[@QiroLab]

Glad you liked it!

[@technicalknowledge4949]

hey skiperrors and other skipping not working with tocollection. Please help!!. Otherwise everything looks perfect

[@fauzanmazlam6806]

Hello bro, you have solve that problem? Tell me also if you had.

[@swapnilkurhade8943]

can we upload images from excel to laravel local storage?

[@norhanel-nezamy6465]

Hello, r u faced this issue? I used batching, chunking with a large file and imported it using queue and it's working fine, I added an extra job to record all failures after finishing all queues and it's an empty array although there're failures rows. $import = new UsersImport; $import->queue($request->file('file'))->chain([ new UsersImporterFailures($import->failures()), ]); My question is , How can I get all failures when using queue importer after running all queues ?

[@QiroLab]

I have already explained that in the video, add SkipsOnFailure interface to import class, and then add `onFailure(Failure ...$failures)` method, in this method, you can handle this error as you want, for example, store that into the database and later when import queue is finished you can see all validation errors.

[@danishmirza5857]

No ReaderType or WriterType could be detected. Make sure you either pass a valid extension to the filename or pass an explicit type

[@ba.z2606]

same error

[@marvinreyes305]

Hi, thanks for making this tutorial it was a great help! But is there a way to import a multiple excel file ?

[@kajudam3829]

Start row (2) is beyond highest row (1) help me please

[@iantabz]

Hi I need your help in updating data from Database, if it already exists it will be update data from the uploaded excel file

[@sumar2543]

thx for tutorial. very helpful

[@KasimSheyi]

This is very helpful. Please what's the extension name that does the auto-import of the interfaces as you type? Thank you!!!

[@QiroLab]

This is most asked question on my channel, I will definitely create a video on that.

[@yassinenatij1699]

Thanks again ! very helpful video, looking forward to see you next video related to notification and the onFailure .

[@skyniitzs5942]

Muy buen video, me hubiera gustado que se hubiera realizado con la ToCollection porque las validaciones que se uso con ToModel no funcionan . Pero na, Muy Buen Video, El unico de su clase

[@leofranca4126]

great video, how validate headers of excel?

[@thanks_God_ForEverything]

Best Way I have ever seen precise,practical and useful

[@behappey8494]

when i try to import excel with relation not work can help me here

[@edmartinez5345]

From the Philippines! Thank you so much great video

[@QiroLab]

Glad you like them!

[@rifat9264]

amazing, your video went staight to the point! thanks

[@QiroLab]

Glad you liked it!

[@roselfrancisco1463]

Very informative, Thank you so much for this tutorial

[@QiroLab]

Thanks

[@thuyphungvan3491]

Thanks

[@QiroLab]

Welcome

[@RANJEETKUMAR-wz4dg]

Thanks

[@QiroLab]

Welcome

[@faisalshikder7308]

Please make a video on, which plugin and extension are you using for VS code. And suggest us, which is more efficient for develop php laravel vue project. And some keyboard shortcut for fast development. Your tutorials are great ❤

[@QiroLab]

Noted

[@sir_brian_d]

Great video!

[@phanhoangkhanh761]

THANK YOU FROM VIETNAM - YOU SAVE MY LIKE

[@QiroLab]

Glad you like them!

[@peterpardo4138]

wow amazing

[@ifranhossain6497]

how to run queue without php artisan queue work command

[@QiroLab]

For that you will need "supervisor" that will run `php artisan queue` in the background.

[@joneyspark7064]

Brilliant

[@jamesbhatta]

Very informative. You were very smooth with each and every scenarios. Keep up the good work.

[@QiroLab]

Glad you enjoyed it!

[@Paltibenlaish]

amazing tut

[@ngusut]

Thank you very much,

[@QiroLab]

You are welcome!

[@flowerbook885]

you are the best instructor that explain that part

[@QiroLab]

I'm so glad!

[@mohammedhachalu735]

wow thanks soo much it is good tutorial

[@QiroLab]

You're welcome!

[@slipknotfen4o]

Absolutly amazing explanation !

[@QiroLab]

Glad you liked it

[@informota2021]

thanks a lot dear you save my time

[@QiroLab]

Glad to hear that

[@khairiu28]

Great tutorial and explanation, thanks for the video

[@QiroLab]

Glad you liked it

[@RJCommunityDev]

Create Videos on Vue-next

[@QiroLab]

Sure, I will create tutorials on that also.

[@QuangNguyen-pk2py]

Omg, thank you so much ;3;

[@QiroLab]

You're welcome 😊

[@digilabmedia]

You are very good! Thank you very much :-)

[@QiroLab]

You're very welcome! Glad you enjoyed it!

[@FahadAli-rn6kn]

Awesom tutorial

[@QiroLab]

Thank you! Cheers!