Todd's MySQL Blog

Tiny tidbits of trivia from Todd

Using MySQL for Excel

I recently had a need to import a bunch of data into MySQL, and for reasons I won’t get into here, LOAD DATA INFILE wasn’t working for me.  I was in a hurry to get the data into MySQL so that I could filter it, and didn’t have time to work through my LOAD DATA INFILE issues.  Looking around for another way to quickly get my tab-delimited data into MySQL quickly, I decided to use the MySQL for Excel plugin.  If you haven’t seen this yet, and you have Excel, it might be worth your while.

In my case, I was able to quickly copy and paste the tab-delimited text file into an Excel spreadsheet, connect MySQL for Excel to the MySQL database and append the data to the existing table.  You’ll need to have Excel, MySQL and the MySQL for Excel plugin.  The latter two can be installed from a single download using the MySQL Installer for Windows.

Here’s a very quick visual tutorial on how this works:

First, create the table.  I’m doing this in MySQL Workbench:, but you can do the same via the mysql command-line client if you prefer:

Create the table in MySQL Workbench

Create the table in MySQL Workbench

Next, open Excel and click on the “Data” tab:

 

Navigate to Data tab in Excel

Navigate to Data tab in Excel

You’ll see the “MySQL for Excel” button if you’ve installed the MySQL for Excel product using the MySQL Windows Installer.  Click on it, and a menu opens up on the right-hand side.  This lists the saved connections you’ve created in Workbench, or allows you to create a new connection:

Select MySQL connection to use

Select MySQL connection to use

I double-clicked the correct instance (“3307″ is my stored connection to the instance I have running on port 3307 locally), which brings  up a list of database schemas:

Select database schema

Select database schema

Selecting the “test” database brings up a list of tables, including the excel_test table I created earlier:

Select table in Excel

Select table in Excel

Selecting the excel_test database enables options to import or edit MySQL data.  In the next screenshot, I’ve added some data to the Excel cells and highlighted it.  This enables the “Append Excel Data to Table” option we want:

Select data to append to table

Select data to append to table

That brings up a wizard to help you map the Excel columns to database columns.  In my case, the automatic mapping worked right, but you can make changes to ensure that the mapping is done correctly:

Map columns to table structure

Map columns to table structure

Clicking the “Append” button in the wizard completes the process, and a confirmation message is displayed.  I expanded the optional details for the following screenprint:

Confirm data appended successfully

Confirm data appended successfully

For confirmation, I return to Workbench and query the table:

 

Verify table changes using MySQL Workbench

Verify table changes using MySQL Workbench

If you are an Excel user and haven’t yet tried this new tool, give it a go – it’s worth the time!

Single Post Navigation

4 thoughts on “Using MySQL for Excel

  1. kontakt@comborm.de on said:

    you know anything about the problem installing teh plugin? Here ist the discussion of the problem? It is discussed here:http://forums.mysql.com/read.php?169,563914,563914#msg-563914

  2. Todd Farmer on said:

    I haven’t experienced that problem myself, but I see that forum thread has the attention of the Windows Installer developers. I’m sure they can help sort out problems seen.

  3. I have a free stand alone program I wrote on my website at panofish called excel2myql… not to be confused with other tools with a similar name. I believe it is the fastest and easiest way to convert excel data into mysql tables. Let me know if you agree or disagree and any feedback welcome. Alan

  4. One of the problems I often have when helping an organisation not from IT department but from business is to load data. To be honest I am still often using ‘vi’ as an editor since it runs absolutely everywhere. For loading from Excel into database such as MySQL I use either one of the following three:

    * Add a column to the Excel sheet with a formula like =”insert into table(…) values (…)” and then copy & paste that into a SQL tool.
    * Use Invantive Control (note: I work there) to download facts into Excel, change them/append data to the tables and send them all back. Works from within Microsoft Excel itself and requires .NET 4.5.
    * Use Kettle Pentaho (it is free!) to exchange data between Excel and any database supported. Requires more setup and not for end users but only needs Java and maybe some drivers to get running.

Leave a Reply

Your email address will not be published. Required fields are marked *


nine + 2 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>