How to Import Database in Magento 2

Recently, we’ve spotted a question on StackExchange regarding the Magento 2 database import. David Duong faced problems while transferring a database on Magento 2.3.3. Below, we shed light on the issue and the provided solution for Magento 2 to import a database via the command line. However, it is quite a complex process since the system doesn’t let you transfer a database to Magento 2 directly. You always need to create an output file that contains a particular entity. If your file incorporates several types of data (e.g., products and orders), it is necessary to split it into two separate tables.

Furthermore, you always have to edit attributes in the imported file to make them suitable for the Magento 2 requirements. Otherwise, the Magento import of a database becomes questionable. It is impossible to automate regular data transfers from a remote database to your e-commerce website. However, there is a tool that solves all these issues. After we describe a method provided in the StackExchange discussion, you will find out how to achieve the missing functionality with the help of the Improved Import & Export Magento 2 extension. For more tips and advice, read our Cookbook.

The Issue with the Magento 2 Database Import

David Duong created a backup database via CLI. He used the following command:

Although the process was successful, problems started to occur when he tried to import it into his new PHPMyAdmin. The author of the discussion faced the following four errors:

1. Unexpected character. (near “\” at position 0)
2. Unexpected beginning of statement. (near “\” at position 0)
3. Unexpected beginning of statement. (near “nCREATE” at position 1)
4. Unrecognized statement type. (near “ALGORITHM” at position 9) 

What do StackExchange users recommend?

The Solution: How to Import Database in Magento 2 via Command Line

Ashok Dubariya provides the following steps to import the database in the PHPMyAdmin

Use the following command (with SSH) to export the database:

Next, you have to move the exported file to a server where the import procedure should take place.

Import the database via the following command (with SSH):

For the first attempt, David Duong reports a bug. However, his next try seems successful.

How do we recommend importing and exporting databases in Magento 2? Let’s see!

The Improved Magento 2 Import of Database via Command Line

You can avoid all the headaches mentioned above with the help of Improved Import & Export. Our module offers an excellent opportunity to transfer any data between Magento 2 and any systems. It means that you can freely import a database into Magento 2 or export it to a third-party platform.

Use the following commands to launch a database import to Magento 2:

As for the Magento 2 database export, you can use these CLI commands:

Note that you need to create the corresponding import and export jobs before using these commands to run the Magento 2 import of a database via the command line.

If you run a command without specifying the Job ID – ALL enabled jobs will be executed.

In this article – How to Import Any Table From Remote Database to Magento 2 – we shed light upon the default mechanism of transferring databases and compare it with the one our plugin offers. The latter not only consists of fewer steps but can be entirely automated. You install the module, configure a new import profile, and launch the import process. The same is true for export. Follow the link below for further information. Don’t hesitate to contact our support to discuss the possibilities Improved Import & Export provides.

Get Improved Import & Export Magento 2 Extension

Other Features of Magento 2 Database Import & Export

Now, let’s briefly describe the core features of the module. First of all, it provides advanced options when it comes to the automation of data transfers. You can either create cron-based schedules or use triggers that launch import/export processes right after a specific event takes place. Thus, you can make database import to Magento 2 flawless: Improved Import & Export will do all the work for you. 

Another vital aspect of the extension is mapping. The module lets you apply mapping presets. It means that the plugin replaces all third-party attributes with ones used internally in a fully automated mode. 

If you cannot find a preset you need, there is a particular interface where you can do everything manually. Choose a third-party value and specify a replacement in front of it. Thus, you can import any external database to Magento 2, avoiding errors caused by unsupported attributes. 

Other mapping features let you match and edit attribute values. You can also map third-party categories while importing products. Besides, the Improved Import & Export Magento 2 extension enables you to create attributes on the fly. You need to use a general form. It means that even a database that lacks some attributes can be transferred to your Magento 2 website with the missing data.

And, of course, our plugin dramatically extends the number of supported file formats (CSV, XML, JSON, ODS, and Excel) and sources (FTP/SFTP, numerous cloud platforms, and URL). You can freely leverage API connections, like in the case of database import to Magento 2, and intermediary services, such as Google Sheets, Office 365 Excel, and Zoho Sheet.

For further information, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension

Magento 2 Database Import FAQ

Below, we provide answers to questions that often occur when it comes to the Magento 2 database import.

Final Words

You can freely run the Magento 2 import of a database with the help of the command line. At the same time, it is possible to automate all the corresponding transfers with the help of the Improved Import & Export extension. In addition to the Magento 2 database import, it can help you transfer all website data, including Magento 2 B2B entities. Follow this link for more information:

Get Improved Import & Export Magento 2 Extension