Critical Skill 7.3 Import MySQL Data into Access and Excel

Many new users have chosen to learn MySQL because they have outgrown their current manner of maintaining their data. Whether you ve kept business records in a handwritten accounting ledger or stored your records in a spreadsheet or basic database, the moment often comes when that system becomes inadequate for a variety of reasons. Occasionally, however, you might want to manipulate data outside MySQL in an already familiar environment, frequently in Microsoft Excel or Access. This skill will cover how to get MySQL data into these programs.

Note

Excel is a widely available spreadsheet that is often a user s first attempt at storing data in a meaningful and manipulatable format; however a spreadsheet is not as functional as a database. If you want to get data from Excel into MySQL, you can write standard SELECT commands or save the data in Excel into a text file and use the LOAD DATA INFILE , as described in Module 2.

Importing MySQL Data into Excel

You can transfer MySQL data into Excel in two ways: by first exporting a file from MySQL and then opening that file in Excel, or by importing the data into Excel using ODBC.

Exporting from MySQL

MySQL allows you to export data, by using the SELECT INTO OUTFILE function, into a file that can be imported into Excel, with varying levels of difficulty. MySQL will export the contents of a table into a text file. You can use a variety of methods to divide the fields in the table when writing to the text file. The most basic syntax is as follows :

 USE <  database_name  >; SELECT * INTO OUTFILE "<  file_name.txt  >" FROM <  table_name  >;

This syntax results in a file where the fields are separated by tabs, which is the default when a delimiter is not specified.

Another common delimiter is a space, and in fixed-length fields, this results in a file that can be easily read. The following syntax shows how to specify a fixed-width delimiter by using single quotes with nothing, not even a space, between them to signify the field s contents should be padded with spaces to a fixed width. This makes all the fields appear in right-justified columns .

 USE <  database_name  >; SELECT * INTO OUTFILE "<  file_name.txt  >" FIELD TERMINATED BY '' FROM <  table_name  >;

Note	If you receive an error using this syntax, try using the backtick ( ` ) instead of the single quote ( ' ) on the FIELD TERMINATED BY line.

The most compatible way to format a file for Excel is to use comma delimiters. Excel even recognizes a special filename extension for a comma-delimited file, .csv. Naming a comma-delimited file in the file_name. csv format makes Excel think that the file is an actual Excel file and treat it accordingly . The syntax for creating such a file follows:

 USE <  database_name  >; SELECT * INTO OUTFILE "<  file_name  .csv>" FIELD TERMINATED BY ',' FROM <  table_name  >;

To import any file you exported from MySQL using the SELECT INTO OUTFILE function into Excel, simply choose to open a file in Excel (by using the File Open option or clicking the Open File button on the toolbar). In the Open dialog box, browse for the file. MySQL saves the result of the OUTFILE command into the database folder in the Data folder in MySQL. For instance, the path for an OUTFILE result for the duck_cust table in the duckwear database would be C:\mysql\data\duckwear. Click the name of the exported file, and then click the Open button.

For any .txt file, a wizard will open to guide you through the rest of the process. Depending on the way the file was generated, you must select either Delimited, if characters were used to separate the fields, or Fixed Width, if spaces were used to pad the data into fixed width fields.

If you choose Delimited, you must next indicate which character the file uses: Tab, Semicolon, Comma, Space, or Other. The Other choice allows you to input the specific character. You are also given options to treat consecutive delimiters as one and to indicate what, if any characters signify the status of what is held within them as text.
If you choose Fixed Width, the second wizard window allows you to create new columns or move the position of the existing columns.

The third screen of the wizard allows you to select each column and specify whether the contents are General, Text, or Date in nature, or to skip importing that specific column. The Advanced button allows you to specify which characters you use for indicating decimal points and thousands. Once you ve made all these decisions, click the Finish button, and your data will be imported into an Excel sheet.

If you use specified comma delimiters and named the file with a .csv filename extension when you exported from MySQL (as in the third OUTFILE example shown earlier in this section), Excel perceives the file as an Excel file type and opens it immediately.

Importing to Excel Using ODBC

Before you can use ODBC to import data from inside the Excel program, you need to create a Data Source Name (DSN), which is usually the same as the database name. Since you installed the ODBC drivers in Module 1, making the connection is simple. See Project 7-3 for step-by-step instructions for creating a DSN.

After you ve set up the DSN, follow these steps to import the MySQL data into Excel:

Open Excel and select Data Import External Data Import Data New Source.
Choose ODBC DSN and click the Next button.
Enter the DSN name and click the Next button.
Enter the database name and the table name that you want to import, and then click the Next button. The filename is < database_name > < table_name >.odc.
Click the Finish button.
Select the file you want to import (< database_name > < table_name >.odc) and click Open.
Select existing worksheet @ =$a$1, and then click OK.

Importing MySQL Data into Access

You can get data from MySQL into Access by importing the data directly into Access tables. You can also link to a MySQL table from Access. The question that might be occurring to you is, Why would I want to do either of those things? Importing data into Access or linking directly to a MySQL table from Access allows you to use Access to build queries, forms, and reports on the data. This lets users who are already familiar with Access continue to use its form-creation processes instead of writing HTML-based forms.

The DSN, discussed in the previous section in relation to importing into Excel, is also used to facilitate the connection between MySQL and Access. Access has a Get External Data link under the File drop-down menu that allows you to choose between importing and linking outside data.

Linking to the database from Access allows you to view the data in what is effectively real-time. Changes made to the data within Access will be reflected within the MySQL database; however, you cannot change the format of the table. Since Access has different data types than MySQL, it interprets the MySQL data types to the ones closest to its own data types. Access also uses a different icon from the normal Access table icon to represent the directly linked version.