Load Excel into MySQL via Access

This page describes how to load Excel XLS Worksheets into MySQL via Microsoft Access, the easy way. You can use this same technique to load data quickly into any ODBC data source. The benefit of this method is that the table structure is automatically generated, with every column treated as text. This allows you to quickly load all the workbooks and worksheets, and then you can analyze and modify the data types later if needed.

Setup a MySQL ODBC Data Source

You will first need to install freeware MySQL Connector / ODBC. Just download the apppropriate Windows installer and run it on your system.

Next, set up a System DSN to connect to your MySQL server. Go to "Control Panel" - "Administrative Tools" - "Data Sources (ODBC)", and click the "System DSN" tab. (These instructions are for WIndows XP SP2.) Click the "Add" button, select MySQL ODBC 3.51 Driver, and then press "Finish".

In the "Connector/ODBC 3.51.12 - Add Data Source Name" dialog box, enter a name for the data source that will be the destination for your data. Enter the server name, user, password, and database, and configure any other necessary settings. Use the "Test" button to check the connection.

Prepare Excel Worksheet for Clean Import

We are going to use the Access Import Spreadsheet Wizard. The wizard has the feature of automatically detecting column names (from an optional header row) and determining column data types. To determine the data types, Access scans the first 25 rows of your data.

This may be a problem, because if the first 25 rows contain numbers, and then later rows contain text, Access may have problems importing the data. Examples include import errors, invalid data, skipped data, or improperly converted data.

To avoid any import issues, you have two choices. If you have Access 2007 or later, then you can set the field types during the import wizard. I believe the steps are as follows: Click the Advanced button and look in the "Field Information" list box. There you will see the field name and data type selected by Access. Change the "Data Type" column to Text (or Memo for long text) for each column. Then click OK and continue the import. This is the first choice.

In older version of Access before 2007, you can not change the field types when doing a spreadsheet import (although you can when doing a Text import). You can use the following, simple workaround though. Add 25 blank rows into the worksheet just below the header row in rows 2 through 26. If there is no header row, then use rows 1 through 25. In each cell, enter junk text that is longer than about 500 characters. You can enter the text below, which is 512 characters. This junk data will be deleted later. Save the Excel workbook with a new name to preserve your original data.

abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 abcd12345 123456789012

Import Excel Workbook into Microsoft Access

Create a blank Access database to serve as the intermediate data storage. Click on the "Tables" button so you can work with the table objects in the Access database. Select the menu item "File" - "Get External Data" - "Import". Under "Files of type", select Microsoft Excel (*.xls), choose the file, and click "Import".

Now you will be in the "Import Spreadsheet Wizard". Check the box if the first row contains column headings. Specify "New Table". Just click "Next" when prompted for "Field Options". Either have Access generate a primary key, or select a primary key column. (You can always drop/modify the primary key later if needed.) Specify a table name, and click "Finish". You should see a message that says "Finished importing file 'YourExcelFileNameHere.xls' to table 'YourTableNameHere'."

For a detailed, step-by-step tutorial on how to do this import, see Importing Spreadsheets into Access.

The table will contain all Memo fields. Open the table to view the imported data. Now, you can select the first 25 rows with junk data and delete them. You can also delete any extra columns that you do not need. I suggest leaving the data types as-is, and then modifying them once the table is in MySQL.

Import Microsoft Access Table into MySQL

Right-click on the table, and select "Export". In the "Save as type" drop-down, select "ODBC Databases ()". Choose a name for the table in MySQL (or leave the default). Select the "Machine Data Source" tab, and choose the data source name you created in the first step. Click "OK" to run. An Export progress bar will appear in the bottom left part of the Access application, and it might take a long time if your MySQL server is on a remote server.

The table will automatically be created in the MySQL database you specified. All the fields will have an appropriate data type matching the data type in Access.

Now you can use your favorite, freeware MySQL tool (such as PhpMyAdmin or MySQL Database Administrator) to further modify the table.

To determine optimal data type for each column, use the MySQL 5 PROCEDURE ANALYSE function. For example, just run SELECT * FROM table1 PROCEDURE ANALYSE(); for each of your imported tables.

For more information (Access 2003 specific), see Import, export, and link data between Access and Excel.