Excel Spreadsheet Import to MySQL

Question

I want to add about 1000 records to a table that contains 31 fields.

The worst part is those 1000 records are in the form of an excel sheet having some columns left unfilled. Is there any easy way to import this excel sheet into my table having the unfilled entries as NULL ?

Using Linux and MySQL

Answer 1

Since MySQL 5.1, there has been a native tool called MySQL for Excel.

It is bundled along with MySQL for Visual Studio, a bunch of connectors, sample databases and so forth.

The MySQL Documentation has a webpage for

• MSI Installation
• Editing
• Importing
• Appending
• Exporting

There is also a forum for users of this product (As of this posting, there are 5 active questions that have activity this month)

MANUAL ALTERNATIVE

If you have the patience, you could do the following

STEP 01) Create a table using the CSV Storage Engine

When you create the table like this:

CREATE TABLE mydb.mytable
(
 i INT NOT NULL,
 c CHAR(10) NOT NULL,
 ...
 column29 INT NOT NULL,
 column30 CHAR(10) NOT NULL,
 column31 CHAR(30) NOT NULL
) ENGINE=CSV;

This will create two files under the mydb subfolder

• mytable.frm
• mytable.CSV

CAVEAT : I do not think NULL columns are allowed for MySQL CSV tables

STEP 02)

• Using Excel, export the spreadsheet with the same number of columns as the MySQL CSV table. Empty Cells should come out as "".

STEP 03) If using MySQL in Linux, you could do this

cd /var/lib/mysql/mydb
mv mytable.CSV mytable_old.CSV

STEP 04) Copy the Excel output into /var/lib/mysql/mydb/mytable.CSV

STEP 05) In the mysql client, run

FLUSH TABLES;
SELECT * FROM mydb.mytable;

This will close and reopen all tables. If you see your data, CONGRATULATIONS !!!

STEP 06) If you get an Error

If you get an error on SELECT * FROM mydb.mytable;, then one of the cells is not field enclosed with double quotes. Just re-export the data and try Step 04 again until you get it right

For Those using MySQL for Windows

If you have MySQL for Windows, you can drop in mydb\mytable.CSV becauswe of the way Windows locks files. You will have to run net stop mysql, copy the file in, and net start mysql, skip the FLUSH TABLES; and just run SELECT * FROM mydb.mytable;

Give it a Try !!!

• panofish: 1000 records isn't much, so the free Excel for Excel plugin should work fine. If you experience problems with larger Excel files or inaccurate conversions then I use excel2mysql.net

Answer 2

You could use DocChow, a very intuitive GIU for importing Excel into MySQL, and it's free on most common platforms (including Linux).

More especially if you are concerned about date, datetime datatypes, DocChow easily handles datatypes. If you are working with multiple Excel spreadsheets that you want to import into one MySQL table DocChow does the dirty work.

• Hannah Vernon: Hi @Seanj1000 - if you are in any way associated with DocChow you must declare that when answering questions. Please look at the tour to get familiar with our site. And welcome to Stack Exchange!
• Seanj1000: Max thanks for the advise. Full disclosure I do work for DocChow.
• Suraj Shingade: This site is not live anymore !!