Recently, we’ve spotted a question on StackExchange regarding 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 the Magento 2 database import. 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, no database import to Magento 2 is possible. And it is impossible to automate regular data transfers from a 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.
‘
Table of contents
The Issue
David Duong created a backup database via CLI. He used the following command:
|
php bin/magento setup:backup —db |
Although the process was successful, the problems started to occur when he tried to import it to his new PHPMyAdmin. The author of the discussion faced the following four errors:
- Unexpected character. (near “” at position 0)
- Unexpected beginning of statement. (near “” at position 0)
- Unexpected beginning of statement. (near “nCREATE” at position 1)
- Unrecognized statement type. (near “ALGORITHM” at position 9)
What do StackExchange users recommend?
How to Import Database in Magento 2
Ashok Dubariya provides the following steps to import the database in the PHPMyAdmin
Use the following command (with SSH) to export the database:
|
mysqldump –u USERNAME –p DATABASE_NAME > DATABASE_FILENAME.sql |
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):
|
mysql –u USERNAME –p DATABASE_NAME < DATABASE_FILENAME.sql |
For the first attempt, David Duong reports a bug. However, his next try seems successful.
How do we recommend to import and export databases in Magento 2?
How to Import Database in Magento 2 with no Errors
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. 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 less steps but can be entirely automated. You install the module, configure a new import profile, and launch the import process. The same is about 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
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 used 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). And 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.
How to import a database table to Magento 2 automatically?
If you need to transfer any database table to Magento 2 on a regular basis, it is necessary to install a third-party tool as we’ve already mentioned above. The default import/export solutions don’t let you create import jobs that run on schedule unless you use the Commerce edition of Magento 2. But it still doesn’t let you connect to databases directly. Therefore, we recommend you to use the Improved Import & Export extension to import database tables to Magento 2 automatically. The extension leverages cron so that you can create a schedule of updates. Next, Improved Import & Export does everything for you.
How to import database tables to Magento 2 created in external systems?
If you want to import a database that contains attributes that are not used in Magento 2, the Improved Import & Export Magento 2 extension will also help you achieve this goal. While the default Magento 2 import mechanisms block the import of third-party data (and doesn’t let you connect to external databases), you can use the Improved Import & Export extension to enable the missing functionality. Create a mapping scheme to match external data to the default Magento 2 attributes right in your admin. Thus, you teach Magento 2 to replace the unsupported attributes with particular records.
How to import database tables to Magento 2 via API?
You also need to use the Improved Import & Export Magento 2 extension to be able to import database tables to Magento 2 via API. Our extension lets you bridge the two systems transferring tables from a remote database to your ecommerce website. Any import job provides an interface where you can specify the parameters necessary to put the two systems together.
How to import database tables to Magento 2 via ODS files?
If you already have an output from an external database stored in an ODS file, you no longer need to convert it to a CSV file to enable the Magento 2 import. 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.
How to import database tables to Magento 2 via XLSX files?
If you already have an output from an external database stored in an XLSX file, you no longer need to convert it to a CSV file to enable the Magento 2 import. 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.
How to import database tables to Magento 2 via JSON files?
If you already have an output from an external database stored in a JSON file, you no longer need to convert it to a CSV file to enable the Magento 2 import. 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.
How to import database tables to Magento 2 via compressed files?
If your database output is already stored in a file archive, you can also transfer it to Magento 2 with the help of the Improved Import & Export extension. Follow these two requirements. Your file archive should be in a ZIP or TAR format. The compressed database table should be stored in a CSV, XML, ODS, XLSX, or JSON file.
How to import database tables to Magento 2 from Google Sheets?
If the output of your database table is transferred to Google Sheets and you need to move it to Magento 2, it is no longer a problem. With the help of Improved Import & Export, you can import the output from a database stored in a Google Sheets table to your e-commerce website. It is only necessary to specify the address of the corresponding data table.
How to import database tables to Magento 2 from Dropbox?
If the output of your database table is transferred to a file stored on Dropbox, you can also leverage the Improved Import & Export module to import it to Magento 2. Note that your database table 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.
How to import database tables to Magento 2 from Google Drive?
In addition to Google Sheets and Dropbox, the Improved Import & Export module lets you transfer files to Magento 2 from Google Drive. So, if your database table is stored there, it is no longer a problem. Note that the file should be in a CSV, XML, ODS, XLSX, or JSON format. ZIP or TAR archives are supported too.
‘
