How to export custom SKU column from Magento 2 with all content

July 23, 2021 - E-Commerce, Magento 2

Magento 2 export CLI

How often do you face export processes that the default Magento 2 instruments cannot cope with? We regularly witness the inability to run even the most basic tasks, not to mention unique use cases that require additional customizations. For instance, we’ve come across a situation when the default export solution of Magento 2 wasn’t enough to transfer a custom SKU column. What’s the story behind this use case?

Paras Sakariya has added a custom SKU column to a sales order grid. While the corresponding records appear on the backend side, no data is displayed in exported files. Below, explore this situation, providing possible resolutions. Firstly, we focus on the origins of the issue, describing the newly created custom conditions. After that, you will find a solution proposed on StackExchange. However, it is quite complicated and requires lots of technical knowledge. Therefore, we provide our own way to export a custom SKU column from Magento 2 that incorporates the Improved Import & Export Magento 2 extension. You can find more similar issues described in our Cookbook.

Table of contents

1 The Issue
2 The Solution
- 2.1 Reliable Alternative
3 Magento 2 Custom SKU Image Import & Export FAQ

The Issue

Paras applies custom configuration to the sales order grid using the following code snippets:

Package\Company\view\adminhtml\ui_component\sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="sku" class="Krish\Customerabandoned\Ui\Component\Listing\Column\Sku">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="label" xsi:type="string" translate="true">Sku</item>

                </item>
            </argument>
        </column>
    </columns>
</listing>

<?xml version="1.0" encoding="UTF-8"?>

</item>

</argument>

</column>

</columns>

</listing>

Package\Company\Ui\Component\Listing\Column\Sku.php

public function prepareDataSource(array $dataSource)
{
if (isset($dataSource['data']['items'])) {
foreach ($dataSource['data']['items'] as & $item) {

    $order1  = $this->_orderRepository->get($item["entity_id"]);
    $items123 = $order1->getAllItems();
    $skuArray = [];

    foreach ($items123 as $key => $item1) {
        $skuArray[] = $item1->getSku();}
    $export_status = implode (", ", $skuArray);
$item[$this->getData('name')] = $export_status;
}
}

return $dataSource;
}
}

public function prepareDataSource(array $dataSource)

{

if (isset($dataSource['data']['items'])) {

foreach ($dataSource['data']['items'] as & $item) {

$order1 = $this->_orderRepository->get($item["entity_id"]);

$items123 = $order1->getAllItems();

$skuArray = [];

foreach ($items123 as $key => $item1) {

$skuArray[] = $item1->getSku();}

$export_status = implode (", ", $skuArray);

$item[$this->getData('name')] = $export_status;

}

return $dataSource;

}

As a result, he got the new SKU field that is visible on the order screen. However, when Paras tried to export the content of the grid, the respective column in a CSV file was empty. The following screenshot illustrates the unwanted result:

The Solution

According to Nirav Patel, the improperly implemented SKU column is a monkey on Sakariya’s back. Therefore, he recommends to apply the following changes:

[vendor]/[module]/view/adminhtml/ui_component/sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
    <listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="skus">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortable" xsi:type="boolean">false</item>
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="label" xsi:type="string" translate="true">Skus</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

<?xml version="1.0" encoding="UTF-8"?>

<item name="sortable" xsi:type="boolean">false</item>

</item>

</argument>

</column>

</columns>

</listing>

[vendor]/[module]/etc/adminhtml/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Sales\Model\ResourceModel\Order\Grid\Collection">
        <plugin name="ordercolumn_grid_load_before" type="[vendor]\[module]\Plugin\Admin\Order\Grid" sortOrder="10" disabled="false"/>
    </type>
</config>

<?xml version="1.0"?>

</type>

</config>

After that, you can create a plugin file here: [vendor]/[module]/Plugin/Admin/Order/Grid.php

<?php
namespace [vendor]\[module]\Plugin\Admin\Order;

use Magento\Backend\Model\Auth\Session;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection;
use Magento\User\Model\ResourceModel\User\Collection as UserCollection;

class Grid extends \Magento\Framework\Data\Collection
{
    protected $coreResource;

    protected $adminUsers;

    public function __construct(
        EntityFactoryInterface $entityFactory,
        ResourceConnection $coreResource,
        UserCollection $adminUsers
    ) {
        parent::__construct($entityFactory);
        $this->coreResource = $coreResource;
        $this->adminUsers = $adminUsers;
    }

    public function beforeLoad($printQuery = false, $logQuery = false)
    {
        if ($printQuery instanceof Collection) {
            $collection = $printQuery;

            $joined_tables = array_keys(
                $collection->getSelect()->getPart('from')
            );

                $collection->getSelect()
                    ->columns(
                        array(
                            'skus' => new \Zend_Db_Expr('(SELECT GROUP_CONCAT(`sku` SEPARATOR " & ") FROM `sales_order_item` WHERE `sales_order_item`.`order_id` = main_table.`entity_id` GROUP BY `sales_order_item`.`order_id`)')
                        )
                    );

        }
    }
}

<?php

namespace [vendor]\[module]\Plugin\Admin\Order;

use Magento\Backend\Model\Auth\Session;

use Magento\Framework\App\ResourceConnection;

use Magento\Framework\Data\Collection\EntityFactoryInterface;

use Magento\Sales\Model\ResourceModel\Order\Grid\Collection;

use Magento\User\Model\ResourceModel\User\Collection as UserCollection;

class Grid extends \Magento\Framework\Data\Collection

{

protected $coreResource;

protected $adminUsers;

public function __construct(

EntityFactoryInterface $entityFactory,

ResourceConnection $coreResource,

UserCollection $adminUsers

) {

parent::__construct($entityFactory);

$this->coreResource = $coreResource;

$this->adminUsers = $adminUsers;

}

public function beforeLoad($printQuery = false, $logQuery = false)

{

if ($printQuery instanceof Collection) {

$collection = $printQuery;

$joined_tables = array_keys(

$collection->getSelect()->getPart('from')

);

$collection->getSelect()

->columns(

array(

'skus' => new \Zend_Db_Expr('(SELECT GROUP_CONCAT(`sku` SEPARATOR " & ") FROM `sales_order_item` WHERE `sales_order_item`.`order_id` = main_table.`entity_id` GROUP BY `sales_order_item`.`order_id`)')

)

);

}

That’s it. In this particular case, the changes described above will help you export a custom SKU column from Magento 2 with all content. How about other similar issues?

Be more careful about what changes you apply. The smallest nuances usually impact unexpected areas of your Magento 2 website. Therefore, all changes should be well-thought and adequately tested.

However, there is a robust data transfer solution that can be easily adjusted for any custom changes that you implement in your Magento 2 project. Let’s see what perks it delivers.

Reliable Alternative

The Improved Import & Export extension lets you transfer data between a Magento 2 website of any complexity and external systems of your choice. The module introduces an entirely new level of synchronization compared to the default Magento tools and most third-party solutions. It dramatically enhances the default capabilities of the platform, delivering numerous features that are exclusively available in our plugin.

The Improved Import & Export module enables you to export SKUs from your unique column within just a few clicks. You only need to discuss the customization with our team.

Besides, the extension allows for automating data transfers in two different ways. First of all, Improved Import & Export lets you use a cron scheduler. It is possible to create custom timespans or choose among provided intervals. At the same time, you can enable real-time transfer with the system of triggers and rules.

For various attribute-related issues, the Improved Import & Export extension offers presets, which introduce the ability to match third-party designations to ones used in Magento 2 within just a few clicks. Moreover, you can apply the accordant changes manually in the extension’s interface. Keep in mind that this feature is also suitable for both attributes and their values.

Another crucial enhancement that the Improved Import & Export module introduces is the ability to transfer a wide array of file transfers. While Magento works with CSV files only, our plugin also supports XML, Excel, JSON, and ODS formats. Besides, it knows how to behave with ZIP and TAR archives. Numerous file sources are under your disposal, such as local and remote servers, cloud storage options, Google Sheets, APIs, etc. Many more features and opportunities associated with our extension are described here:

Get Improved Import & Export Magento 2 Extension

Magento 2 Custom SKU Image Import & Export FAQ

Below, you will find several more facts about the Improved Import & Export Magento 2 extension and its ability to transfer custom SKU data.

How to import and export custom SKUs to Magento 2 automatically?

If you need to transfer custom SKUs to or from Magento 2 on a regular basis, it is necessary to install a third-party tool. Unfortunately, the default import/export solutions don’t let you create import/export jobs that run on schedule. Therefore, we recommend you use the Improved Import & Export extension to import and export custom SKUs to Magento 2 automatically. The extension leverages cron so that you can create a schedule of updates. Next, the module does everything for you.

How to import custom SKUs to Magento 2 from external systems?

While the default Magento 2 import mechanisms block the import of third-party data that doesn’t follow the system’s requirements, you can use the Improved Import & Export extension to circumvent these restrictions. The module lets you create a mapping scheme matching any external values to the default Magento 2 attributes, including custom SKUs. Consequently, any external data is easily adapted to the native standards of your e-commerce website. The most intriguing aspect is that you do everything right in your admin.

How to import or export custom SKUs to Magento 2 via API?

You need to use the Improved Import & Export Magento 2 extension to import or export custom SKUs to Magento 2 via API. Our extension lets you create the connection between the two systems transferring any data, including custom SKUs. Any import job provides an interface where you can specify the parameters necessary to put the two systems together.

How to import custom SKUs to Magento 2 via ODS files?

If custom SKUs are stored in an ODS file, you no longer need to convert it to a CSV file to fit the Magento 2 requirements. With the Improved Import & Export Magento 2 extension, it is possible to import ODS files to your ecommerce website without any necessity to run the conversion. Note that you can also export custom SKUs from Magento 2 with the help of ODS files.

How to import custom SKUs to Magento 2 via XLSX files?

If custom SKUs are stored in an XLSX file, you no longer need to convert it to a CSV file to fit the Magento 2 requirements. With the Improved Import & Export Magento 2 extension, it is possible to import XLSX files to your ecommerce website without any necessity to run the conversion. Note that you can also export custom SKUs from Magento 2 with the help of XLSX files.

How to import custom SKUs to Magento 2 via JSON files?

If custom SKUs are stored in an ODS file, you no longer need to convert it to a CSV file to fit the Magento 2 requirements. With the Improved Import & Export Magento 2 extension, it is possible to import JSON files to your ecommerce website without any necessity to run the conversion. Note that you can also export custom SKUs from Magento 2 with the help of JSON files.

How to import custom SKUs to Magento 2 via compressed files?

If custom SKUs are stored in a compressed file, you can also transfer it to Magento 2 with the help of the Improved Import & Export extension. However, there are two requirements to follow. Firstly, your file archive should be in a ZIP or TAR format. Secondly, the compressed data should be stored in a CSV, XML, ODS, XLSX, or JSON file.

How to import custom SKUs to Magento 2 from Google Sheets?

If custom SKUs are stored in Google Sheets, it is no longer a problem. There is no need to export them from the platform and then transfer the output to Magento 2. The Improved Import & Export lets you move the corresponding information straight from Google Sheets to your e-commerce website. It is only necessary to specify the address of the corresponding data table.

How to import custom SKUs to Magento 2 from Dropbox?

In addition to Google Sheets, the Improved Import & Export module lets you transfer custom SKUs to Magento 2 from Dropbox. Your data should be stored in CSV, XML, ODS, XLSX, or JSON. If it is a compressed file, it should be in a ZIP or TAR format. Note that you can also export custom SKUs from Magento 2 straight to Dropbox.

How to import custom SKUs to Magento 2 from Google Drive?

In addition to Google Sheets and Dropbox, the Improved Import & Export module lets you transfer custom SKUs to Magento 2 from Google Drive. Your data should be stored in CSV, XML, ODS, XLSX, or JSON. If it is a compressed file, it should be in a ZIP or TAR format. Note that you can also export custom SKUs from Magento 2 straight to Google Drive.