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:
Next, open Excel and click on the “Data” tab:
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:
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:
Selecting the “test” database brings up a list of tables, including the excel_test table I created earlier:
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:
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:
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:
For confirmation, I return to Workbench and query the table:
If you are an Excel user and haven’t yet tried this new tool, give it a go – it’s worth the time!
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
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.
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
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.