Migrating data between heterogeneous repositories - that is to say, where the source and target databases are of different database management systems from different providers - presents several challenges. In some cases, it is possible to connect to both databases simultaneously. However, there are times that it is simply not possible. When presented with such a dilemma, database practitioners have no choice but to populate tables from a dump file. Navicat can be of great help in that process. The Import Wizard allows you to import data to tables/collections from a variety of sources, including CSV, TXT, XML, DBF and more. Moreover, you can save your settings as a profile for future use or setting automation tasks. In today's blog, we'll use the Navicat Import Wizard to migrate data from the PostgreSQL "dvdrental" database to a MySQL 8 instance using the FREE Navicat Premium Lite 17.
For this tutorial, we will populate the film table in MySQL 8 using the PostgreSQL DAT file. Here is the table definition in the Table Designer:
To launch the Import Wizard, right-click the target table in the Navicat Navigation Pane (or Ctrl-Click in macOS) and select "Import Wizard..." from the context menu:
The first screen of the wizard is where we select the source file. Note that Lite edition only supports text-based files, such as TXT, CSV, XML and JSON. Although we have a .dat file, we can select the Text file option, which encompasses .txt, .csv, and .dat formats:
On the next screen we'll choose the DAT file. There is one file for each table. The one for the film table is named "3061.dat":
Next it's time to set the delimiters. Records are delimited using the Line Feed (LF) character, while columns are separated using the TAB character. There are no quotes around text values, so be sure to remove the double quote (") character from the "Text Qualifier" text box:
On the next screen, you'll find a few additional options. Here, we have to uncheck the "Field Name Row" box because the DAT file does not include the field names. We'll also need to change the Date Order to Year/Month/Day ("YMD") and replace the forward slash (/) delimiter with the dash (-) as the dates we will be importing are in a YYYY-MM-DD hh:mm:ss.ms, i.e. 2013-05-26 14:50:58.951, format:
We have the option of choosing an existing table or create a new one. Since we selected the target table when launching the Import Wizard, it should be displayed here:
The next step is to map the source fields to those in the destination table. Here we mustn't just assume that they will line up. A quick look at an entry in the DAT file reveals that the last_update and special_features columns are reversed:
5 African Egg A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico 2006 1 6 2.99 130 22.99 G 2013-05-26 14:50:58.951 {"Deleted Scenes"} 'african':1 'chef':11 'dentist':14 'documentari':7 'egg':2 'fast':5 'fast-pac':4 'forens':19 'gulf':23 'mexico':25 'must':16 'pace':6 'pastri':10 'psychologist':20 'pursu':17
We can right-click (or Ctrl-Click in macOS) anywhere in the dialog and select "Direct Match All" from the context menu to quickly map the field to those of the target table. However, once that is done, we must manually choose the last_update and special_features columns from the Target field drop-downs to change their order:
Note that field 13 (f13) can be safely ignored.
For the Import Mode, we can either Append or Copy the records, since the table should be empty:
When migrating from one database type to another, there is a strong chance of encountering data conversion errors. For that reason, it's a good practice to deselect the Advanced "Use extended insert statements" box. Doing so causes Navicat to issue separate INSERT statements for each record rather than combine multiple rows using syntax such as:
INSERT INTO `film` VALUES (1, 'African Egg', 'A Fast-Paced...'), (2, 'Rumble Royale', 'A historical drama...'), (3, 'Catherine the Great', 'A new take on...'), etc...
Now, it's time to hit the Start button to kick off the import process.
As expected, there were a couple of errors (3 to be exact), but 1000 of 1003 rows were added to the target table!
Conclusion
Navicat's Import Wizard can dramatically cut down the amount of time spent on migrating data between heterogeneous repositories. It supports a wide range of inputs, including CSV, TXT, XML, DBF, ODBC Data Sources and more.
Interested in giving Navicat Premium Lite 17 a try? You can download it for free here. It's available for Windows, macOS, and Linux operating systems.
