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!