How To - Guide to appending data from Excel to an existing MySQL table

Today we're going to talk about one of the main features in MySQL for Excel.

There may be times when you have a MySQL Database table that you want to add new rows to. In this blog post we're going to show you how you can do that from within excel directly.

This guide assumes you already have a MySQL Server set up and running, Microsoft Office Excel 2007 or newer and MySQL for Excel installed. We will explain how to dump data into an existing MySQL table using the Append Excel Data to Table feature.

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. Type data to be append to MySQL table into Excel. 
In the active Excel worksheet, type the data you want to be added to a MySQL table:

If you have column names we’ll show you how to take advantage of the auto-mapping functionality in step 6.b. the columns order doesn’t need to be the same as in the MySQL table as we’ll see next.

6. Select Excel data to append into an existing MySQL table
In the active Excel worksheet select with the mouse the cells containing the data you want to append. For this step we could think of two main possibilities:

a. Select data not including column names.
In this case we only select raw data that we want to append to a table:

b. Select data including column names.
In this case we select the raw data that we want to append to a table plus the row containing the names of each column in the selected range:

Once the data is selected, click over the table you want to append data to, inside the main panel. Finally, click the Append Excel Data to Table action label when it becomes enabled.

The Append Data dialog that appears afterwards has two grid sections, on the first we can see a preview of the selected data, in the second one we can see how this selected data is being mapped. Notice how the name of the columns in the first section (in green) show the correspondent mapping to a table column in the second section.

If you don’t have column names in your selected range it is probably better to leave unchecked the above First Row Contains Column Names checkbox. This will allow us to include the first row of selected data as part of the appending process like in the example above.

If you have column names in your selected range you could check the First Row Contains Column Names checkbox and take advantage of the auto-mapping feature (like in the example below). Notice how the columns order doesn’t need to be the same as in the MySQL table:

The Automatic mapping first attempts to map columns by column name, if no columns can be mapped that way then it attempts to match ordinal positions and if and only if the source column's data type can "fit" into the target column's data type (Notice the example above). The combo box for Mapping Method at the top of the first section is set to Automatic in the past two examples, for the next one we will change it to Manual and select how columns will be mapped. Once the value of the combo is selected is time to unbind the data columns.

To do so we right click the destination grid and we’ll see a pop up menu like the one in next figure:

We can remove one by one the unwanted column mappings, or clear the mapping of all the columns at once. For this example we will click Clear All Mappings. Once all columns are unbind (and appear in orange). We want to map a column from the selected range to a column in the table. To do so we simply drag and drop a column from the top grid and drop it into the desired target column in the bottom grid:

Take into account that you can drag and drop a source column multiple times to different destination columns:

After we had finished with the manual mapping, we can optionally store this mapping for future append operations. Simply click the Store Mapping button as it becomes enabled. If the Automatically store the column mapping for the given table advanced option is checked, there is no need to store the mapping since it will be stored automatically (see below for an explanation of all advanced options). Then select a name for this custom column mapping and click on OK to save it.

If you click over Advanced Options you will see some 3 options for column mapping, 2 for field data options and a section to manage the list of the stored mappings.

  • The Mapping Options are:
    • Perform an automatic mapping when dialog opens. (The Automatic column mapping will be defaulted, otherwise skipped and manual column mapping will be requested).
    • Automatically store the column mapping for the given table. The column mapping will be related to the current appending table. Note this will be very useful when you store column mappings to allow the last stored column mapping for that MySQL table be the one used when you open the Append Data dialog the next time.
    • Reload stored column mapping for the selected table automatically. This option will take advantage of column mappings which relationship with a given table has been established. Note this will become useful when you store new column mappings, the default column mapping for that MySQL table will be the one you stored last.
  • The Field Data Options are:
    • Use the first <number> Excel data rows to preview and calculate data types. For MySQL for Excel it is important to run data detection diagnostics as quickly as possible to return a preview almost immediately. As default MySQL for Excel sweeps through a small sample of the data being selected to do so. Lowering the value of this option will decrease the size of the analyzed sample, lowering the time required for the analysis but increasing the chances of miscalculating a data type too, the opposite occurs if the value is increased.
    • Use formatted values. When checked, if MySQL for Excel detects special Excel data types like Date, it performs the casting of the values to their corresponding MySQL type. The value is passed as is otherwise.
  • And in the section to manage the list of the Stored Column Mappings we can see a couple of buttons to Rename or Delete whatever column mapping we selected on the list.

Once we have the data mapped correctly, we can click on the Append button at the Append Data dialog and see the results of that operation in the following dialog:

The details of the operation can be browsed when we click on Show Details in the dialog:

Your feedback is very important for us, please drop us a message and follow our social networks:

Post a Comment:
  • HTML Syntax: NOT allowed

Here's where you'll hear about all the new and fun stuff we are doing with MySQL on Windows.


« March 2015