Introduction

MySQL-to-Excel is a program to publish MySQL database as MS Excel spreadsheet. Each MySQL table becomes a worksheet, each record becomes a row. With this program you will be able to generate various statistics on MySQL databases using the whole power of MS Excel engine.

Features

  • All MySQL data types are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Fast conversion engine (10MB MySQL database - in less than 1 minute on an average P-IV system)
  • Converts queries (see Using Queries article for related information)
  • Command line support (see Command Line article for related information)
  • Converts individual tables (see Select Tables article for related information)
  • Converts tables containing more than 65535 records
  • Output document is compatible with MS Excel 5.0 and higher
  • Stores configuration into profile (see Profile article for related information)
  • Easy-to-use wizard style interface
  • Full install/uninstall support

Requirements

  • Necessary privileges to read the target database on MySQL server


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be converted into MS Excel spreadsheet as if it would be a regular table. Select name of table where you wish to store query results in the desintation MS Excel spreadsheet. Enter query in the corresponding field. Also, you can test query on source database by pushing "Test Query" button. If query is not correct please check that it is a correct SQL SELECT-statement and all table and field names are spelled properly. You can also test query by running it from MySQL client.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call the conversion procedure from an automation script you can use console version of MySQL-to-Excel S2XAGENT.EXE. Find this file in MySQL-to-Excel 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:

--blob2text | convert BLOB to text
--date2str | convert dates into strings
--dest=... | MS Excel spreadsheet name
--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
--port=... | MySQL port
--pswd=... | MySQL user password
--silent | use this option to disable program output
--split | split data into multiple spreadsheets if necessary
--src=... | MySQL database name
--tab_file=... | file containing the list of tables to convert (one table name per line)
--user=... | MySQL user name
--wrap | wrap text into multiple lines in a cell
--xls_ver=... | format version for the destination MS Excel spreadsheet (0 - MS Excel 5.0, 1 - MS Excel 2000)

In the following example the program converts MySQL database "db1" on the remote MySQL server "mysqlhost" to MS Excel 2000 spreadsheet "from_mysql.xls" using table names file "c:\tabfile1.txt":

S2XAGENT.EXE --src=db1 --dest="c:\from mysql.xls" --host=mysqlhost --user=administrator
--pswd=the_passsword --tab_file=c:\tabfile1.txt --xls_ver=1

Table names file should be formatted as follows:

Table_1
Table_2
...
Table_N

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 database tables will be converted
  4. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="c:\my database.xls")


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. MySQL-to-Excel is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MySQL-to-Excel 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.

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


Format Settings

On the third wizard screen you can specify how the resulting spreadsheet should be formatted. First, you should select format version. If you choose MS Excel 5.0 format, the resulting spreadsheet will be compatible with MS Excel 5.0/95 and later. All strings will be cut to 255 symbols (it is a limitation of MS Excel 5.0 format). If you choose MS Excel 2000 format, the resulting spreadsheet will be compatible with MS Excel 2000 and XP only.

If you want to format all date fields as strings in the destination spreadsheet, select "Convert dates into strings" checkbox. This option was designed to resolve possible conflicts caused by the fact that MySQL and MS Excel have different ranges of acceptable date values.

Sometimes MySQL database authors define text columns as BLOB (for example, in order to perform case-sensitive comparison). In this case you should select "Convert BLOB to Text" checkbox to obtain better conversion results.

Select "Wrap text into multiple lines in a cell" checkbox at the bottom of the wizard screen to wrap text into multiple lines in each cell. The number of wrapped lines depends on the width of the column and the length of the cell contents.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL connection information (host, user name, etc) and all other necessary conversion settings. 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.


Select Databases

On the third wizard screen you should enter the source MySQL database name and path to the destination MS Excel spreadsheet. If you specify the name of existing MS Excel spreadsheet it will be overwritten.

Also you can specify how to process tables with records number exceeds maximum number of rows for MS Excel (it is 16383 - for MS Excel 5.0 and 65535 - for MS Excel 97/2000). By default the program truncates the data to the maximum number of rows in MS Excel. Select 'Split data into multiple files if necessary' checkbox to allow splitting database contents into multiple spreadsheets. In this approach MySQL-to-Excel generates a number of *.xls files (your_name.xls, your_name(1).xls, your_name(2).xls, etc.) to store the entire data into several parts so that size of each part does not exceed the maximum number of rows in MS Excel.


Select Tables

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

Push "Add Query" button to add SELECT-query filtering data that will be converted into MS Excel spreadsheet. See Using Queries article for the further information.


Troubleshooting

While working with MySQL-to-Excel 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 MySQL-to-Excel. You can use OLD_PASSWORD function to make the certain account compatible with MySQL-to-Excel. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
        -> 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 MySQL-to-Excel.

P: Unknown MySQL Server Host <IP-address or network name> (0)
R: Program failed to find MySQL server on specified host. Verify IP-address or network name entered.

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: There are no databases in 'MySQL database name' listbox.
R: The user has not enough privileges to extract databases list. Ask your MySQL server administrator give you 'SHOW DATABASES' privilege.

P: Program freezes on the second dialog while connecting to remote MySQL server.
R: MySQL server cannot resolve network names. Close the program, restart MySQL daemon with "--skip-name-resolve" option and try again.

P: I cannot uninstall MySQL-to-Excel.
R: If you need to remove MySQL-to-Excel 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 sql2xls.log

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you 'SELECT' privilege on the source MySQL database.

P: In the resulting MS Excel spreadsheet some cells have incorrect values: "##...##".
R: Such values appear when the cell contains a number, date, or time that is wider than the cell. You can increase the width of the column by dragging the boundary between the column headings.

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


Using Queries

Queries give you the ability to extract partial data for converting into MS Excel spreadsheet. The following examples are provided to illustrate how to use queries for particular purposes. Assume that we have table "Table1" as below:

   Table1(
    ID INT NOT NULL AUTO_INCREMENT,
    FName VARCHAR(50),
    LName VARCHAR(50),
    Birthday DATE,
    Notes TEXT
   );

Example 1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000
Example 2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

Example 3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE Notes IS NOT NULL

You can add queries on "Select Tables" wizard screen. See Select Tables article for related information.


Contents

Add Query

Command Line

Connect to MySQL Server

Format Settings

Introduction

Profile

Select Databases

Select Tables

Troubleshooting

Using Queries