How to Import Data from MariaDB to Magento 2

Let’s assume that you have some important data update stored in a remote MariaDB database. You need to move the information from the external source to your Magento 2 database. How to do that? There are two ways of achieving your goal. First of all, it is possible to leverage a standard approach when you need to perform MariaDB export, edit the output, and move it into your store. However, there is a more user-friendly direction of doing the same things. Below, you will find the exploration of how to import data from MariaDB to Magento 2 in both ways.

The Common Way of Moving Data from MariaDB to Magento 2

As we’ve just mentioned the standard way of importing data from MariaDB to Magento 2 consists of three steps. Below, we describe each one to illustrate both the complexity of the procedure and lots of drawbacks related to the traditional algorithm. It still works, since you can complete your initial goal, but the process is far from being perfect. Unfortunately, you cannot improve it – only replace with another algorithm which is also described in this article.

CSV Export from MySQL Database

You will never import data from MariaDB to Magento 2 without creating a corresponding output file by the external system. The same is about all databases and MariaDB is not an exception. So, you should leverage your technical skills to complete that goal. It is necessary to be a tech-savvy person to some extent to give it a go. Luckily, the Internet is full of various tutorials that explain how to export a CSV file from MariaDB.

This is not a new question, so you might have already heard about a SELECT statement and its role in exporting a CSV formatted file. For instance, you can create your command on the basis of the following code:

But note that this query exports only selected columns. Your export file is named amfs.csv. Its fields are delimited by a comma. As for non-numeric fields, they are enclosed by double quotes. If it is necessary to escape a character, the slash is used. You can find the file under the /var/lib/mysql/fusionpbx/amfs.csv path – it is the database directory.

Also, note that our file lacks field names in the first row!!!

Consequently, it is necessary to add them manually, which is a considerable inconvenience. Of course, you don’t want to do so. Instead, you want a CSV file that is already suitable for the Magento 2 import, but it is an unimaginable luxury. So, let’s take a look at the second stege of the average approach to the Magento 2 import from MariaDB.

Another way of exporting a database:

Map Third-Party Attributes to Magento 2 Attributes

Chances are, you not only need to add field names manually but apply a lot of editing transforming the external attributes into ones used in Magento 2. The problem occurs since different systems operate using different requirements for the stored information. As a result, each one has its attributes that are not suitable for other platforms. Thus, we need to match data from our CSV file generated in the first step to the Magento 2 standards. The more attributes you have, the more time-consuming this process is.

Note that you won’t import the data file correctly if its attributes haven’t been edited properly. In the worst case scenario, the update will be failed utterly. But let’s assume that you’ve done everything as required and your MariaDB CSV file is ready for the Magento 2 import.

Import MySQL Table into Magento 2

If you think that all the headaches are left behind, we should disappoint you. The last stage of the Magento 2 MariaDB integration is also a problematic one. If everything is ok with your data file, you will seamlessly move it into your database, but you have to do everything manually every time the update is required. The process is slow and inflexible. Read this article for further information: The Complete Guide to Magento 2 Product Import / Export.

Pros & Cons

Let’s summarize all the pros and cons of the traditional data import from MariaDB into Magento 2.

Pros

1. You can move data from a remote MariaDB database to your Magento 2 database.

Cons

1. You need to export a CSV file from MariaDB in an un-user-friendly way.
2. You should edit a file adding field names.
3. You should map attributes manually.
4. You should repeat the whole algorithm every time an update is required.

You must admit that the traditional approach to the Magento 2 data import from MariaDB has too many negative aspects. What do we offer instead?

The Easy Way of Importing Data from MariaDB into Magento 2

The alternative way of moving data from MariaDB into Magento 2 is represented by the Improved Import & Export Magento 2 extension. This module is designed to simplify every import/export process related to Magento 2. And there is a plethora of various tools and features that dramatically enhance the default data migration processes. We will describe everything more detailly below. Now, let’s concentrate on how the module revamps the Magento 2 data import from MariaDB.

MariaDB Database Connection

The first significant improvement related to the user-friendly algorithm is the ability to avoid data files from the transfer. With the Improved Import & Export Magento 2 extension, you can connect to a remote MariaDB database directly. It means that the first step of the traditional approach is eliminated. You neither need to figure out what to do with the SELECT statement nor install and use third-party export utilities for MariaDB. All the necessary data is transferred directly from one database to another. So, how does the import process look now and what about mapping?

Magento 2 Data Import from MariaDB

As you can see, there is no need to edit data files directly. Instead, you can apply all the changes right in the Magento admin. The Improved Import & Export extension lets you either apply mapping presets and match attributes automatically, or do that manually. Both processes are a way more intuitive and user-friendly than the manual editing mentioned above. We describe them more detailly below.

The final vital improvement is the ability to automate all data transfers. You need to create a schedule or specify an event that will launch data import. It is no longer necessary to perform the same steps every time an update is required.

How to Import Data From MariaDB into Magento 2

Let’s see how to import data from MariaDB into Magento 2 with the help of the Improved Import & Export Magento 2 extension.

1. Go to the extension admin, find the import section, and create a new import job.
2. Configure the job: create a schedule (or choose a trigger), choose an imported entity, map attributes, etc.
3. Save and launch the job.

Now, the Improved Import & Export extension will automatically transfer data from MariaDB into Magento 2. What are the pros and cons of this approach?

Pros & Cons

The difference between the traditional algorithm and improved approach is vast. You can still rely on the first one but must admit that we offer a more optimized way of importing data from MariaDB into Magento 2.

Pros

1. You don’t need to export CSV or other files.
2. There is no need to edit data files.
3. The import process is fully automated.
4. You establish a direct connection between Magento 2 and MariaDB.

Cons

1. You need to install a third-party extension.

If you have any difficulties with this process and the further exploration of the module, our specialists will help you.

Now, we’d like to draw your attention to some key facts about MariaDB and then proceed to the description of the Improved Import & Export Magento 2 extension.

Get Improved Import & Export Magento 2 Extension

MariaDB Facts

MariaDB can be compared with MySQL, PostgreSQL, and MongoDB. Being an open source database from the creators of MySQL it is enhanced with enterprise tools and services, optimized for performance, and offers high availability, security, and interoperability.

Typical application scenarios related to MariaDB include web, SaaS, and cloud operational/transactional apps. As for the key customers, they include Google, Booking.com, Wikipedia, Deutsche Telekom, Western Digital, etc. It is also necessary to mention that MariaDB is the default database in the LAMP stack of Red Hat and SUSE Linux. It a part of the cloud stacks by Pivotal Cloud Foundry and Rackspace. Other features are:

• The primary database model is a relational DBMS;
• The secondary database models is a document store; a graph DBMS; a key-value store;
• The developer of MariaDB Enterprise is MariaDB Corporation Ab;
• The developer of the community MariaDB Server is MariaDB Foundation;
• While the initial release of MariaDB is dated back to 2009, its latest version is 10.3.9 published in August 2018;
• MariaDB is Open Source and offers no cloud-based features;
• Its implementation languages are C and C++;
• Server operating systems are FreeBSD, Linux, Solaris, and Windows;
• The data scheme is supported including dynamic columns;
• MariaDB supports typing, XML, secondary indexes, SQL;
• APIs and other access methods include the proprietary native API, ADO.NET, JDBC, and ODBC;
• There is also a plethora of supported programming languages: Ada, C, C#, C++, D, Eiffel, Erlang, Go, Java, JavaScript (Node.js), Objective-C, PHP, Python, Ruby, etc;
• Server-side scripts and triggers are supported;
• Partitioning methods include horizontal partitioning, sharding with Spider storage engine or Galera cluster;
• Replication methods include master-master and master-slave replication;
• MapReduce is not available;
• Consistency concepts are represented by Immediate Consistency;
• Foreign keys are supported;
• Transaction concepts are described by ACID;
• Concurrency, durability, and in-memory capabilities are supported;
• User concepts are represented by fine-grained access rights according to SQL-standard.

Improved Import & Export Magento 2 Extension

Now, we’d like to shed light on various features of the Improved Import & Export Magento 2 extension. Let’s take a look at how our module enhances the default connectivity options of Magento.

Extended Connectivity

The following chapter is split into three sections: Supported File Formats, Supported File Sources, and Alternative Ways of Import/Export. Each one shows improvements regarding the default import/export tools of Magento.

Supported File Formats

While our favorite e-commerce platform works with CSV files only, the Improved Import & Export extension adds multiple more standards into the game. With our module, you can freely import and export data via XML, JSON, ODS, and Excel. There is no need to convert them to CSV every time an import process is required. The opposite operations are eliminated as well.

Supported File Sources

In addition to multiple file formats, the Improved Import & Export Magento 2 extension works with numerous file sources. You are no longer limited to a direct upload. Feel free to choose a source that is suitable for your particular needs.

Firstly, the Improved Import & Export Magento 2 extension supports FTP/SFTP. It means that you can use your local server as a file source. Import data files from it or place the output there. If a local server is not suitable for your business goals, use a remote one instead. Freely move data through it.

Secondly, you can use Dropbox for the same purposes. Create a new account or use an existing one to import data to and export it from.

Thirdly, the Improved Import & Export Magento 2 extension supports direct URL uploads. You only need to specify a file’s URL, and the module will import it into the system.

Also, note that our plugin unpacks compressed files automatically before import. Thus, you no longer have to do that manually before every update.

Alternative Ways of Import/Export

Another important aspect of the Improved Import & Export Magento 2 extension is the ability to avoid data files from transfers. And there are two ways to achieve this goal. First of all, you can use some intermediaries, such as Google Sheets, Office 365 Excel, or Zoho Sheet. The Google Sheets Magento 2 integration is illustrated below:

The second way to avoid data files from import and export processes is represented by the support for such API connections as REST, SOAP, and GraphQL. You can freely apply all extension features described below to any connection type.

Import & Export Automation

As mentioned above, the Improved Import & Export Magento 2 extension offers two ways of data transfer automation. You can leverage cron or Magento 2 events and observers.

Flexible Cron Schedules

With the full support for cron, the Improved Import & Export Magento 2 module allows creating any custom update schedules. You only need to understand the default cron syntax. At the same time, there is a list of predefined intervals. Choose one and customize it if necessary.

Besides, you can create import & export jobs that have no schedules. They are useful for one-time data migration. If necessary, you can add a schedule lately.

At the same time, the Improved Import & Export Magento 2 extension provides the ability to launch scheduled profiles asynchronously. For instance, you’ve just received a file with a significant update, but the related import process is scheduled for the next week. There is no need to wait since you can launch the profile manually within just a few clicks.

Here is how you can configure a schedule:

Events as Triggers

With the Improved Import & Export module, multiple actions may be considered triggers. You can use either native Magento 2 events or create your own. Create special conditions to make the automation more precise.  To read more about event-based import and export processes, follow this link:  How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Functionality

Our extension offers two ways to map attributes: you can use either mapping presets or match everything within a particular interface. Let’s explore each one.

Mapping Presets

Our team has created presets that dramatically simplifies attribute editing. You no longer have to edit data files manually. Furthermore, it is possible to avoid manual matching in the extension’s interface. You only need to select a preset related to your connection and apply it within a few clicks. Below, you can see how to import data from Magento 1 to Magento 2 by using a mapping preset to match older attributes to their newer incomers:

Mapping Interface

At the same time, it is possible to do everything manually. Select an external attribute and match it to the one used internally. The procedure is as simple as specifying one designation in the first column and its corresponding alternative in the next one. Besides, you can set a hardcoded attribute value that will be added to the attribute. The process looks as follows:

Attribute Values Mapping

Additionally to attributes, you can map their values. The process is similar to the one described above. You need to select and external value and match it to the corresponding one used internally.

It is possible to map multiple external values to the same internal one. For instance, you have an external attribute “color” with two values: “baby-blue” and “dark-blue”. In your database, there is only one corresponding value – “blue”. Match each external designation to the one used internally. You can find more information regarding attribute values mapping here: Attribute Values Mapping.

Attribute Values Editing

If matching capabilities are not enough, the Improved Import & Export module allows you to edit attribute values in bulk. The extension offers multiple rules that can be combined. First of all, you can add a prefix to multiple values. For instance, add “dark” to all “green” values.

The same logic can be applied to suffixes. Besides, you can merge and split data. If information regarding multiple values is stored in one cell, split it within a few clicks.

The Improved Import & Export plugin lets you create conditions to apply these changes more efficiently. The corresponding features are described here: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

And you can always match external categories to ones used internally. Alternatively, the Improved Import & Export Magento 2 extension provides the ability to generate missing sections of your catalog right during the import process. Both features are described in this article:  Category Mapping.

Attributes On The Fly

And it is possible to create missing attributes on the fly. Add parameters necessary to create a missing attribute, and the extension will do everything for you. Use the following general format to specify data:

Attribute|attribute_property_name:attribute_property_value|…

Follow this link to discover the full algorithm of attributes creation on the fly: Product attributes import.

Watch the video below to see the extension in action:

Final Words

As you can see, importing data from MariaDB into Magento 2 is no longer a problem. With the Improved Import & Export Magento 2 extension, you can connect your e-commerce store to a remote database directly transferring all the necessary information.

There is no need to export a CSV from MariaDB so that data file editing is eliminated. Furthermore, you don’t need to relaunch the import process every time an update is provided. Our extension lets you automate data transfers in multiple ways. Besides, issues caused by different attribute standards can be solved within a few clicks with mapping presets.

You can even match external attribute values and categories to ones used within your Magento 2 database. Everything is straightforward and intuitive. For further information, follow the link below and connect our support:

Get Improved Import & Export Magento 2 Extension