I am using Laravel maatwebsite 3.1.3 row validation to validate a csv file import. But the WithValidation concern in my Imports php file is constantly throwing an error that the 2nd import row's last value is required even though the value is present.
When I remove the validation the import is working fine.
My controller calls the import function:
public function doImport(Request $request) {
$this->validate($request, [
'import_file' => 'required|mimes:xls,csv,xlsx,txt' // txt is needed for csv mime type validation
]);
if($request->file('import_file')) {
try {
Excel::import(new FPImport, $request->file('import_file'));
return redirect('admin/fertil/products')->with('success', 'Invoer Sukses');
} catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
$msg = '';
$failures = $e->failures();
foreach ($failures as $failure) {
$msg = 'Ry '.$failure->row(); // row that went wrong
$msg = $msg.' vir hoof '.$failure->attribute(); // either heading key (if using heading row concern) or column index
$msg = $msg.'. '.$failure->errors()[0]; // Actual error messages from Laravel validator
// $msg = $msg.' : met waarde '.$failure->values(); // The values of the row that has failed: not available in version
}
return back()->with('error', $msg);
}
}
return back()->with('error', 'Lêer nie gevind nie');
}
My import class:
class FPImport implements ToModel, WithValidation, WithStartRow
{
public function startRow(): int
{
return 2;
}
public function model(array $row)
{
return new FertilProd([
//
'register_no'=> $row[0],
'product_type'=> $row[1], // id of prodtype tb
'formulation_type'=> $row[2], // id of formulation type tb
'description'=> $row[3],
'formulation_metric'=> $row[4], // id of formulation metric tb
'SAP_no'=> $row[5],
'N'=> $row[6],
'P'=> $row[7],
'K'=> $row[8],
'Zn'=> $row[9],
'Ca'=> $row[10],
'Mg'=> $row[11],
'S'=> $row[12],
'B'=> $row[13],
'Cu'=> $row[14],
'Fe'=> $row[15],
'Mn'=> $row[16],
'Mo'=> $row[17],
'Ni'=> $row[18],
'Se'=> $row[19],
'SG'=> $row[20],
]);
}
public function rules(): array
{
return [
'register_no' => 'required|string',
'product_type' => 'required|integer|min:1', //validate unsigned integer
'formulation_type' => 'required|integer|min:1',
'description'=> 'required|string',
'formulation_metric'=> 'required|integer|min:1',
'SAP_no'=> 'required|string',
'N' => 'required|numeric|between:0,99.9999',
'P' => 'required|numeric|between:0,99.9999',
'K' => 'required|numeric|between:0,99.9999',
'Zn' => 'required|numeric|between:0,99.9999',
'Ca' => 'required|numeric|between:0,99.9999',
'Mg' => 'required|numeric|between:0,99.9999',
'S' => 'required|numeric|between:0,99.9999',
'B' => 'required|numeric|between:0,99.9999',
'Cu' => 'required|numeric|between:0,99.9999',
'Fe' => 'required|numeric|between:0,99.9999',
'Mn' => 'required|numeric|between:0,99.9999',
'Mo' => 'required|numeric|between:0,99.9999',
'Ni' => 'required|numeric|between:0,99.9999',
'Se' => 'required|numeric|between:0,99.9999',
'SG' => 'required|numeric|between:0,99.9999',
];
}
public function customValidationMessages()
{
return [
'register_no' => 'Registrasie kolom moet letter waarde wees',
'product_type' => 'Tipe produk kolom moet volgetal wees',
'product_type' => 'Formulasie tipe kolom moet volgetal wees',
'description'=> 'Beskrywing kolom moet letter waarde wees',
'formulation_metric'=> 'Formulasie mate kolom moet volgetal wees',
'SAP_no'=> 'SAP no kolom moet letter waarde wees',
'N' => 'N kolom moet waarde wees tussen 0,99.9999',
'P' => 'P kolom moet waarde wees tussen 0,99.9999',
'K' => 'K kolom moet waarde wees tussen 0,99.9999',
'Zn' => 'Zn kolom moet waarde wees tussen 0,99.9999',
'Ca' => 'Ca kolom moet waarde wees tussen 0,99.9999',
'Mg' => 'Mg kolom moet waarde wees tussen 0,99.9999',
'S' => 'S kolom moet waarde wees tussen 0,99.9999',
'B' => 'B kolom moet waarde wees tussen 0,99.9999',
'Cu' => 'Cu kolom moet waarde wees tussen 0,99.9999',
'Fe' => 'Fe kolom moet waarde wees tussen 0,99.9999',
'Mn' => 'Mn kolom moet waarde wees tussen 0,99.9999',
'Mo' => 'Mo kolom moet waarde wees tussen 0,99.9999',
'Ni' => 'Ni kolom moet waarde wees tussen 0,99.9999',
'Se' => 'Se kolom moet waarde wees tussen 0,99.9999',
'SG' => 'SG kolom moet waarde wees tussen 0,99.9999',
];
}
}
Update
So I got it to import by using the ToCollection concern. But it is not validating the rows now. As I tested it by placing strings where integers must be and it still tried to import. I received an error from the SQL error handler.
Another update: So I returned to using the the ToModel concern as the Collection did not validate the csv. I also noticed that I did not declare use Illuminate\Validation\Rule; package in my import file. This did not solve it after declaration. I changed my variable reference within the rules() function to the indexes of the rows and then row for row was validated. But now its stuck with a failure message that the 0 field of row 14 is required. The first cell in column one of the 14th row is populated, so the failure->error()[0] message should not be present.
Can someone help me to understand why the rule for import is constantly saying that a field is required even though it is present?
I solved my answer. The last update in my question was the right answer. There was a null value which meant it was required. Although the null value was on a different row as 14. The reason for the wrong row is because the foreach loop on the failures array object will only return the last row after validation.
Firstly I should have declared the package: use Illuminate\Validation\Rule; in my imports file.
Secondly, I should use the index of the rows as the variable for my rules within my rules() function not the models $fillable declared names i.e.:
/**
* @return array
*/
public function rules(): array
{
return [ // use indexes as variables
'0' => 'required|string',
'1' => 'required|integer|min:1', //validate unsigned integer
'2' => 'required|integer|min:1',
'3'=> 'required|string',
'4'=> 'required|integer|min:1',
'5'=> 'required|string',
'6' => 'required|numeric|between:0,99.9999',
'7' => 'required|numeric|between:0,99.9999',
'8' => 'required|numeric|between:0,99.9999',
'9' => 'required|numeric|between:0,99.9999',
'10' => 'required|numeric|between:0,99.9999',
'11' => 'required|numeric|between:0,99.9999',
'12' => 'required|numeric|between:0,99.9999',
'13' => 'required|numeric|between:0,99.9999',
'14' => 'required|numeric|between:0,99.9999',
'15' => 'required|numeric|between:0,99.9999',
'16' => 'required|numeric|between:0,99.9999',
'17' => 'required|numeric|between:0,99.9999',
'18' => 'required|numeric|between:0,99.9999',
'19' => 'required|numeric|between:0,99.9999',
'20' => 'required|numeric|between:0,99.9999',
];
}
It now imports and validates. I could not get the custom validation messages to work though.