Magento 2 : How to Import CSV File to Custom Table?

Magento 2 : Import CSV file to custom table
Today we are going to talk about how to import csv file to custom table in Magento 2. We had the similar requirement on our Magento 2 product shelf life extension where we had to allow administrators to load stock for different product batches based on their expiry date.

As usual lets crack on with our step by step implementation to implement Magento 2 how to import CSV file to custom table -:

Step 1 – Create file import.xml in app/code/Scommerce/Custom/etc

 <?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_ImportExport:etc/import.xsd"> <entity name="custom_import" label="Custom Import" model="ScommerceCustomModelImportCustomImport" behaviorModel="MagentoImportExportModelSourceImportBehaviorBasic" />
</config>

name: Name for the dropdown value

label: Label name of the dropdown option

model: Class name of the model which will be responsible to handle the logic for the import

behaviorModel: Class name of the Magento Basic import behaviour model class

Step 2 – Create file CustomImport.php under app/code/Scommerce/Custom/Model/Import folder

<?php namespace ScommerceCustomModelImport; use ScommerceCustomModelImportCustomImportRowValidatorInterface as ValidatorInterface; use MagentoImportExportModelImportErrorProcessingProcessingErrorAggregatorInterface; use MagentoFrameworkAppResourceConnection; class CustomImport extends MagentoImportExportModelImportEntityAbstractEntity { const ID = 'id'; const NAME = 'name'; const DESC = 'description'; const TABLE_Entity = 'custom_table'; /** * Validation failure message template definitions * * @var array */ protected $_messageTemplates = [ ValidatorInterface::ERROR_TITLE_IS_EMPTY => 'Name is empty', ]; protected $_permanentAttributes = [self::ID]; /** * If we should check column names * * @var bool */ protected $needColumnCheck = true; protected $groupFactory; /** * Valid column names * * @array */ protected $validColumnNames = [ self::ID, self::NAME, self::DESC, ]; /** * Need to log in import history * * @var bool */ protected $logInHistory = true; protected $_validators = []; /** * @var MagentoFrameworkStdlibDateTimeDateTime */ protected $_connection; protected $_resource; /** * @SuppressWarnings(PHPMD.CouplingBetweenObjects) */ public function __construct( MagentoFrameworkJsonHelperData $jsonHelper, MagentoImportExportHelperData $importExportData, MagentoImportExportModelResourceModelImportData $importData, MagentoFrameworkAppResourceConnection $resource, MagentoImportExportModelResourceModelHelper $resourceHelper, MagentoFrameworkStdlibStringUtils $string, ProcessingErrorAggregatorInterface $errorAggregator, MagentoCustomerModelGroupFactory $groupFactory ) { $this->jsonHelper = $jsonHelper; $this->_importExportData = $importExportData; $this->_resourceHelper = $resourceHelper; $this->_dataSourceModel = $importData; $this->_resource = $resource; $this->_connection = $resource->getConnection(MagentoFrameworkAppResourceConnection::DEFAULT_CONNECTION); $this->errorAggregator = $errorAggregator; $this->groupFactory = $groupFactory; } public function getValidColumnNames() { return $this->validColumnNames; } /** * Entity type code getter. * * @return string */ public function getEntityTypeCode() { return 'custom_import'; } /** * Row validation. * * @param array $rowData * @param int $rowNum * @return bool */ public function validateRow(array $rowData, $rowNum) { if (isset($this->_validatedRows[$rowNum])) { return !$this->getErrorAggregator()->isRowInvalid($rowNum); } $this->_validatedRows[$rowNum] = true; return !$this->getErrorAggregator()->isRowInvalid($rowNum); } /** * Create Advanced message data from raw data. * * @throws Exception * @return bool Result of operation. */ protected function _importData() { $this->saveEntity(); return true; } /** * Save entity * * @return $this */ public function saveEntity() { $this->saveAndReplaceEntity(); return $this; } /** * Replace entity data * * @return $this */ public function replaceEntity() { $this->saveAndReplaceEntity(); return $this; } /** * Deletes entity data from raw data. * * @return $this */ public function deleteEntity() { $listTitle = []; while ($bunch = $this->_dataSourceModel->getNextBunch()) { foreach ($bunch as $rowNum => $rowData) { $this->validateRow($rowData, $rowNum); if (!$this->getErrorAggregator()->isRowInvalid($rowNum)) { $rowTtile = $rowData[self::ID]; $listTitle[] = $rowTtile; } if ($this->getErrorAggregator()->hasToBeTerminated()) { $this->getErrorAggregator()->addRowToSkip($rowNum); } } } if ($listTitle) { $this->deleteEntityFinish(array_unique($listTitle),self::TABLE_Entity); } return $this; } /** * Save and replace entity * * @return $this * @SuppressWarnings(PHPMD.CyclomaticComplexity) * @SuppressWarnings(PHPMD.NPathComplexity) */ protected function saveAndReplaceEntity() { $behavior = $this->getBehavior(); $listTitle = []; while ($bunch = $this->_dataSourceModel->getNextBunch()) { $entityList = []; foreach ($bunch as $rowNum => $rowData) { if (!$this->validateRow($rowData, $rowNum)) { $this->addRowError(ValidatorInterface::ERROR_TITLE_IS_EMPTY, $rowNum); continue; } if ($this->getErrorAggregator()->hasToBeTerminated()) { $this->getErrorAggregator()->addRowToSkip($rowNum); continue; } $rowTtile= $rowData[self::ID]; $listTitle[] = $rowTtile; $entityList[$rowTtile][] = [ self::ID => $rowData[self::ID], self::NAME => $rowData[self::NAME], self::DESC => $rowData[self::DESC], ]; } if (MagentoImportExportModelImport::BEHAVIOR_REPLACE == $behavior) { if ($listTitle) { if ($this->deleteEntityFinish(array_unique( $listTitle), self::TABLE_Entity)) { $this->saveEntityFinish($entityList, self::TABLE_Entity); } } } elseif (MagentoImportExportModelImport::BEHAVIOR_APPEND == $behavior) { $this->saveEntityFinish($entityList, self::TABLE_Entity); } } return $this; } /** * Save custom data. * * @param array $entityData * @param string $table * @return $this */ protected function saveEntityFinish(array $entityData, $table) { if ($entityData) { $tableName = $this->_connection->getTableName($table); $entityIn = []; foreach ($entityData as $id => $entityRows) { foreach ($entityRows as $row) { $entityIn[] = $row; } } if ($entityIn) { $this->_connection->insertOnDuplicate($tableName, $entityIn,[ self::ID, self::NAME, self::DESC ]); } } return $this; } /** * Delete custom data. * * @param array $entityData * @param string $table * @return $this */ protected function deleteEntityFinish(array $ids, $table) { if ($table && $listTitle) { try { $this->countItemsDeleted += $this->_connection->delete( $this->_connection->getTableName($table), $this->_connection->quoteInto('id IN (?)', $ids) ); return true; } catch (Exception $e) { return false; } } else { return false; } }
}

Step 3 – Create validator interface file RowValidatorInterface.php under app/code/Scommerce/Custom/Model/Import/CustomImport folder

 <?php
namespace ScommerceCustomModelImportCustomImport;
interface RowValidatorInterface extends MagentoFrameworkValidatorValidatorInterface
{ const ERROR_INVALID_TITLE= 'InvalidValueTITLE'; const ERROR_MESSAGE_IS_EMPTY = 'EmptyMessage'; /** * Initialize validator * * @return $this */ public function init($context);
}

Step 4 – Create custom table named custom_table with the following columns

ID
Title
Description

Step 5 – Create CSV file using the following columns and upload in Magento 2 admin -> System -> Data Transfer -> Import -> Select “Custom Data Import” from dropdown list