CODEWITHSUNDEEP
phplaravellaravel-5.8maatwebsite-excellaravel-excel
Akshay Lokur
Akshay Lokur

Reputation: 7506

Laravel excel validate two columns' combination is not duplicate

I have a requirement wherein I need to ensure that Excel file being uploaded by user does not have duplicate rows w.r.t. 2 particular columns.

Example:

In the snippet below, I want to flag out that row 1 and 2 contain duplicate combination of COMPANY_CODE and CLERK_CODE:

enter image description here

If such duplicate combination is found I want to reject the entire file being imported and let the user know where the problem is.

Any clues?

Upvotes: 3

Views: 1951

Answers (2)

Moh Zulkifli Katili
Moh Zulkifli Katili

Reputation: 1

I solved this using Laravel Excel's built-in function, namely prepareForValidation().

in it I created a new column called composite_column.

public function prepareForValidation($data, $index)
{
    $temp=[];
    foreach (['COMPANY_CODE', 'CLERK_CODE'] as $key => $value) {
        $temp[] = $data[$value];
    }
    $composite_column = implode('_', $temp);
    $data['composite_column']=$composite_column;

    return $data;
}

in the construct section I created a property containing the composite key data row

public $exist_composite_column;

public function __construct()
{
    $this->exist_composite_column = YourModels::
        get()->map(function ($models) {
        return "{$models->COMPANY_CODE}_{$models->CLERK_CODE}";
    });
}

in the validation section I validate the created column composite_column

public function rules(): array
{
    return [
        ...
        'composite_column' => [//variable
            'required',
            'strings',
            Rule::notIn($this->exist_composite_column),//variable
        ],
    ];
}

Upvotes: 0

Akshay Lokur
Akshay Lokur

Reputation: 7506

Not sure if Maat/Laravel Excel can solve this easily. So, I went ahead and created associative array with key as concatenation of two columns which I don't want to repeat in Excel.

Then I check manually using a foreach loop that if key exists in associative array, it means there is duplicate entry in Excel.

Some Sample code for reference below:

        $array = Excel::toArray(new MyExcelImport(), request()->file);

        $assoc_array = array();
        foreach ($array[0] as $key => $value) {
            $new_key = $value['company_code'] . $value['clerk_code'];

            // Presence of combination of company_code and clerk_code in the assoc_array indicates that
            // there is duplicate entry in the Excel being imported. So, abort the process and report this to user.
            if (array_key_exists($new_key, $assoc_array)) {
                return response()->json("Combination of company_code: " .
                    $value['company_code'] .
                    " and clerk_code: " .
                    $value['clerk_code'] .
                    " is duplicate in the file being imported. Please correct same and retry upload.", 422);
            }

            $assoc_array[$new_key] = $value;
        }

Hope this helps someone with similar needs!

Upvotes: 1

Related Questions