UNIX Systems Administrator (IL)
Next Step Systems
US-IL-Chicago

Justtechjobs.com Post A Job | Post A Resume

How to Import Excel, Access or XML Data into MySQL Using Navicat
by kenlin
This article discusses a solution to import MS Excel, MS Access, XML data to local or Remote MySQL databases by using a famous MySQL Admin Tool called Navicat. You can find the information of Navicat at http://www.mysqlstudio.com/detail.php3.
Overview
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 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 to get started. It is more than just a simple MySQL admin client, other useful major features include schedule backup, data transfer, import/export wizard, support 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 5.3 version, you can save the process as a profile. With the profile, you can create a schedule for it.
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 it. This is quite easy as you only have to give your server details and you will be connected.
After connecting, you should see your databases in left side of Navicat main window.
If you click on database icon, Navicat shows you a list of all of the tables. Select the table that you want to perform Import action.
Please click on Import Wizard button. Import Wizard will be started. Follow the steps of Import Wizard, you should be able to import your MS Excel/Access/XML file into MySQL database.
Tutorial on Import/Export Wizard of Navicat
Import Data to Table Using 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
  5. Depending on the import data format, you should follow several steps to complete the import process. Here are the steps for importing from text file:
Import Wizard Steps:
  1. State which import data format you would like.
  2. Locate your file for import data.
  3. Select the appropriate delimiter. (For text file 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 format 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 Execute button to start importing data.
Export data by using Export Wizard
  1. Click the Tables tab.
  2. Select the table which you want to export data. 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. Please select which data format you want and click OK button to proceed.
Other Features of Navicat
Some of the Navicat key features are listed below:
  1. Support Multiple Connections for Local or Remote MySQL Servers.
  2. Browse databases, Modify your data, Create and delete databases, tables, indexes and users accounts.
  3. Create or Run SQL queries with visual query builder.
  4. Create reports with visual report builder, Reports can be saved as an archive file (.raf) for backup and increased portability.
  5. Manage user privileges [security configuration].
  6. Backup/Restore your database, The new Schedule Backup feature is used to create a new schedule that will backup your data.
  7. Data Transfer - Support transfer databases from one MySQL server to another MySQL server (1. Local to Remote , 2. Remote to Remote, 3. Remote to Local).
  8. Import/Export data - The Import and Export Wizard would help you to import/export data. It supports up to 18 most popular formats including MS Access, MS Excel, XML, PDF and TXT.
Tips on Setup Connection Between Navicat and Your MySQL Server
Localhost Connection Setting
If you installed MySQL database on your local PC (download here), the default username is "root" and password is empty.
Localhost connection setting is similiar as following :
Host : localhost
Username : root
Password : (empty)
Port : 3306 (default)
Before you established local connection between Navicat and your local MySQL server, please also use our Launcher program to start your local MySQL server. You could also use another program to start your local MySQL server (Path : C:\mysql\bin\winmysqladmin.exe).
Remote Connection Setting
After MySQL database was installed, it would only allow "localhost" connection by default. Therefore, most server-side scripts program (e.g. phpMyAdmin) can easily connect with the local database in the same server. You could use Telnet or SSH program to manage your remote server because it's localhost connection.
Any client PCs will be blocked by the remote MySQL server until the user privileges has been configured. Remote connection setting is similiar as following :
Host : IP address of remote MySQL server
Username : Your_username (username is 'root' if you just installed the MySQL server)
Password : Your_password (Emtpy value if you just installed the MySQL server)
Port : 3306 (default)
NOTE: Navicat would be able to access your remote MySQL server no matter it is running on Linux, Unix, Mac, Windows platform. If you couldn't connect your remote MySQL server via Internet, please visit the Navicat web site for detailed explanation on MySQL Privileges System.