Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb


Connectors and APIs Manual  /  ...  /  Using Connector/ODBC with Microsoft Word or Excel

5.6.5 Using Connector/ODBC with Microsoft Word or Excel

You can use Microsoft Word and Microsoft Excel to access information from a MySQL database using Connector/ODBC. Within Microsoft Word, this facility is most useful when importing data for mailmerge, or for tables and data to be included in reports. Within Microsoft Excel, you can execute queries on your MySQL server and import the data directly into an Excel Worksheet, presenting the data as a series of rows and columns.

With both applications, data is accessed and imported into the application using Microsoft Query, which lets you execute a query though an ODBC source. You use Microsoft Query to build the SQL statement to be executed, selecting the tables, fields, selection criteria and sort order. For example, to insert information from a table in the World test database into an Excel spreadsheet, using the DSN samples shown in Section 5.5, “Configuring Connector/ODBC”:

  1. Create a new Worksheet.

  2. From the Data menu, choose Import External Data, and then select New Database Query.

  3. Microsoft Query will start. First, you need to choose the data source, by selecting an existing Data Source Name.

    Figure 5.30 Microsoft Query Wizard: Choose Data Source Dialog

    Shows the initial "Choose Data Source" dialog in the Microsoft Query wizard. The three available tabbed sections are "Databases", "Queries", and "OLAP Cubes". The "Databases" tab is selected that shows the following fields: "New Data Source", "dBASE Files", "Excel Files", "MS Access Database" and "Test World" where "Test World" is selected. The "Use the Query Wizard to create/edit queries" option's check box is selected.

  4. Within the Query Wizard, choose the columns to import. The list of tables available to the user configured through the DSN is shown on the left, the columns that will be added to your query are shown on the right. The columns you choose are equivalent to those in the first section of a SELECT query. Click Next to continue.

    Figure 5.31 Microsoft Query Wizard: Choose Columns

    Content is described in the surrounding text.

  5. You can filter rows from the query (the equivalent of a WHERE clause) using the Filter Data dialog. Click Next to continue.

    Figure 5.32 Microsoft Query Wizard: Filter Data

    The left pane titled "Column to filter" lists available columns, and the right pane titled "Only include rows where" with six select boxes each with "And" and "Or" options for defining the filter's WHERE clause behavior.

  6. Select an (optional) sort order for the data. This is equivalent to using a ORDER BY clause in your SQL query. You can select up to three fields for sorting the information returned by the query. Click Next to continue.

    Figure 5.33 Microsoft Query Wizard: Sort Order

    Shows sorting options "Sort by" and "Then by", each with "Ascending" and "Descending" options.

  7. Select the destination for your query. You can select to return the data Microsoft Excel, where you can choose a worksheet and cell where the data will be inserted; you can continue to view the query and results within Microsoft Query, where you can edit the SQL query and further filter and sort the information returned; or you can create an OLAP Cube from the query, which can then be used directly within Microsoft Excel. Click Finish.

    Figure 5.34 Microsoft Query Wizard: Selecting A Destination

    Content is described in the surrounding text.

The same process can be used to import data into a Word document, where the data will be inserted as a table. This can be used for mail merge purposes (where the field data is read from a Word table), or where you want to include data and reports within a report or other document.