How to import data from external MySQL database to Magento 2

Magento 2 mysql data import

Often it is necessary to import products, customers, orders, or other data types to Magento 2 from a third-party MySQL database. However, Magento 2 doesn’t let you do that. Although the default tools don’t support such file sources as external databases, there are several ways to synchronize Magento 2 with external databases

In the following post, we shed light on how to achieve this goal in two different ways. Firstly, we describe a common approach that involves the default Magento 2 import solution. It requires tons of manual work and cannot be automated. You need to run a CSV export from your MySQL database, match third-party attributes to ones used in Magento 2, and import your MySQL table into your e-commerce website.

On the contrary, you will find out how to avoid numerous unnecessary chores while importing data to Magento 2 from a MySQL database. This approach involves the Improved Import & Export extension. The module combines MySQL database connection and other data import activities within a single interface right in your Magento 2 admin.

After that, we shed light on several MySQL facts as well as describe the core features of the Improved Import & Export extension. To make the long story short, let’s see how to import data from an external MySQL database to Magento 2.

The Common Way of Moving MySQL Bases to Magento 2

Let’s imagine a situation when you have a remote MySQL database with a product table in it. The most common (and at the same time hardest) way is based on CSV files. It requires lots of manual work to get data from a specific table of the external MySQL database. You have to pass three big stages:

  • Export a CSV table from the MySQL database;
  • Map third-party attributes of the table to Magento 2 attributes;
  • Import the table into Magento 2.

All three stages are disconnected and act like separate procedures that require different tools to be utilized. Let’s describe each one briefly.

CSV Export from MySQL Database

Below, we describe core steps necessary to export a CSV file from MySQL using phpMyAdmin.

  1. First of all, you need to open the database.
  2. Next, select a specific table that includes the data you need.
  3. Now, you should find the “Export” tab.
  4. After that, you need to configure the parameters of the export process: select “Custom” in “Export Methods”.
  5. In “Format”, select “CSV”.
  6. Next, check the “Put columns in the first row” option.
  7. At last, you can hit the “Go” button.

Watch the video below for further information:

[embedded content]

Map Third-Party Attributes to Magento 2 Attributes

Now, you have a standard CSV file with the desired data. Unfortunately, you cannot upload it right into Magento unless it uses the default attributes of the platform. Now, it is necessary to spend some time matching the attributes of the exported CSV file to the native Magento attributes. Depending on the size of your table, this procedure may consume lots of time, since you need to edit the name of every column.

Import MySQL Table into Magento 2

When the data file is modified, you can import it to Magento 2. It is possible to use the default Magento 2 import interface to achieve this goal. If attributes are mapped correctly, you will easily transfer data from an external MySQL database to Magento 2. In case of errors, try to find the issue in the CSV file. Fix it and relaunch the import process.

It was the common way to import data from an external MySQL database to Magento 2. It is proven by the community but takes much time to complete the procedure especially if you have to deal with multiple big tables. Alternatively, you can implement the following approach which is a way more efficient.

The Easy Way of Moving MySQL Bases to Magento 2

Magento 2 mysql data import

To reduce the number of steps and as a result time necessary to import data from an external MySQL database to Magento 2, you can use the Improved Import & Export Magento 2 extension. How does the module simplify the procedure?

MySQL Database Connection

First of all, it reduces the three stages above to two leading to the 30% reduction of work. You don’t need to use phpMyAdmin to export a CSV file. Furthermore, you don’t need any files to be exported at all. The seven steps of the “CSV Export from MySQL Database” stage are wholly reduced since the Improved Import & Export Magento 2 extension connects to an external MySQL database directly to import all the necessary data.

Magento 2 Data Import from MySQL

Instead of the common way, you just need to open the interface of the module, create a new import job, specify the required MySQL database, select all the necessary tables, apply mapping (match the external attributes to Magento 2 attributes), and update your Magento 2 database with the desired information. Furthermore, you can create a schedule of updates to automate data import from the external MySQL database to Magento 2.

The mapping procedure is dramatically simplified in comparison to the aforementioned one since you don’t need to edit data files directly. The Improved Import & Export Magento 2 extension provides an intuitive interface where you match Magento 2 attributes to external requirements and vice versa. Next, the created scheme is saved and applied to further updates. In case of the common data import from an external MySQL database to Magento 2, you should edit new CSV files manually before importing them into the system. In combination with scheduling, this feature saves tons of time and effort. That’s why we think that the Improved Import & Export extension provides the easiest and the fastest MySQL data import into Magento 2. Now, let’s say a few words about MySQL and then return to core features of the module.

Get Improved Import & Export Magento 2 Extension

MySQL Facts

Magento 2 MySQL data Import

MySQL is the most popular database system used with PHP. It is usually utilized on the web, runs on a server, and suits best for both big and small apps. The database is fast, reliable, and easy to use. The information in a MySQL database is stored in tables. Being an open-source relational database management system, MySQL is widely used as a central component of the LAMP stack as well as other “AMP” stacks.

As for other facts, the name of the system stands for the abbreviation for Structured Query Language combined with the name of co-founder Michael Widenius’s daughter. Initially, MySQL was owned and sponsored by the Swedish company MySQL AB. Now, it is owned by Oracle Corporation.

The project is available in several free and paid editions that offer additional functionality. Many popular apps rely on the MySQL database. Drupal, WordPress, Joomla, TYPO3, and many others are among its prominent users.  Furthermore, several Internet giants also incorporate the database. Facebook, Twitter, YouTube, and Flickr streamline it to provide a better experience.

As we’ve just mentioned, there is a paid edition of MySQL. It is the proprietary MySQL Enterprise Server. At the same time, you can always leverage the open source MySQL Community Server. A series of proprietary extensions differentiate the Enterprise edition. You can install them as server plugins to get additional features. At the same time, both versions share the same numbering system and are built from the common code base.

Below, you can see a list of features available in MySQL 5.6:

  • ANSI SQL 99 subset;
  • Multiple extensions;
  • Cross-platforming capabilities;
  • Triggers and Cursors;
  • Updatable views;
  • Online DDL for the InnoDB Storage Engine;
  • Information and Performance Schemas;
  • SQL Mode options to control runtime behavior;
  • X/Open XA DTP support;
  • SSL support;
  • Transactions with savepoints;
  • Query caching;
  • ACID compliance;
  • Sub-SELECTs
  • Replication support;
  • Full-text indexing and searching;
  • Unicode support;
  • Embedded database library;
  • Multiple storage engines.

For the full list of features, follow this link: MySQL Features. Note that minor updates of the MySQL Server are released approximately once per two months. You can obtain the sources from MySQL’s website or the corresponding GitHub repository. Note that the GPL license is applied in both cases.

MySQL and Magento 2

As for the Magento 2 MySQL tandem, it is strongly recommended to observe the following standard:

  • Since Magento improves database access during reindexing using MySQL database triggers, you should keep in mind that it does not support any custom triggers in the Magento database. The reason is issues caused by custom triggers regarding future Magento versions.
  • It is also recommended to explore these potential MySQL trigger limitations before going any further with the development.
  • In case of MySQL database replication, you should remember that Magento does not support MySQL statement-based replication. To avoid further mistakes and corresponding issues, it is necessary to row-based replication only.

You can find more information here: Magento 2 MySQL. Now, let’s take a look at our extension.

Improved Import & Export Magento 2 Extension

The Improved Import & Export Magento 2 extension is developed to connect Magento 2 to any third-party systems and sources. You can use our module to synchronize your e-commerce store with multiple external platforms, marketplaces, cloud services, etc. To achieve the broad connectivity options, the plugin supports numerous different approaches to data import/export.

Supported File Formats

First of all, you can leverage the default file transfer (which is eliminated in case of data import from an external MySQL database to Magento 2) to connect your e-commerce store to an external system. This approach is often used to provide price comparison engines and marketplaces with product data. At the same time, you can easily import order data back to your website. Besides, data file transfers help to migrate from external e-commerce platforms to Magento 2. You can even leverage this approach to migrate from your old Magento 1 store.

The Improved Import & Export extension supports multiple file formats. While Magento works with CSV files and most third-party solutions add only XML support, our plugin extends this connectivity options significantly. In addition to these two formats, it efficiently works with JSON as well as uses XLS, and XLSX formats to transform XML files. You can not only import files of supported formats to Magento 2 but also create the corresponding output. As a result, there is no need to convert data files before every update.

Supported File Sources

Besides, the Improved Import & Export Magento 2 extension supports multiple file sources. It means that you can import/export data files from/to various places. First of all, it is a local or remote server – the extension fully supports FTP/SFTP so that you have the first basic option. Next, it is possible to utilize Dropbox as a source. Use your account in the cloud to transfer information between two systems. When it comes to importing procedures, our extension goes even further. It allows importing files right via a direct URL. Also, note that all compressed data is unzipped/untarred automatically.

Alternative Ways of Import/Export

Another way to move data to/from Magento 2 incorporates REST API. This approach is applied to complex synchronizations, such as the Magento 2 ERP integration. It helps to eliminate data files from the transfer. At the same time, you can still automate this process by creating different schedules and applying mapping (both features are described below in more details).

If the information is provided from Google Sheets, it is not a problem for the Improved Import & Export Magento 2 extension. With the help of our module, you can easily import data from the popular service within a few clicks. Just take a look at the following illustration:

Magento 2 Google Sheet import

Flexible Cron Schedules

Now, let’s focus on schedules and mapping. As mentioned above, you can fully automate every import/export process by applying mapping and creating a scheme of updates. And it is true to data import from an external MySQL database to Magento 2.

The Improved & Export Magento 2 extension supports cron so that you can create any custom schedule of updates. If you don’t want to deal with the standard cron syntax, it is possible to select one of the predefined intervals. If necessary, you can customize it as well. At the same time, the ability to launch every import/export procedure manually is preserved. Below, you can see how to configure a schedule:

Advanced Mapping Functionality

As for mapping, you can apply it within a convenient interface of the Improved Import & Export Magento 2 extension. No data files should be edited manually. You create a mapping scheme, and it is used for all further updates. The process looks as follows:

Moreover, the Improved Import & Export Magento 2 extension offers mapping presets – pre-made mapping schemes that simplify data migration even more. You don’t even need to match attributes manually. Instead, our module allows selecting the corresponding preset that can be applied within a few clicks creating the required mapping scheme automatically. You can see how it work below:

To discover more features of the Improved Import & Export Magento 2 Extension, watch this video:

[embedded content]

If you want to import data from an external MySQL database to Magento 2 in the most efficient manner without spending hours of time on CSV import/export, get the Improved Import & Export Magento 2 extension. You can purchase the module here:

Get Improved Import & Export Magento 2 Extension

Magento 2 MySQL Integration FAQ

Let’s look at several other facts about the Magento 2 MySQL integration.

How to import data to Magento 2 from MySQL automatically?

Since the Improved Import & Export extension leverages cron scheduler, it is possible to specify an interval to periodically run data transfers in a fully automated manner between a MySQL table and your Magento 2 website. Just choose a predefined time frame or create a custom one.

How to import third-party data to Magento 2 from MySQL?

If you need to transfer data that contains third-party attributes from a MySQL table, the Improved Import & Export lets you match them to the ones used in Magento 2 right in the import profile. The corresponding section provides the ability to choose unsupported attributes and define a replacement for them.

How to import customers to Magento 2 from MySQL?

You can use the Improved Import & Export extension to transfer customers to Magento 2 from a remote MySQL table. The module lets you create an API integration between your e-commerce store and the database, importing customer data.

How to import orders to Magento 2 from MySQL?

You can use the Improved Import & Export extension to transfer orders to Magento 2 from a remote MySQL table. The module lets you create an API integration between your e-commerce store and the database, importing order data.

How to import products to Magento 2 from MySQL?

You can use the Improved Import & Export extension to transfer products to Magento 2 from a remote MySQL table. The module lets you create an API integration between your e-commerce store and the database, importing product data.

How to import categories to Magento 2 from MySQL?

You can use the Improved Import & Export extension to transfer categories to Magento 2 from a remote MySQL table. The module lets you create an API integration between your e-commerce store and the database, importing category tree data.

How to import newsletter subscribers to Magento 2 from MySQL?

You can use the Improved Import & Export extension to transfer newsletter subscribers to Magento 2 from a remote MySQL table. The module lets you create an API integration between your e-commerce store and the database, importing newsletter subscriber data.

How to import B2B entities to Magento 2 from MySQL?

You can use the Improved Import & Export extension and its B2B add-on to transfer B2B entities to Magento 2 from a remote MySQL table. The module lets you create an API integration between your e-commerce store and the database, importing B2B data.

How to import attribute values into Magento 2 from MySQL?

The Improved Import & Export extension lets you import both attributes and their values from MySQL to Magento 2. You can match any external records to the data your Magento 2 store uses.

How to remove attribute values during Magento 2 import?

With the Improved Import & Export module, you can delete attribute values during the Magento 2 import. It is only necessary to add the option:delete_values column to your MySQL table. Use it to specify the attribute values that should be removed. As for the attribute itself, it stays unchanged. You provide values that have to be removed (e.g., green, red, and yellow for the attribute ‘color’) and the module gets rid of them.

How to export product data from Magento 2 to MySQL?

Since the Improved Import & Export extension creates an API connection to transfer data from MySQL to Magento 2, you can also leverage the module to maintain the data flow in the opposite direction. Create a new export job, choose the corresponding export entity, provide connection parameters for your Magento 2 MySQL integration, and apply other configurations, such as mapping, filters, and schedules.

Discover more from WHO WILL CARE eCommerce

Subscribe now to keep reading and get access to the full archive.

Continue reading