MySQL for Excel Guide

Abstract

This document describes MySQL for Excel, which enables you to work with a MySQL database from within Microsoft Excel.

For release notes detailing the changes in each release of MySQL for Excel. see http://dev.mysql.com/doc/relnotes/mysql-for-excel/en/.

Document generated on: 2012-12-22 (revision: 33724)

Preface and Legal Notices

This document describes MySQL for Excel, which enables you to work with a MySQL database from within Microsoft Excel.

Legal Notices

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. MySQL is a trademark of Oracle Corporation and/or its affiliates, and shall not be used without Oracle's express written authorization. Other names may be trademarks of their respective owners.

This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this material is subject to the terms and conditions of your Oracle Software License and Service Agreement, which has been executed and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle or as specifically provided below. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms:

You may create a printed copy of this documentation solely for your own personal use. Conversion to other formats is allowed as long as the actual content is not altered or edited in any way. You shall not publish or distribute this documentation in any form or on any media, except if you distribute the documentation in a manner similar to how Oracle disseminates it (that is, electronically for download on a Web site with the software) or on a CD-ROM or similar medium, provided however that the documentation is disseminated together with the software on the same medium. Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above.

For more information on the terms of this license, or for details on how the MySQL documentation is built and produced, please visit MySQL Contact & Questions.

For additional licensing information, including licenses for third-party libraries used by MySQL products, see Preface and Legal Notices.

For help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists where you can discuss your issues with other MySQL users.

For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.

Chapter 1. MySQL for Excel

MySQL for Excel enables you to work with a MySQL database from within Microsoft Excel. MySQL data can be imported into Excel, Excel data can be exported into MySQL as a new table or appended to a current table, and MySQL for Excel enables you to edit the MySQL data directly from within Excel.

Visit the MySQL for Excel forum for additional MySQL for Excel help and support.

For release notes detailing the changes in each release of MySQL for Excel, see http://dev.mysql.com/doc/relnotes/mysql-for-excel/en/.

Chapter 2. Installing and Configuring

MySQL for Excel is a product for Microsoft Windows, and it is installed with MySQL Installer. And typically you will not be required to install or configure additional tools to use MySQL for Excel.

Note

To install, download and execute the MySQL Installer. Select the MySQL for Excel product and then proceed with the installation. See the MySQL Installer manual for additional details.

MySQL for Excel Requirements

The MySQL Installer installation process will check if these requirements are met, or notify you if further action is required before proceeding with the installation.

.NET Framework 4.0 (Client or Full Profile).

Microsoft Office Excel 2007 or greater, for Microsoft Windows.

Visual Studio Tools for Office 4.0, and MySQL Installer may install this for you.

An available MySQL Server connection.

MySQL for Excel is loaded and executed by selecting the Data menu tab in Excel, and then choosing the "MySQL for Excel" Database icon. This opens a new Excel sidebar with the available MySQL for Excel options. The navigation bar with the MySQL for Excel icon is shown in the following screenshot:

Figure 2.1. The MySQL for Excel navigation bar

Chapter 3. Edit MySQL Data

MySQL for Excel enables you to load and edit MySQL data directly from Microsoft Excel. Changes are immediately committed if the Auto-Commit option is enabled, or done manually by pressing Commit Changes.

Note

The Edit MySQL Data feature exists as of MySQL for Excel 1.1.0.

The example below uses the category table of the example sakila database, but the screen will look the same for any table. Within MySQL for Excel, Open a MySQL Connection, click the sakila schema, Next, select the category table, click Edit MySQL Data, then choose Import to import the data into a new Microsoft Excel worksheet for editing.

Figure 3.1. Editing table data with MySQL for Excel

The background color represents the status of each cell, and there are four distinct colors that are used while editing table data:

Table 3.1. Background cell colors

Color | Description
White | Default color for all cells. This is either the original data, or the data after Refresh from DB is clicked.
Blue | Cells that were committed with success.
Green | Cells that were modified but have not yet been committed.
Red | Cells that generated an error when a commit was attempted. An error dialog is also displayed while the commit is attempted.
Yellow | Cells that accept new data. Data entered here is inserted into the MySQL table.

In our example, the blue "Drama" field was changed and then committed first, then the green "Gaming" field was changed but not committed, and then Auto-Commit was enabled before changing the "9" to a "10" in column 10, which generated an error because this commit would have added a duplicate value as primary key.

Chapter 4. Import MySQL Data

Data can be imported from MySQL into a Microsoft Excel spreadsheet by using the Import MySQL Data option after selecting either a table, view, or procedure to import.

Choosing columns to import

By default, all columns are selected and will be imported. Specific columns may be selected (or unselected) using the standard Microsoft Windows method of either Control + Mouse click to toggle the selection of individual columns, or Shift + Mouse click to select a range of columns.

The background color of a column shows the status of each column. The color white means that the column has been selected, and therefore it will be imported. Conversely, a gray background means that the column will not be imported.

Right-clicking anywhere in the preview grid opens a context-menu with either a Select None or Select All option, depending on the current status.

Importing a table

The dialog while importing a table includes the following options:

Include Column Names as Headers: Enabled by default, this inserts the column names at the top of the Microsoft Excel spreadsheet as a "headers" row.

Limit to ___ Rows and Start with Row ___: Disabled by default, this limits the range of imported data. The Limit to option defaults to 1, and defines the number of rows to import. The Start with Row option defaults to 1 (the first row), and defines where the import begins. Each option has a maximum value of COUNT(rows) in the table.

Importing a table displays a dialog similar to the following:

Figure 4.1. Importing table data with MySQL for Excel

Importing a view or procedure

Importing a view or procedure displays a similar dialogue, but with the following options:

Include Column Names as Headers: Enabled by default, this will insert the column names at the top of the Excel spreadsheet as a "headers" row.

Import: Because a procedure might return multiple result sets, the import options include:

Selected Result Set: Imports the selected tab sheet. This is the default behavior.

All Result Sets - Arranged Horizontally: Imports all result sets into the Excel Worksheet horizontally, and inserts one empty column between each result set.

All Result Sets - Arranged Vertically: Imports all result sets into the Excel Worksheet vertically, and inserts one empty row between each result set.

For example, a dialogue like the following is displayed after importing a procedure and pressing the Call button to invoke the stored procedure:

Figure 4.2. Importing called stored procedure data with MySQL for Excel

Chapter 5. Append MySQL Data

Data from a Microsoft Excel spreadsheet can be appended to a MySQL database table by using the Append Excel MySQL Data to Table option.

Column mappings

Mapping the Excel columns to the MySQL columns can be executed automatically (default), manually, or by using a stored mapping routine. An automatic mapping routine is the default, and can be can be tweaked if every column cannot be matched automatically. The following screenshot shows two columns of Excel data, and the preview dialog after choosing Append Excel Data to Table:

Figure 5.1. Appending Excel data to MySQL (Automatic mapping)

General mapping information

It is common to tweak the column mappings. A few notes about the manual mapping process:

Manual mapping is performed by dragging a column from the upper source grid (Excel spreadsheet) and dropping it into the lower target column MySQL table grid. Click anywhere within the column to initiate this dragging routine.

The color of the header field for each column defines the current mapping status of the column. The colors include:

Green: A source column is mapped to a target column.

Red: A target column is not mapped.

Gray: A source column is not mapped.

A source column may be mapped to multiple target columns, although this action generates a warning dialog.

Right-clicking a target column shows a context menu with options to either Remove Column Mapping for a single column, or to Clear All Mappings for all columns. Dragging a target column outside of the grid removes the mapping.

Mapping methods

The three mapping methods are described below:

Automatic: The automatic mapping method attempts to match the Excel source column names with the MySQL target table column names. It is then possible to manually tweak the mapping afterwards.

If the automatic process finds zero columns to match, then a simple 1 to 1 matching routine is attempted. Meaning, SourceColumn #1 to TargetColumn #1, SourceColumn #2 to TargetColumn #2, and so on.

Manual: The source column names are manually dragged (matched) with the target column names. Manual dragging can also be performed after the Automatic method is selected.

Stored: Manual mapping styles may be saved using the Store Mapping button, which will also prompt for a name and then save it using a "name (dbname.tablename)" naming scheme. The saved mapping style will then be available alongside the Automatic and Manual options.

Stored mappings may be deleted or renamed within the Advanced Options dialog.

Advanced Options

There are several advanced options that are configured and stored between sessions for each Excel user. The dialog looks similar to:

Figure 5.2. Appending Excel data to MySQL (Advanced Options)

The advanced Mapping Options:

Perform an automatic mapping when dialog opens: Automatically attempt to map the target and source when the Append Data dialog is opened. This feature is enabled by default.

Automatically store the column mapping for the given table: Stores each mapping routine after executing the Append operation. The mapping routine is saved using the "tablenameMapping (dbname.tablename)" format. This may also be performed manually using the Store Mapping button. It is enabled by default, and this feature was added in MySQL for Excel 1.1.0.

Reload stored column mapping for the selected table automatically: If a stored mapping routine exists that matches all column names in the source grid with the target grid, then it is automatically be loaded. This is enabled by default, and this feature was added in MySQL for Excel 1.1.0.

The advanced Field Data Options:

Use the first 100 (default) Excel data rows to preview and calculate data types. This determines the number of rows that the preview displays, and the values that affect the automatic mapping feature.

Use formatted values: The data from Excel is treated as Text, Double, or Date. This is enabled by default. When disabled, data is never treated as a Date type, so for example, this means that a date would be represented as a number.

The Stored Column Mappings is a list of saved column mappings that were saved with with the "Automatically store the column mapping for the given table" feature, or manually with the Store Mapping option.

Chapter 6. Export MySQL Data

Data from a Microsoft Excel spreadsheet can be exported to a new MySQL database table by using the Export Excel Data to New Table option. Exporting data looks like so:

Figure 6.1. Exporting Excel data to MySQL

Advanced Export options

Several advanced options enables you to tweak the exported data. The advanced options dialog looks like so:

Figure 6.2. Exporting Excel data to MySQL (Advanced options)

Column Datatype Options:

Use the first 100 (default) Excel data rows to preview and calculate data types: This determines the number of rows that the preview displays, and the values that affect the automatic mapping feature.

Analyze and try to detect correct datatype based on column field contents: Attempts to analyze the data and determine the data type for the column. The column type is defined as VARCHAR if it contains multiple types.

Add additional buffer to VARCHAR length (round up to 12, 25, 45, 125, 255): When the data type is automatically detected and is set to VARCHAR, then it calculates the maximum length for all rows within the column, and rounds up the maximum length to one of the defined lengths above.

If disabled, then the VARCHAR length is set to the length of the longest entry in the Excel spreadsheet.

Automatically check the Index checkbox for Integer columns: If enabled (default), columns with an Integer data type will have the Create Index option enabled by default.

Automatically check the Allow Empty checkbox for columns without an index: If enabled (default), columns without the Create Index checkbox checked will automatically enable the Allow Empty configuration option.

Field Data options:

Use formatted values: When enabled (default), the data from Excel is treated as Text, Double, or Date. When disabled, data is never treated as a Date type, so for example this means that a date would be represented as a number.

Other options:

Remove columns that contain no data, otherwise flag them as "Excluded": If enabled, columns without data in Excel are removed and not shown in the preview panel. If disabled (default), these columns will exist but have the Exclude Column option checked. This option was added in MySQL for Excel 1.1.0.

Appendix A. Third Party Licenses

Use of any of this software is governed by the terms of the licenses that follow.