There are some challenges in migrating data between heterogeneous repositories, where the source and target databases come from different database management systems from different vendors. In some cases, it is possible to connect two databases simultaneously. But sometimes it simply cannot be achieved. Faced with such a dilemma, database practitioners have no choice but to fill tables from dump files. During this process, Navicat can provide great assistance. The import wizard allows you to import data from various sources into tables/collections, including CSV, TXT, XML, DBF, and more. In addition, you can save the settings as a configuration file for future use or to set up automated tasks. In today's blog, we will use the free Navicat Premium Lite 17 to migrate data from a PostgreSQL "DVD rental" database to a MySQL 8 instance using the Navicat import wizard.

In this tutorial, we will use PostgreSQL DAT files to fill the film table in MySQL 8. Here are the table definitions in Table Designer:

To start the import wizard, right-click on the target table in the Navicat navigation pane (or hold down Ctrl Click in macOS), and then select "Import Wizard..." from the pop-up menu:

The first screen of the wizard is where we select the source files. Please note that the simplified version only supports text-based files such as TXT, CSV, XML, and JSON. Although we have a. dat file, we can choose text file options, including. txt CSV and. dat formats:

In the next screen, we will select the DAT file. Each table has a file. The file name of the movie table is "3061. dat":

The next step is to set the delimiter. Records are separated by line breaks (LF) characters, while columns are separated by tabs (TAB). There are no quotation marks around the text value, so be sure to remove the double quotation mark (") character from the" text identifier "text box:

On the next screen, you will see some additional options. Here, we must uncheck the 'Field Name Row' box because DAT files do not contain field names. We also need to change the 'date order' to 'year/month/day' (YMD) and replace the forward slash (/) separator with a dash (-), as the date we are importing is YYYY-MM-DD hh: mm:ss.ms， The format is as follows: May 26, 2013 14:50:58.951

We can choose existing tables or create new tables. Since we selected the target table when starting the import wizard, it should be displayed here:

The next step is to map the source fields to the fields in the target table. Here, we cannot assume that they will be consistent. A quick look at an entry in the DAT file will reveal that the last_uUpdate and special_features columns are opposite:

We can right-click (or hold down Ctrl Click in macOS) anywhere in the dialog box and select "Match All Directly" from the context menu to quickly map fields to fields in the target table. However, once this is done, we will have to manually select the last update and special_features columns from the target field drop-down menu to change their order:

Please note that field 13 (f13) can be safely ignored.

For import mode, we can 'append' or 'copy' records because the table should be empty:

When migrating from one database type to another, it is highly likely to encounter data conversion errors. Therefore, it is a good practice to uncheck the 'Use extension to insert statements' checkbox in' Advanced '. Doing so will cause Navicat to issue separate INSERT statements for each record, instead of using the following syntax to merge multiple records:

Now, click the 'Start' button to start the import program.

As expected, although there were several errors (specifically 3), 1000 out of 1003 rows have already been added to the target table!