How to import an excel file in to a MySQL database

Question

Can any one explain how to import a Microsoft Excel file in to a MySQL database?

For example, my Excel table looks like this:

Country   |   Amount   |   Qty
----------------------------------
America   |   93       |   0.60

Greece    |   9377     |   0.80

Australia |   9375     |   0.80

Thanks in Advance.

Fero

Answer 1

the best and easiest way is to use "MySQL for Excel" app that is a free app from oracle. this app added a plugin to excel to export and import data to mysql. you can download that from here

Answer 2

For a step by step example for importing Excel 2007 into MySQL with correct encoding (UTF-8) search for this comment:

"Posted by Mike Laird on October 13 2010 12:50am"

in the next URL:

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Answer 3

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.

Answer 4

Not sure if you have all this setup, but for me I am using PHP and MYSQL. So I use a PHP class PHPExcel. This takes a file in nearly any format, xls, xlsx, cvs,... and then lets you read and / or insert.

So what I wind up doing is loading the excel in to a phpexcel object and then loop through all the rows. Based on what I want, I write a simple SQL insert command to insert the data in the excel file into my table.

On the front end it is a little work, but its just a matter of tweaking some of the existing code examples. But when you have it dialed in making changes to the import is simple and fast.