Load Data example

I saw a recent question about LOAD DATA on the forums.mysql.com site so I thought I would post my solution example here as well.

The user in question was getting a lot of skipped rows and no warnings. The user also wanted to skip the header row and I assume set some of the fields as it was imported. Since I did not see any of the related data or schema I just posted the following working example:


CREATE TABLE `example` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Column2` varchar(14) NOT NULL,
  `Column3` varchar(14) NOT NULL,
  `Column4` varchar(14) NOT NULL,
  `Column5` DATE NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB

Column1 Column2 Column3 Column4 Column5
1 A Foo sdsdsd 4/13/2013
2 B Bar sdsa 4/12/2013
3 C Foo wewqe 3/12/2013
4 D Bar asdsad 2/1/2013
5 E FOObar wewqe 5/1/2013

# more /tmp/example.csv
Column1,Column2,Column3,Column4,Column5
1,A,Foo,sdsdsd,4/13/2013
2,B,Bar,sdsa,4/12/2013
3,C,Foo,wewqe,3/12/2013
4,D,Bar,asdsad,2/1/2013
5,E,FOObar,wewqe,5/1/2013

> LOAD DATA LOCAL INFILE '/tmp/example.csv'
    -> INTO TABLE example
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id, Column2, Column3,Column4, @Column5)
    -> set
    -> Column5 = str_to_date(@Column5, '%m/%d/%Y');
Query OK, 5 rows affected (0.04 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

> select * from example;
+----+---------+---------+---------+------------+
| Id | Column2 | Column3 | Column4 | Column5    |
+----+---------+---------+---------+------------+
|  1 | A       | Foo     | sdsdsd  | 2013-04-13 |
|  2 | B       | Bar     | sdsa    | 2013-04-12 |
|  3 | C       | Foo     | wewqe   | 2013-03-12 |
|  4 | D       | Bar     | asdsad  | 2013-02-01 |
|  5 | E       | FOObar  | wewqe   | 2013-05-01 |
+----+---------+---------+---------+------------+
5 rows in set (0.00 sec)