PHP Laravel : How to Avoid duplicate data while importing xl/ csv into mysql?

Question

I want to import a csv file into database where name should always unique means if any duplicate title found it should avoid the row and go to the next row. How do I implement using Laravel Controller?

Here's the controller for importing csv/xl I've used :

 public function importExcel()
    {
        if(Input::hasFile('import_file')){
            $path = Input::file('import_file')->getRealPath();
            $data = Excel::load($path, function($reader) {
            })->get();
            if(!empty($data) && $data->count()){
                foreach ($data as $key => $value) {
                    $insert[] = ['title' => $value->title, 'description' => $value->description];
                }
                if(!empty($insert)){
                    DB::table('items')->insert($insert);
                  //  dd('Insert Record successfully.');
                }
            }
        }
        return back();    
    }

why do you want to make it complicated? Mysql has built in tools for dealing with CSV - load data in file — e4c5
– e4c5, Commented Aug 10, 2016 at 5:39
I'm not clear to your answer, please can you elaborate more? — Hola
– Hola, Commented Aug 10, 2016 at 6:00
read up on mysql's LOAD DATA INFILE. It's a matter of using the right tool for the job — e4c5
– e4c5, Commented Aug 10, 2016 at 6:22

Community · Accepted Answer · 2017-05-23 11:53:38Z

2

Some improvement on Matt Borja's answer. This will also check earlier data from table.

public function importExcel()
{
    // Get current data from items table
    $titles = Item::lists('title')->toArray();

    if(Input::hasFile('import_file')){
        $path = Input::file('import_file')->getRealPath();
        $data = Excel::load($path, function($reader) {
        })->get();

        if(!empty($data) && $data->count()){
            $insert = array();

            foreach ($data as $key => $value) {
                // Skip title previously added using in_array
                if (in_array($value->title, $titles))
                    continue;

                $insert[] = ['title' => $value->title, 'description' => $value->description];

                // Add new title to array
                $titles[] = $value->title;
            }

            if(!empty($insert)){
                DB::table('items')->insert($insert);
              //  dd('Insert Record successfully.');
            }
        }
    }
    return back();    
}

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Aug 12, 2016 at 6:10

Sovon

1,8241 gold badge22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hola Over a year ago

I have changed to your code But got the following Error:

FatalThrowableError in ItemController.php line 89: Cannot access protected property Maatwebsite\Excel\Collections\RowCollection::$title

It's in the line if (in_array($value->title, $titles))

Sovon Over a year ago

That error is for $value->title part which was in your original code actually. That is for your code which can't access title value from excel file.

Sovon Over a year ago

Checked the code locally, it's working fine. Didn't get any error like that.

Mayank Pandeyz · Accepted Answer · 2016-08-10 05:19:22Z

2

Before inserting the data into database you can apply array_unique() function on your array variable $insert[]. It will return you the unique array.

Or make the column in the table unique so that it cannot accept duplicate value.

answered Aug 10, 2016 at 5:19

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

1 Comment

Hola Over a year ago

If I put the column key unique, i will see unique key constrain error while importing data, whereas I used array() unique But it's showing the array to string conversion Error!

Matt Borja · Accepted Answer · 2016-08-10 08:39:14Z

0

I typically maintain an index of previously added items, skipping the current iteration (re-adding) if I've indexed it already.

In your example, it would be something along the lines of this:

 public function importExcel()
    {
        // Index titles
        $titles = [];

        if(Input::hasFile('import_file')){
            $path = Input::file('import_file')->getRealPath();
            $data = Excel::load($path, function($reader) {
            })->get();

            if(!empty($data) && $data->count()){
                foreach ($data as $key => $value) {

                    // Skip title previously added using array_key_exists or in_array
                    if (array_key_exists($value->title, $titles))
                        continue;

                    $insert[] = ['title' => $value->title, 'description' => $value->description];

                    // Index added title
                    $titles[$value->title] = true; // or array_push
                }

                if(!empty($insert)){
                    DB::table('items')->insert($insert);
                  //  dd('Insert Record successfully.');
                }
            }
        }
        return back();    
    }

answered Aug 10, 2016 at 8:39

Matt Borja

1,5971 gold badge17 silver badges40 bronze badges

2 Comments

Hola Over a year ago

I have changed it to your code but it still keeps the duplicate value in database!

Matt Borja Over a year ago

Well it's not meant to clean it up if they were previously imported. I would do some "print" debugging at this point to verify that the $insert[] array doesn't contain any duplicate titles. I would also initialize $insert as an empty array somewhere at the beginning of this method to ensure it's not picking up any global vars, etc. If you're looking to clean up your previously imported duplicate records, though, a good start might be to audit those records: SELECT title, COUNT(*) AS total FROM items GROUP BY title ORDER BY total DESC

Collectives™ on Stack Overflow

PHP Laravel : How to Avoid duplicate data while importing xl/ csv into mysql?

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related