27.4 Import MySQL Data into Excel

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 Export

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.

Import: Advanced Options

Figure 27.6 Importing table data with MySQL for Excel: Advanced options

Importing table data with MySQL for Excel: Advanced options

General Options:

  • Use the first [ ] rows to preview the MySQL tables data. Defaults to 10.

  • [ ] Escape text values that start with "=" so Excel does not treat them as formulas. Enabled by default.

Excel Table Options:

  • [ ] Create an Excel table for the imported MySQL table data. Enabled by default.

  • Use style [ ] for the new Excel table. Defaults to MySqlDefault.

  • [ ] Prefix Excel tables with the following text: _______. Disabled by default.

Importing a table displays a dialog similar to the following:

Figure 27.7 Importing table data with MySQL for Excel

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 27.8 Importing called stored procedure data with MySQL for Excel

Importing called stored procedure data with MySQL for Excel