[Quick Guide] How To Import CSV File to Custom Table In Magento 2

During the development of Magento 2, we have received a lot of questions from developers about how to Magento 2 import a CSV file to a custom table.

We see that it’s a bit hard for Developers to handle their problems, so today, in this tutorial, we will share three detailed steps.

Steps To Magento 2 Import CSV File To Custom Table

Due to a lack of awareness of different details, a non-technical user will not implement the procedure stated above. When you need to import a custom CSV file to a custom Magento 2 table, a solution entirely eliminates the need to master code. You’ll also be able to import files in other formats, even if they’re compressed into an archive. 

Step 1: Create a simple module for importing CSV files to a custom table

First of all, we create a simple module named Bss_ImportCustomTable; you need:

• Create file module.xml in app/code/Bss/ImportCustomTable/etc

Declare the module created already.

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Bss_ImportCustomTable" setup_version="1.0.0">
<sequence>
                 <module name="Magento_ImportExport" />
            </sequence>
    </module>
</config>

• Create file registration.php in app/code/Bss/ImportCustomTable/

To register the module with the system Magento.

<?php
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Bss_ImportCustomTable',
    __DIR__
);

• Create file import.xml in app/code/Bss/ImportCustomTable/etc

To create the dropdown option “Entity Type”, let’s navigate to “System -> Data Transfer -> Import” then call the model to import data.

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_ImportExport:etc/import.xsd">
    <entity name="messages" label="Custom Import" model="Bss\ImportCustomTable\Model\Import\CustomImport" behaviorModel="Magento\ImportExport\Model\Source\Import\Behavior\Basic" />
</config>

In which:

behaviorModel: Takes the class name of the model class responsible for handling the logic for the import.

label: Label name of the dropdown option.

name: name for the dropdown value.

• Then you need to create the class declared in the import.xml.

Handle the logic, then read and check data in the CSV file to import the custom table.

<?php
namespace Bss\ImportCustomTable\Model\Import;
use Bss\ImportCustomTable\Model\Import\CustomImport\RowValidatorInterface as ValidatorInterface;
use Magento\ImportExport\Model\Import\ErrorProcessing\ProcessingErrorAggregatorInterface;
class CustomImport extends \Magento\ImportExport\Model\Import\Entity\AbstractEntity
{
    const ID = 'entity_id';
    const MESSAGE = 'message_deltail';
    const DATE = 'create_at';
    const TABLE_Entity = 'messages';
    /**
     * Validation failure message template definitions
     *
     * @var array
     */
    protected $_messageTemplates = [
    ValidatorInterface::ERROR_MESSAGE_IS_EMPTY => 'Message is empty',
    ];
     protected $_permanentAttributes = [self::ID];
    /**
     * If we should check column names
     *
     * @var bool
     */
    protected $needColumnCheck = true;
    /**
     * Valid column names
     *
     * @array
     */
    protected $validColumnNames = [
    self::ID,
    self::MESSAGE,
    self::DATE,
    ];
    /**
     * Need to log in import history
     *
     * @var bool
     */
    protected $logInHistory = true;
    protected $_validators = [];
    /**
     * @var \Magento\Framework\Stdlib\DateTime\DateTime
     */
    protected $_connection;
    protected $_resource;
    /**
     * @SuppressWarnings(PHPMD.CouplingBetweenObjects)
     */
    public function __construct(
    \Magento\Framework\Json\Helper\Data $jsonHelper,
    \Magento\ImportExport\Helper\Data $importExportData,
    \Magento\ImportExport\Model\ResourceModel\Import\Data $importData,
    \Magento\Framework\App\ResourceConnection $resource,
    \Magento\ImportExport\Model\ResourceModel\Helper $resourceHelper,
    \Magento\Framework\Stdlib\StringUtils $string,
    ProcessingErrorAggregatorInterface $errorAggregator
    ) {
    $this->jsonHelper = $jsonHelper;
    $this->_importExportData = $importExportData;
    $this->_resourceHelper = $resourceHelper;
    $this->_dataSourceModel = $importData;
    $this->_resource = $resource;
    $this->_connection = $resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);
    $this->errorAggregator = $errorAggregator;
    }
    public function getValidColumnNames()
    {
        return $this->validColumnNames;
    }
    /**
     * Entity type code getter.
     *
     * @return string
     */
    public function getEntityTypeCode()
    {
        return 'messages';
    }
    /**
     * Row validation.
     *
     * @param array $rowData
     * @param int $rowNum
     * @return bool
     */
    public function validateRow(array $rowData, $rowNum)
    {
    $title = false;
    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 Message
     *
     * @return $this
     */
    public function saveEntity()
    {
    $this->saveAndReplaceEntity();
    return $this;
    }
    /**
     * Save and replace data message
     *
     * @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::MESSAGE => $rowData[self::MESSAGE],
                self::DATE => $rowData[self::DATE],
            ];
        }
        if (\Magento\ImportExport\Model\Import::BEHAVIOR_REPLACE == $behavior) {
            if ($listTitle) {
                if ($this->deleteEntityFinish(array_unique(  $listTitle), self::TABLE_Entity)) {
                    $this->saveEntityFinish($entityList, self::TABLE_Entity);
                }
            }
        } elseif (\Magento\ImportExport\Model\Import::BEHAVIOR_APPEND == $behavior) {
            $this->saveEntityFinish($entityList, self::TABLE_Entity);
        }
    }
    return $this;
    }
    /**
     * Save message to customtable.
     *
     * @param array $priceData
     * @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::MESSAGE,
                self::DATE,
        ]);
        }
    }
    return $this;
    }
}

• Create file RowValidatorInterface.php in Bss\ImportCustomTable\Model\Import\CustomImport

<?php
namespace Bss\ImportCustomTable\Model\Import\CustomImport;
interface RowValidatorInterface extends \Magento\Framework\Validator\ValidatorInterface
{
       const ERROR_INVALID_TITLE= 'InvalidValueTITLE';
       const ERROR_MESSAGE_IS_EMPTY = 'EmptyMessage';
    /**
     * Initialize validator
     *
     * @return $this
     */
    public function init($context);
}

Step 2: Custom table with structure

Our table structure:

Step 3: Create a CSV file with format data

Our CSV file with format data:

The Result

That concludes our discussion. You’re set to go if you follow these three simple steps. It’s worth noting that the plugin saves and applies all previously established profiles to subsequent updates. It means you only have to set it up once (per entity per integration).

Conclusion

I hope this article helps you find your solution!

BSS Commerce is one of the leading Magento extension providers and web development services in the world. With experienced and certified Magento developers, we commit to bringing high-quality products and services to optimize your business effectively. Furthermore, we offer FREE Installation – FREE 1-year Support and FREE Lifetime Update for every Magento extension.

CONTACT NOW to let us know your problems. We are willing to support you every time.

Nguyen Quang Trung

+ posts

• Solve Magento 2 Braintree Integration
• Magento Widgets Details
• 3 Ways to Complete Magento 2 Disable Module Requests
• 10 Important Tools to Get Better SEO on Magento (Updated 2020)