Using Navicat to Import Data into MySQL

In today’s article Michael takes a look at using Navicat (a MySQL management tool) to quickly and easily import/export data to and from MySQL.

This article discusses a solution to import MS Excel, MS Access and XML data into local or remote MySQL databases using Navicat – a MySQL management tool. You can find out more about Navicat at http://www.mysqlstudio.com/detail.php3.

We very often need to import various data into MySQL databases. This becomes a tough job if we don’t have a reliable & easy-to-use MySQL GUI; At times, when I am working on deadlines, this job becomes frustrating and I find myself wishing I had a tool to do this job for me.

Navicat can convert Excel spreadsheets/MS Access data to MySQL databases, eliminating time-consuming data entry and the errors that accompany it. It uses a Microsoft Access-like interface and comes with a MySQL server launcher and comprehensive manual that will help you get started. Navicat is more than just a simple MySQL admin client – other useful features include scheduled backup, data transfer, import/export wizard, support for Foreign Keys, visual query builder, and visual report builder.

Navicat’s Database Transfer Tool is a visual data transfer tool designed for developers who work between different MySQL servers or need to move databases between two MySQL servers.

In Depth

PremiumSoft Navicat enables you to convert your documents (different file types ) into your MySQL databases. Its easy-to-use interface makes it easy to perform the Import/Export action. In Navicat version 5.3, you can save the process as a profile. You can then schedule the execution of this profile.

Getting Started

First of all, you need to install PremiumSoft Navicat. You can download it from http://www.mysqlstudio.com/shareware.php3 . You can connect to the MySQL host where you want to import your data into. This is quite easy as you only have to give your server details and you will be connected.

After connecting, you should see your database in left side of the Navicat main window:

If you click on the database icon, Navicat shows you a list of all tables in that database. Select the table that you want to perform Import action on.

Click on Import Wizard button. The Import Wizard will start. Follow the steps of the Import Wizard and you will be able to import your MS Excel/Access/XML file into your MySQL database.

{mospagebreak title=Using Navicat’s Import/Export Wizard&toc=1}

To import data to a table using the Import Wizard:

  1. Click the tables tab
  2. If you have an existing table to which you want to import data, select the table. Otherwise, do not select any table.
  3. Click Import Wizard at the Database Window, or, right-click anywhere in the Database Window and select Import Wizard.
  4. An Import Wizard dialog box will be shown. Depending on the import data format, you should follow several steps to complete the import process.

Here are the steps for importing from a text file:

  1. Choose which import data format you would like.
  2. Locate a file containing data to import.
  3. Select the appropriate delimiter (for text files only).
  4. You can define some additional options for source
  5. The wizard has made some guesses about where your field breaks occur. If they are incorrect, you can make adjustments in this step.

    NOTE: If the import data format is text and you have selected “fixed width” in step 3, you can set field breaks in this step.
  6. The wizard will make some guesses on your table structure and you can make adjustments.
  7. You can define the field mappings. Set mappings to specify the correspondence between fields in the source and fields in the destination.
  8. Check your data before importing.
  9. Select a desired import mode.
  10. Click the execute button to start importing data.

To export data using the Export Wizard:

  1. Click the tables tab.
  2. Select the table that you want to export data from. Click Export Wizard at the database window, or, right-click anywhere in the database window and select Export Wizard.
  3. An Export Wizard dialog box will be shown.
  4. Select which data format you want and click OK button to proceed.
{mospagebreak title=Other Features of Navicat&toc=1}

Some of Navicats key features are listed below:

  • Supports multiple connections for local or remote MySQL servers.
  • Browse databases, modify your data, create or delete databases, tables, indexes and users accounts.
  • Create or run SQL queries with the visual query builder.
  • Create reports with the visual report builder. Reports can be saved as an archive file (.raf) for backup and increased portability.
  • Manage user privileges [security configuration].
  • Backup/restore your database. The new Schedule Backup feature is used to create a new schedule that will backup your data.
  • Data Transfer. Navicat supports transfering databases from one MySQL server to another MySQL server (1. Local to Remote , 2. Remote to Remote, 3. Remote to Local).
  • Import/export data. The import and export wizard supports 18 formats including MS Access, MS Excel, XML, PDF and TXT.

Navicat Tips

If you installed MySQL on your local PC (Download URL: http://www.mysql.com/downloads/mysql-3.23.html ), the default username is “root” and password is empty.

The localhost connection setting would look something like this:

Host : localhost
Username : root
Password : (empty)
Port : 3306 (default)

Before you establish a local connection between Navicat and your local MySQL server, use the launcher program to start your local MySQL server. You can also use WinMySQLAdmin to start your MySQL server (c:mysql.inwinmysqladmin.exe).

After MySQL is installed, by default, it only allows “localhost” connections. Therefore, most server-side scripts (e.g. phpMyAdmin) can easily connect with the local database on the same server. You can use Telnet or an SSH client to manage your remote server because it’s also a localhost connection.

Any client PC/s will be blocked by the remote MySQL server until the user privileges have been configured.

A remote connections settings would look like this:

Host : IP address of remote MySQL server
Username : Your_username (username is ‘root’ if you just installed MySQL)
Password : Your_password (Emtpy value if you just installed MySQL)
Port : 3306 (default)

NOTE: Navicat can access your remote MySQL server no matter if it’s running on a Linux, Unix, Mac, Windows platform.

{mospagebreak title=Conclusion&toc=1}

Well, there you have it. My detailed look at how using Navicat can speed up the usually tedious process of importing or exporting your data with MySQL. Remember, you can download Navicat from http://www.mysqlstudio.com/detail.php3.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort