Magento 2 Import: Field Separator & Multiple Value Separator

From the very beginning of our history, data transfers with all their aspects and related processes have been the main focus of our company. We’ve explored lots of topics that describe Magento 2 import and export. Along with that, we’ve expanded our interest to other areas of Magento 2 and went far beyond the ecosystem. However, in the following article,  you will find several basic nuances of moving information to your e-commerce website.

Below, we continue exploring Magento 2 import and export processes and explain their two components: field separator and multiple-value separator. What is the purpose of both? What separator types are supported? Are there any alternatives? You will find the answers below.

The following blog post touches upon the topic of different separator types that you can use in Magento 2. After that, we proceed to a guide on how to configure a multiple-value separator in Magento 2. Also, you will find out what to do with field separators in your data files. Various multiple value separator and field separator improvements associated with the Improved Import & Export extension are also mentioned in the article below.

Magento 2 Import: Multiple-Value Separator & Field Separator

Both field separators and multiple-value separators are critical for every import and export process. They define how the default Magento 2 import tool parses a CSV file.

While a multiple-value separator splits many values for the same column, a field separator shows how columns and fields are separated.

Different Separator Types

Magento 2 introduces multiple options for separating data in a CSV file. You can use the following characters:

• Comma – “,”
• Vertical bar – “|”
• Colon – “:”

How to Configure Multiple-Value Separator & Field Separator in Magento 2

Both field separator and multiple value separator can be configured for import processes in your admin. Go to System -> Import and select an entity type under Import Settings. A bunch of new options will appear.

In Import Behaviour, you can find two fields: Field separator and Multiple value separator. Note that you should specify both separators every time a new import process is required (as well as configure other parameters).

Luckily, there is a solution that lets you configure these and other options only once. Below, we’d like to introduce you to a universal import and export tool that enables you to leverage Magento 2 field and multiple value separators more efficiently.

Multiple-Value Separator & Field Separator Improvements for Magento 2

Meet the Improved Import & Export Magento 2 extension – your number-one time-saver for all data transfers. Our module offers lots of features that are not represented in Magento 2, but let’s take a look at multiple value and field separator improvements.

Like Magento 2, Improved Import & Export supports the same three separator types: comma, vertical bar, and colon. But you no longer need to specify them every time a new update is required. Our extension allows the creation of separate import jobs for each entity for every integration. Below, you can see a grid with import jobs.

As a result, it is possible to configure individual field and multiple-value separators for every Magento 2 import process. Select a job from the grid above or add a new one. Configure its parameters. You can find separators in Import Behavior:

Specified settings are saved for further updates within a particular job so that you configure it only once.  

Furthermore, the Improved Import & Export extension provides the ability to automate data transfers via schedules or event-based triggers. At the same time, the module offers various mapping features, so the problem of different attribute standards can be solved within a few clicks. Yes, you no longer have to edit data files in a file editor. Instead, it is only necessary to select a preset that automatically matches external attributes to ones used internally. Let’s explore the extension’s functionality in more detail.

Improved Import & Export Exclusive Features

After facing integrations, data transfers, and synchronizations with external systems based on the default Magento 2 tools, we’ve developed the Improved Import & Export extension to simplify import and export processes related to Magento 2.  You know how difficult and time-consuming the default routine is, but the Improved Import & Export extension lets you automate it.

Below, we shed light on the module’s core features that dramatically improve the import and export processes of the platform. Note that most of them are not even represented in Magento 2 by default. Let’s start the exploration from the automation of data transfers.

Automated Import & Export Processes

As mentioned above, the Improved Import & Export Magento 2 extension lets you automate data transfers via schedules or event-based triggers. As a result, you don’t need to control all import and export processes manually.

Schedules

Schedules are intuitive and straightforward. Being based on cron, they allow you to create any custom update schedule using the default cron syntax.

At the same time, the Improved Import & Export Magento 2 extension offers a list of predefined intervals. Select one and apply it to your import/export job. You may customize it if necessary.

Profiles without schedules are also available. Use them if there is no need to run frequent updates, a single data transfer is required, or updates are provided asynchronously.

Also,  note that you can launch every import/export process whenever you want.

As you can see from the picture below, configuring a schedule is a very straightforward process:

Events

As for event-based triggers, they represent an alternative way of automating import and export processes which enables real-time updates. The following example illustrates the provided possibilities:

• A customer places an order on your e-commerce website.
• This event is recognized as a trigger.
• The trigger exports order data to an external ERP system.

Note that every data transfer (import or export) launched by the trigger occurs every time specified conditions are met.

It is also necessary to mention that the Improved Import & Export Magento 2 extension lets you use both system and custom events as triggers. After a trigger is defined, create conditions to make its work more efficient. You can find more information here: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

Although Magento 2 lacks automated import and export processes, now you know how to add the missing functionality to your website. However, the platform doesn’t prevent problems caused by different attribute standards. As a result, it is always necessary to edit multiple data files every time a new update is required. If unsupported attributes or values are provided to Magento 2, the import process is usually interrupted.  

At the same time, most external systems reject your exported files unless you edit them according to the provided requirements. However, a seamless connection between your e-commerce store and any external systems can be achieved if you use the Improved Import & Export Magento 2 extension. Ou module dramatically simplifies the Magento 2 import and export processes in terms of mapping. Several significant enhancements are described below.

Mapping Presets

With our extension, you can solve the problem of different attribute standards within a few clicks. The Improved Import & Export module offers a list of presets that match external designations to the corresponding internal ones automatically. You choose a preset and start the import procedure.

As for the extension, it analyzes the input data and replaces all external attributes with ones used in Magento 2. Thus, admin duties are reduced to selecting and applying a preset related to a particular integration.

As for the default attribute editing, you have to work with data files in a third-party app. The more attributes are included – the more pain you get since it is necessary to check all of them finding and replacing external designations. Luckily, the Improved Import & Export Magento 2 module lets you forget about that. The image below illustrates how simple the process of attribute mapping is:

Matching Interface

As for the manual solution of problems caused by different attribute standards, it is also quite easy. The Improved Import & Export Magento 2 extension lets you map attributes right in your admin without using any third-party editors. Select an external designation and specify the corresponding internal one in front of it. Repeat this action until no third-party attributes are left untouched.

Note that it is necessary to create a matching scheme only once: our module saves and applies it for further data transfers automatically.

It is also worth mentioning that the Improved Import & Export extension provides the ability to set hardcoded attribute values via the “Default Value” column so that specified designations are provided to all items related to the attribute.

Attribute Values Mapping

Since unsupported attribute values sometimes prevent import processes, we’ve decided to solve this problem in the Improved Import & Export Magento 2 extension. The module allows mapping attribute values like the attributes above. Although there are no presets, it is much quicker than doing everything manually in a data file.

Select a third-party designation and specify the internal one in front of it. Note that the process should be applied to all external attributes. For further information, read this article: Attribute Values Mapping.

Attribute Values Editing

Another extension’s feature that simplifies your daily routine related to attribute values is the ability to edit them in bulk. The Improved Import & Export Magento 2 module offers the following rules:

• Add a prefix to multiple attribute values in bulk;
• Add a suffix to numerous attribute values in bulk;
• Split multiple attribute values;
• Merge various attribute values.

The best thing is that you can combine them as well as create conditions to increase the efficiency of bulk editing. For further information, follow this link: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

Category mapping is a feature that saves those merchants who often import product data. With Improved Import & Export, you can easily avoid third-party categories moving product data from a third-party system to your Magento 2 store. Our module lets you match the external categories to the ones used internally right during the import process as follows:

To create new categories on the fly, select a parent category and specify the name of a new one. Both features are described here: Category Mapping.

Attributes On The Fly

The lack of attributes also prevents Magento 2 import processes. To solve the problem, our module lets you create missing attributes on the fly. With the Improved Import & Export Magento 2 extension, you can specify all the necessary parameters using the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full process is described in more detail here: Product attributes import.

Extended Connectivity Options

When it comes to various ways to connect your e-commerce website to external systems, Improved Import & Export is the number one tool in the whole ecosystem. While the standard procedure is based on CSV files, our module introduces support for numerous file formats, API standards, and intermediary services. If an update file isn’t provided in CSV, it causes import errors unless you use Improved Import & Export. If a CSV file is compressed – you won’t import it. But our module changes everything.

Multiple File Standards

With the Improved Import & Export Magento 2 extension, you get numerous file formats that can be imported and exported. Our tool adds support for XML, JSON, ODS, and Excel. And of course, it works with standard CSV files!

Multiple File Sources

To make file transfers more flexible, the Improved Import & Export Magento 2 extension also offers several connection points:

• FTP/SFTP connection allows leveraging a local or remote server as a place of transfers.
• The same functionality is possible with a Dropbox account. Alternatively, you can rely on Box, OneDrive, Google Drive, iCloud, and Amazon Drive.
• When you want to transfer data to your website, a direct URL is enough.

It is also worth mentioning that the extension lets you import compressed files. There is no need to extract archives manually before transferring them into the system. The extension does everything for you.

Alternative Ways of Import & Export

And don’t forget that the Improved Import & Export extension lets you establish direct API integration between your website and other external systems. The module uses REST, SOAP, and GraphQL APIs for synchronizing your e-commerce store with any external systems.

At the same time, it supports different online sheets allowing you to create a bridge between your Magento 2 website and external platforms. With the Improved Import & Export Magento 2 extension, you can use Google Sheets, Office 365 Excel, or Zoho Sheets. Google Sheets-based integration looks as follows:

Watch the video below to see the extension in action:

Magento 2 Field & Value Separator FAQ

Conclusion

You can use three types of separators in Magento 2: commas, vertical bars, and colons. However, the default import tools have many limitations. Thus, you have to specify a field separator and a multiple-value separator every time an update is required. With Improved Import & Export, it is necessary to configure both options only once.

Furthermore, you can use different separators for different import processes since every job has individual settings. For further information about the possibilities of our extension, follow the link below:

Get Improved Import & Export Magento 2 Extension