Introduction

Excel-to-MySQL is a program to convert MS Excel spreadsheet into MySQL database. Each worksheet becomes a table, each row except the first one becomes a record and each cell becomes a field. Depending on your privileges on the target MySQL server you can export MS Excel data into new database or overwrite the contents of existing MySQL database.

Features

  • All versions of MS Excel are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Stores configuration into profile (see Profile article for related information)
  • Command line support (see Command Line article for related information)
  • Converts individual worksheets (see Select Worksheets article for related information)
  • Converts Comma Separated Values (CSV) files into MySQL tables (see CSV Files article for related information)
  • Merges MS Excel data into an existing MySQL database
  • Special approach for VServer users (see VServer User Notes article for related information)
  • Exports data source into a dump file (see Customize Conversion article for related information)
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • Microsoft Excel or Office installation (ODBC is not required)
  • Necessary privileges to write into the destination database


Command line

To perform batch conversion or call the conversion procedure from an automation script you can use console version of Excel-to-MySQL X2SAGENT.EXE. Find this file in Excel-to-MySQL installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--charset=... | MySQL character set
--csv_delim=... | select the delimiter that separates CSV field (0 - tab, 1 - semicolon, 2 - comma, 3 - space)
--dest=... | MySQL database name
--dump | convert MS Excel spreadsheet into MySQL dump file
--engine=... | MySQL storage engine (MyISAM, MEMORY, InnoDB, BerkeleyDB, ARCHIVE, FEDERATED, etc). Please refer to MySQL documentation for the further information.
--extr_fn | extract field names from the first row
--from_csv | data source is a CSV file (MS Excel spreadsheet by default)
--help | display help message and exit
--host=... | MySQL server IP address or network name
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge)
--no_refine | disable improving type conversion (see Refine type conversion article for related information)
--port=... | MySQL port
--profile=... | path to a profile to load conversion settings from
--pswd=... | MySQL user password
--silent | use this option to disable program output
--src=... | CSV file or MS Excel spreadsheet name
--tab_file=... | file containing the list of worksheets to convert (one worksheet name per line)
--tab_name=... | MySQL table name (when converting from CSV file)
--user=... | MySQL user name

Notes:

  1. You can omit 'host' parameter to connect local MySQL server
  2. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  3. If you omit 'tab_file' parameter, all worksheets will be converted
  4. If you omit 'mode' parameter, default mode will be used (it is 'overwrite the entire database' when converting xls file or 'overwrite existing tables only' when converting csv file)
  5. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")

Sample 1. The program converts MS Excel spreadsheet "c:\book1.xls" to MySQL database "book1_from_excel" on the remote MySQL server "mysqlhost" using table names file "c:\tabfile1.txt":

X2SAGENT.EXE --src=c:\book1.xls --dest=book1_from_excel --host=mysqlhost
--user=administrator --pswd=the_passsword --tab_file=c:\tabfile1.txt

Worksheet names file should be formatted as follows:

Table_1
Table_2
...
Table_N

Sample 2. The program converts CSV file "c:\sheet1.csv" to MySQL database "Northwind" on the local MySQL server:

X2SAGENT.EXE --src=c:\sheet1.csv --dest=Northwind --tab_name=sheet1
--user=administrator --pswd=the_passsword --from_csv --extr_fn --csv_delim=2

MySQL character sets

This parameter specifies correct encoding of text values in the destination MySQL database. It can have one of the following values:

value | Description | value | Description
big5 | Big5 Traditional Chinese | dec8 | DEC West European
cp850 | DOS West European | hp8 | HP West European
koi8r | KOI8-R Relcom Russian | latin1 | cp1252 West European
latin2 | ISO 8859-2 Central European | swe7 | 7bit Swedish
ascii | US ASCII | ujis | EUC-JP Japanese
sjis | Shift-JIS Japanese | hebrew | ISO 8859-8 Hebrew
tis620 | TIS620 Thai | euckr | EUC-KR Korean
koi8u | KOI8-U Ukrainian | gb2312 | GB2312 Simplified Chinese
greek | ISO 8859-7 Greek | cp1250 | Windows Central European
gbk | GBK Simplified Chinese | latin5 | ISO 8859-9 Turkish
armscii8 | ARMSCII-8 Armenian | utf8 | UTF-8 Unicode
ucs2 | UCS-2 Unicode | cp866 | DOS Russian
keybcs2 | DOS Kamenicky Czech-Slovak | macce | Mac Central European
macroman | Mac West European | cp852 | DOS Central European
latin7 | ISO 8859-13 Baltic | cp1251 | Windows Cyrillic
cp1256 | Windows Arabic | cp1257 | Windows Baltic
binary | Binary pseudo charset | geostd8 | GEOSTD8 Georgian
cp932 | SJIS for Windows Japanese | eucjpms | UJIS for Windows Japanese

Please refer to MySQL documentation to better understand the concept of character sets and collations.


Connect to MySQL server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. Excel-to-MySQL is able to connect to MySQL server using either local or remote mode. Local mode is used to connect MySQL server launched on the same machine where you run Excel-to-MySQL from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import MS Excel database contents into an existing MySQL database. See VServer User Notes article for related information.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by Excel-to-MySQL. See Troubleshooting article to learn how to resolve this problem.


Conversion Settings

On this wizard screen you can customize the conversion process using the following settings:

  • MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
  • MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.

When converting MS Excel spreadsheet, it is also possible to specify the following settings (not available for import CSV file):

  • Convert table definitions only - select this checkbox to convert only structure (header) of MS Excel worksheets. All the data will not be migrated.
  • Extract MySQL field names from the first row - select this checkbox to extract MySQL field names from the first row of each worksheet. Otherwise, Excel-to-MySQL generates field names as F<field number> (e.g. F1, F2, F32, etc).
  • Refine type conversion - select this checkbox to allow the program to improve type mapping during the conversion process. See Refine type conversion article for related information.
  • Skip empty rows - select this checkbox to not convert MS Excel rows containing empty cells only.


CSV files

Besides converting MS Excel spreadsheet into MySQL directly, the program can export Comma Separated Values (CSV) files into MySQL tables. This option was designed to allow users to import MS Excel workbooks that cannot be converted to MySQL databases directly (see Troubleshooting article for related information). In this approach you should export your MS Excel spreadsheet (or its certain worksheets) into CSV format. Then run Excel-to-MySQL and select the appropriate data source type on the first wizard screen. If you specify the name of existing MySQL database, CSV file will be imported as a new table in the database. If MySQL database with the specified name doesn't exist, new database will be created.

CSV file is a sequence of "fields" separated by a certain delimiter symbol (e.g. comma, semicolon, etc). You can export any single worksheet into CSV file via MS Excel using the following steps:

  • open corresponding workbook in MS Excel
  • activate worksheet you want to export into CSV format
  • select "Save As" in menu "File"
  • select destination format "CSV (MS DOS) (*.csv)"

You can customize the way Excel-to-MySQL interprets your CSV files. See Formatting Settings article for related information.


Customize conversion

Excel-to-MySQL supports several types of data source and destination. You can combine them in different ways to use the program for solving specific problems. In "Source" combo box you can select one of the following values:

  • MS Excel spreadsheet - export data from MS Excel spreadsheet (*.xls) file
  • CSV file - export data from Comma Separate Values (CSV) file. See CSV Files article for related information.

In "Destination" combo box you can select on of the following values:

  • MySQL server - move data to MySQL server directly
  • Local dump file - store data into a local dump as MySQL statements to create all tables and to fill them with the data. See Importing dump files article for related information.

You can choose data source and destination types on the first page of Excel-to-MySQL wizard.


Edit Table

On this dialog users can edit table name and customize types mapping.

Excel-to-MySQL converts Microsoft Excel types into the most appropriate MySQL types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes of the original types mapping, user-defined types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.


Formatting settings

On this wizard screen you can customize Comma Separated Values (CSV) formatting settings to ensure the program interprets your CSV file correctly. First, you should specify which delimiter separates CSV fields. Also you can manage the following conversion settings on this page:

  • Extract MySQL field names from the first row - select this checkbox to extract MySQL field names from the first row of each worksheet. Otherwise, Excel-to-MySQL generates field names as F<field number> (e.g. F1, F2, F32, etc).
  • Refine type conversion - select this checkbox to allow the program to improve type mapping during the conversion process. See Refine type conversion article for related information.


Importing dump files

This article explains how to move dump file contents to MySQL server using the most popular tools.

1. MySQL console client (mysql)
Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call MySQL console client as follows:

mysql --host=... --user=... --password=... -vvf < dump_file_name

Otherwise:

mysql --host=... --user=... --password=... -vvf MySQL_database_name < dump_file_name

where 'MySQL_database_name' is a name of an existing database.

2. phpMyAdmin
Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain 'CREATE DATABASE' statement.

Step 1. Click on the appropriate database name in the left frame.

Step 2. Click 'SQL' tab on the top of the screen.

Step 3. Enter full path to the dump file in 'Location of the textfile' field or click 'Browse' button to locate it through the directory tree.

Step 4. Click 'Go' button.


Merging and overwriting

If you try to convert MS Excel database into an existing MySQL database, Excel-to-MySQL allows you to choose one of the following options: overwrite the entire database, overwrite existing tables only, merge into the database or specify another destination name.

  • Select 'Overwrite the entire database' option to replace the entire contents of the destination MySQL database with MS Excel data. Note that all data contained in MySQL database will be lost.
  • Select 'Overwrite existing tables only' option to replace the contents of MySQL tables having the same names as workbooks in source MS Excel spreadsheet.
  • Select 'Skip existing tables only' option to not convert existing tables.
  • Select 'Merge' option to merge MS Excel data into the destination MySQL database. Excel-to-MySQL implements quite simple merging algorithm. It appends data to the corresponding MySQL table. Note that structures of MS Excel workbooks and destination tables must be identical.
  • If you decide to enter another destination name just click 'Cancel' button.

When converting CSV file, only 'Overwrite', 'Merge' or 'Cancel' options are available.


MySQL dump file settings

On this wizard screen you can specify how the resulting dump file should be constructed. There are following settings available:

  • MySQL version - this parameter determines the syntax of MySQL statements being used in output dump file. 'Old' versions of MySQL (before 3.23.6) put more restrictions on database objects names. Note that 'new' syntax is not accepted by 'old' versions of MySQL. Use 'old' syntax whenever you are not sure about the version of your MySQL server.
  • Line breaks - this parameter specifies how to separate text lines in the dump file. If the destination MySQL server is running on MS Windows platform, you should select 'MS Windows style' option. Otherwise select 'Unix style'.
  • Add lock for write around 'INSERT' statements - it makes importing the contents of the dump file into a database much faster. To use this feature in MySQL 4.0 and higher you need global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.
  • Insert 'CREATE DATABASE' statement - select this option to put 'CREATE DATABASE' statement in the resulting dump file. This setting doesn't take effect without the option below activated.
  • Insert 'CREATE TABLE' statements - select this option to put 'CREATE TABLE' statements in the resulting dump file. You should always select it in order to obtain a valid dump file. The only exception is appending the data to existing tables.
  • Insert comments - select this option to add brief comments. It makes the resulting dump file more readable.

See Importing dump files article to learn how to move dump file contents to MySQL server.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL connection characteristics (host, user name, etc) and all of the necessary information for each MS Excel spreadsheet being converted. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.x2s) file with left mouse button. This will cause Excel-to-MySQL wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Refine type conversion

Unlike MySQL, MS Excel has just a few data types. For example, there is no separate INTEGER and DOUBLE types. In view of this fact all MS Excel numbers are converted in MySQL DOUBLE type to avoid data loss. To make the type conversion in more intelligent way Excel-to-MySQL analyzes each value in every column during the migration process. If all values in a column allow more precise type mapping, the program changes the column type at the end of conversion process.

For example, some MS Excel column contains the following values:

1.000000000000000e+000
2.000000000000000e+001
3.000000000000000e+002
...
In the begin of the conversion process Excel-to-MySQL creates the corresponding column in MySQL database with DOUBLE type. While migrating data the program will find out that all values in this column are integers and will change column type to INT at the end of the conversion process.

This algorithm gives the most valuable results on Comma Separated Values (CSV) files. By default all values from CSV files are interpreted as strings. However, after analyzing the data Excel-to-MySQL will be able to extract numbers and dates from CSV fields.


Select source and destination

On this wizard screen you should select the source MS Excel spreadsheet (*.xls) or CSV file and the destination MySQL database or dump file (depending on conversion scenario you chose). If you specify a name of existing MySQL database or dump file it will be overwritten. While converting from CSV file you should also specify MySQL table name. If you specify a name of an existing MySQL table it will be overwritten.


Select worksheets

On this wizard screen you can manage the list of MS Excel worksheets to convert. In the left part of the screen you can see "Available worksheets" list box. It contains all available worksheets of the source file except those that you have selected already. On the right you can see "Selected worksheets" list box. To add new MS Excel worksheet for converting into MySQL table you should select it in "Available worksheets" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MS Excel worksheets. To remove an item from "Selected worksheets" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected worksheets" list box.

To edit selected worksheet double click the corresponding item in "Selected worksheets" list box. Then the dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information.


Troubleshooting

While working with Excel-to-MySQL wizard you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by Excel-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with Excel-to-MySQL. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with Excel-to-MySQL.

P: I cannot uninstall Excel-to-MySQL.
R: If you need to remove Excel-to-MySQL product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE xls2sql.log

P: Error message "Can't connect to MySQL server on <host name or IP>".
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.

P: Some values appear in MySQL database as NULL when the original MS Excel value is not a NULL.
R: This typically occurs when numeric and text data types are intermixed within the same Excel column. This problem is caused by types recognition algorithm implemented in Excel-to-MySQL. The program scans first eight rows and forecast the column type. For example, if first eight rows contains numbers and the rest rows are strings, the program will consider all data in the column to be numbers and string values will be lost. To resolve this problem you should change the value of registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows from 8 to 0. It will force the program to determine column type based on the entire contents of the column.

P: Error message "The structure of data source does not conform to the destination table".
R: The structures of data source and MySQL table should be compatible in one of the following ways:
1. have the same number of columns
2. if data source has more columns than the destination table, each of the destination columns should be contained in data source structure
3. if the destination table has more columns than data source, each of data source columns should be contained in the structure of destination table
See Merging and Overwriting article for related information.

P: In the destination MySQL database some columns are entirely empty.
R: Excel-to-MySQL tries to recognize "table structure" of the source worksheets. However it's not always possible to interpret MS Excel worksheet as a table. You can export it to MySQL server manually via CSV files. See CSV Files article for related information.

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to the target MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you sufficient permissions to create new databases on the target MySQL server. See VServer User Notes article for related information.

P: Some table names and/or field names in the resulting MySQL database are different from the original.
R: MS Excel and MySQL accept different sets of characters for table names and field names. When Excel-to-MySQL converts a name containing prohibited characters it replaces them with '_' in the destination database.

P: Error message Access denied for user: '<user-name>' to database '<database>'
R: If the destination database already exists, you should have necessary privileges to add/remove tables. If the destination database doesn't exist, you should have sufficient privileges to create new databases on the target MySQL server. See VServer User Notes article for related information.

P: After merging MS Excel data into an existing MySQL database a number of records have been lost.
R: It means that Excel-to-MySQL was not able to insert these records into the destination MySQL table. The probable reasons are:
1. record contains empty fields and the corresponding MySQL columns do not allow NULLs
2. record contains duplicate values in the fields that are part of unique index in the destination MySQL table
The only way to resolve this problem is to manually edit the structure of the destination MySQL table (remove NOT NULL attribute or conflicting index).

P: In the destination MySQL database some tables are entirely empty.
R: It means that Excel-to-MySQL failed to recognize "table structure" in the corresponding worksheets. You can export them to MySQL server manually via CSV files. See CSV Files article for related information.

If you came across some other kind of problem while using Excel-to-MySQL, please contact our support department at service@convert-in.com


VServer user notes

If you are using Virtual Server hosting you probably have no sufficient privileges to create new databases on the target MySQL server. For this case we recommend the following approach:

  1. ask your Virtual Server administrator to create a blank database for you
  2. run Excel-to-MySQL and select this database as the conversion target
  3. click "Yes" when you will be asked if you'd like to overwrite existing MySQL database

When you complete these steps, the entire contents of the source MS Excel spreadsheet will be imported into the specified MySQL database.


Contents

Command line

Connect to MySQL server

Conversion Settings

CSV Files

Customize conversion

Formatting settings

Importing dump files

Introduction

Merging and overwriting

MySQL dump file settings

Profile

Refine type coversion

Select source and destination

Select worksheets

Troubleshooting

VServer user notes