How To - Guide to exporting data from Excel to a new MySQL table

There may be times when you need to create a new table in MySQL and feed it with data, normally this data may come from another database, the Internet or from combined data sources. MS Excel is commonly used as the bridge between those data sources and a target MySQL database because of the simplicity it offers to organize the information to then just dump it into a new MySQL table. Although the last bit sounds trivial, it may actually be a cumbersome step, creating ODBC connections within Excel through Microsoft Query may not help since these are normally created to extract data from MySQL into Excel, not the opposite. What if you could do this in a few clicks from within Excel after making your data ready for export to a MySQL database?
With MySQL for Excel you can do this and this guide will teach you how easy it is.
This guide assumes you already have a working MySQL Server instance, Microsoft Office Excel 2007 or higher and MySQL for Excel installed. This guide also focuses on dumping the Excel data into a new MySQL table using the Export Excel Data to New Table feature in MySQL for Excel. We will explain how to dump the Excel data into an existing MySQL table using the Append Excel Data to Table feature in a different blog post.

If you are already familiar with MySQL for Excel and have a connection to a MySQL server instance you can skip directly to step 4.

1. Opening MySQL for Excel

In Excel, go to the Data tab located in the Ribbon and click MySQL for Excel at the far right of the Ribbon.

2. Creating a MySQL Connection (may be optional)

If you have MySQL Workbench installed you will see the same connections that you have in MySQL Workbench, in that case there may be no need to create a new connection.

If you still want to create a new connection, in the Welcome Panel click New Connection, which opens the MySQL Instance Connection dialog. Here you only need to give your new connection a distinctive Connection Name, specify the Hostname (or IP address) where the MySQL Server instance is running on (if different than localhost), the Port to connect to and the Username and Password for the login.

Optionally if you wish to test if your setup is good to go, click Test Connection and an information dialog will pop-up stating if the connection is successful or errors were found.

3.Opening a connection to a MySQL Server

To open a pre-configured connection to a MySQL Server you just need to double-click it.

4. Selecting a MySQL Schema

After opening a connection to a MySQL Server, the Schema Selection Panel is shown, where you can select the Schema that contains the Tables, Views and Stored Procedures you want to work with. To do so, you just need to either double-click the desired Schema or select it and click Next >.

5. Exporting Excel data into a new MySQL table

a. Select data to export.

In the active Excel worksheet select with the mouse the cells containing the data you want to export. When the data is selected the Export Excel Data to New Table action label will become enabled. Click on it.

b. Give the new table a unique name.

This step is required, the Export Data button will remain disabled unless the new table name is entered.

If a name that is already being used by another table is entered, a warning will be displayed just below the Name field stating a table with that name already exists in the database so the user knows beforehand the export operation will fail if the table name is not changed to be a unique one.

c. Select the table's primary index.

The new table will have a primary key created for it, if MySQL for Excel detects the first column in the selected Excel data contains whole numbers, the Use existing column radio button will be automatically checked and that column used as the table's primary key (it is strongly suggested to make sure the numbers do not repeat if the intention is to actually use that column as a primary key).

If the first column does not contain whole numbers, then the Add a Primary Key column radio button will be automatically selected, in which case MySQL for Excel will prepend a numeric incremental column with a name like "<table_name>_id". This prepended column will also be known in this guide as an AutoPK column. Note in the screenshot below how the AutoPK column name was set to "customer_id2" and not "customer_id" because a column named "customer_id" was already present.

The user can override the default selection MySQL for Excel sets for the table's primary key by checking any of the 2 radio buttons. If the Use existing column option is selected the user can flag several columns to create a multi-column primary key, this can be done by selecting each column in the grid and checking the Primary Key checkbox for each desired column. You can tell that a multi-column primary key is being set because the combo box next to the Use existing column radio button will display a value of "<Multiple Items>".

c. Specify or tweak column options.

The last important step is to tweak the features of columns that will be created in the new table. The Column Options groupbox below the data preview grid shows the options that can be set for each of the columns. Note that MySQL for Excel will smartly set these for you based on the selected data, for example by default MySQL for Excel assumes the first row of the selected Excel data contains the column names, you can tell because the First Row Contains Column Names checkbox is checked by default, if the checkbox is manually unchecked by the user the first row of data is not used for column names but as the actual first row of data and columns will be named as Column1, Column2, and so forth. Also the Datatype combo box will be automatically set to the one of the most common datatypes (contained in the Datatype combo box) that MySQL for Excel determines is best suited for the data in that column. MySQL for Excel will analyze the data in each column and smartly detect the best data type for it only if the corresponding advanced option is turned on (see down below the screenshot with the Export Data dialog's advanced options).
The column options work as follows:

• Column Name: the name of the column in the new table, must comply with the Schema Object Names rules.
• Datatype: a MySQL valid data type, the combo box contains the most common ones but any valid data type can be entered manually in the combo box field. For a complete list of valid MySQL data types refer to the MySQL Server documentation's Chapter 11: Data Types.
• Create Index: when checked, an index will be created for the selected column. For more information about indexes refer to the MySQL Server documentation's Chapter 8.5.1: Column Indexes and Chapter 8.5.3: How MySQL Uses Indexes to determine when they are useful.
• Unique Index: when checked, a unique index will be created for the selected column which is useful when you want to ensure no duplicates values are inserted into that column.
• Primary Key: when checked, it means the selected column is part of the table's primary key. For more information about primary keys refer to the MySQL Server documentation's Chapter 8.3.2: Using Primary Keys.
• Allow Empty: when checked, the selected column will have the NULL attribute set when created, meaning NULL values are allowed in the column. For more information about NULL values refer to the MySQL Server documentation's Chapter 9.1.7: NULL values, Chapter 3.3.4.6: Working with NULL Values and Appendix C.5.5.3: Problems with NULL Values.
• Exclude Column: when checked, the selected column will be excluded from the new table creation process and data export.

d. Export Data or Create Table only.

Finally to apply your changes click on Export Data so MySQL for Excel sends to the MySQL Server the SQL statements needed to create the new table and then feed it with the selected Excel data. Alternatively notice that there is a little down arrow at the right of the Export Data button (available since version 1.2.0) that when clicked opens a context menu where you can select the Create Table option so the text of the button changes to contain that text and when clicked MySQL for Excel will only create the new table as an empty one and no data will be exported.
After the button is clicked and the chosen operation is applied, an information dialog will be displayed showing the results of the operation.

There are also some advanced options that affect the behavior of the Export Data dialog, by clicking Advanced Options... you will see a dialog like the one in the following screenshot.

You can tweak the default settings to suit your needs, for example you can increase the number of analyzed Excel rows to detect the data types (bear in mind that the more rows are analyzed, the more time the Export Data dialog will take to be shown), or turn off the addition of an additional buffer to the length for varchar columns so the exact maximum length of the text in the analyzed rows of that column is used. For example in the 100 analyzed rows of a column where the data type is suggested as varchar the longest text has let's say 28 characters, so the data type is detected as Varchar(28) whereas applying the buffer will bump the length to be Varchar(45).

As you can see it is very easy to copy data from an Excel worksheet into a new MySQL table with its column settings properly set. Hope this guide is useful to you!
Remember that your feedback is very important for us, so drop us a message and follow us: