Read csv file and insert in database with validation?


#1

Hai Everyone,

I need your help any plugin available for read csv or xls file read and insert that read data to database with validation.

Kindly give suggestions.

Thanks advance.


#2

I use this lib to read csv xlsx files its fast and low memory usage:

To insert into the database just create the model with the validations rules, create new entity with the read data and save()


#3

I’ve used https://csv.thephpleague.com/ in the past, it’s pretty easy to work with.

As far as validation, saving a row at a time would be easy - you’d take the csv data you get back as an array, json, etc, and then use it to populate the data you pass to Cake’s ORM and validation would occur there.

One question is how to handle error, since by using a CSV file, I’m assuming you have a fair bit of data to process. Easiest way to do it would be just print out the line number of the csv file where the data failed to validate - and then manually go back and add the lines that failed to save.

A second way would be to pass the data into a form, so you see the data before saving it (and you can correct any errors at that point). You could read-in a single line from the csv file in the controller, then pass it into the template. For stepping though, use an index in the url to step though the records (e.g. /save_csv/add?index=23)
I’ve used this approach for a Excel file with about 50 entries, bit tedious to step though each, but do-able :slight_smile: