Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Employee Using MySQL for Excel
+4 Vote Up -0 Vote Down

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

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

 

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

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

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

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

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

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

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

 

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!

Votes:

You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.