Cannot load Mysql LOAD DATA LOCAL INFILE

Question

Trying to load a small .txt file into mysql but get all my data show in one fields except the null and \n.

This is the data layout:

Fluffy,Harold,cat,f,1993-02-04,null \N
Claws,Gwen,cat,m,1994-03-17,null \N
Buffy,Harold,dog,f,1989-05-13,null  \N
Fang,Benny,dog,m,1990-08-27,null  \N
Bowser,Diane,dog,m,1995-08-31,1998-07-29 \n
Chirpy,Gwen,bird,f,1998-09-11,null  \N
Whistler,Gwen,bird,null,1997-12-09,null  \N
Slim,Benny,snake,m,1996-04-29,null  \N

The command I using is: LOAD DATA LOCAL INFILE"c://petdata.txt" INTO TABLE pet; this seem to be working as the data is going into table but in the wrong format.

This is my output

mysql> SELECT*FROM pet;
+----------------------+-------+---------+------+------------+-------+
| name                 | owner | species | sex  | birth      | death |
+----------------------+-------+---------+------+------------+-------+
| Bowser,Diane,dog,m,1 |       |         |      | 0000-00-00 | NULL  |
| Buffy,Harold,dog,f,1 |       |         |      | 0000-00-00 | NULL  |
| Chirpy,Gwen,bird,f,1 |       |         |      | 0000-00-00 | NULL  |
| Claws,Gwen,cat,m,199 |       |         |      | 0000-00-00 | NULL  |
| Fang,Benny,dog,m,199 |       |         |      | 0000-00-00 | NULL  |
| Fluffy,Harold,cat,f, |       |         |      | 0000-00-00 | NULL  |
| Slim,Benny,snake,m,1 |       |         |      | 0000-00-00 | NULL  |
| Whistler,Gwen,bird,n |       |         |      | 0000-00-00 | NULL  |
+----------------------+-------+---------+------+------------+-------+
8 rows in set (0.00 sec)

user1432124 · Accepted Answer · 2012-06-19 11:10:34Z

5

Try

LOAD DATA LOCAL INFILE 'c://petdata.txt'
  INTO TABLE `pet`
  FIELDS
    TERMINATED BY ','
  LINES
    TERMINATED BY '\n'

answered Jun 19, 2012 at 11:10

user1432124

Sign up to request clarification or add additional context in comments.

1 Comment

user1432124 Over a year ago

Don't forget to click on the check mark on the left side of this answer

Collectives™ on Stack Overflow

Cannot load Mysql LOAD DATA LOCAL INFILE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related