Magento 2: How to Update Existing Record If Available In the DB Table Or Not then Insert New Row From CSV File

Question

I have tried to import CSV with some following Data, My code is working for insert new record in the table which is in the CSV but not updating the existing record. Everytime when I upload the CSV, it create the new record in Database.

For Example: Suppose, CSV file contains 2 Columns Username, Price with 3 data rows.

• Two rows are new which is not available in XYZ table
• And One Row are already in the XYZ table

So, I want to insert two new rows which is not in the table and update the existing record from the csv data. Also I want to check both column for check existing record in the table.

My Code Sample.

Controller

    <?php

       namespace Custom\Module\Controller\Product;

       use Custom\Module\Model\ItemsFactory;

       class Upload extends \Magento\Framework\App\Action\Action
       {
        public function __construct(
        Context $context,
        \Custom\Module\Model\ItemsFactory  $items,
        ) 
        {
           $this->_items = $items;
        }

            public function execute()
             {
                $err = [];
                $post   =   $this->getRequest()->getPost();
                $filename = $_FILES["prod_massupload"]["tmp_name"];
                $items = $this->_items->create();
                $row = 1;
                 if (($handle = fopen($filename, "r")) !== FALSE) {
                      fgetcsv($handle);
                        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    
                                $num = count($data);
                                $row++;
    
                                $items->setData('username',$data[0]);
                                $items->setData('price',$data[1]);
                                $saveData = $items->save(); 
                                $items->unsetData();
                                
                          }
                        fclose($handle);
                        if (!empty($err)) {
                            $errors = implode(',', $err);
                            $this->messageManager->addError($errors);
                        } else {
                            $this->messageManager->addSuccess('Data Imported successfully');
                        }
                    } 
               }

CSV Sample:

Any help will be very appreciated!!

Answer 1

Welcome to Magento Stack Exchange Community.

To be able to update an existing record in the table (instead of adding new record every time), you must have some way to uniquely identify the record.

If you are using a primary key column in the database table, then you can write something like this before $saveData = $items->save();

$items->setId($id);

If there is no primary key column, then you need write custom query in both cases (insert and update), since Magento models expect that you have a primary key defined for your table (in order to update the record).

Custom update query would be something like this:

$connection  = $this->resource->getConnection();
$data = ["key1"=>"value1","key2"=>"value2"]; // Key_Value Pair
$username = username-to-be-updated;
$where = ['username = ?' => $username];

$tableName = $connection->getTableName("Your_Tablename");
$connection->update($tableName, $data, $where);

I hope it will help.