Learn in 5 steps : How to import Excel data into a MySQL database ?

1) Open your Excel file and click Save As. Choose to save it as a .CSV (Comma Separated) file. If you are running Excel on a Mac, you will need to save the file as a Windows Comma Separated (.csv) or CSV (Windows) to maintain the correct formatting.

2) Log into your MySQL shell and create a database. For this example the database will be named testdb. Note that the --local-infile option is needed by some versions of MySQL for the data loading we’ll do in the following steps.

$ mysql -u root -p --local-infile

mysql> create database testdb;

mysql> use testdb;

3) Next we’ll define the schema for our test table using the CREATE TABLE command. For more details, see the MySQL documentation.

CREATE TABLE tests (

d INT NOT NULL PRIMARY KEY,

name VARCHAR(40),

type VARCHAR(10),

owner_id INT NOT NULL,

date_made DATE,

rental_price FLOAT

);

4) Run show tables to verify that your table was created.

mysql> show tables;

+------------------+

| Tables_in_testdb |

+------------------+

| tests            |

+------------------+

5) Now that there is a database and a table setup, the data can be imported with the LOAD DATA command.

LOAD DATA LOCAL INFILE "/path/to/tests.csv" INTO TABLE testdb.tests

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n'

IGNORE 1 LINES

(id, name, type, owner_id, @datevar, rental_price)

set date_made = STR_TO_DATE(@datevar,'%m/%d/%Y');