load data infile partially cuts phone numbers?

Question

I made a table which name is 'test' in mysql in my PC like belows.

create table test(
telnum varchar(20) not null,
reg_date datetime not null default '0000-00-00 00:00:00',
remarks text,
primary key(telnum)
);

And I uploaded a file named 1.txt into table 'test'. 1.txt's contents are like belows :

01011112222 

01022223333 

01033334444

And 'load data infile' syntax are like belows :

load data infile "c:/temp/1.txt"
ignore into table test;

But there is a problem.

Every phone numbers were cut like below.

Query OK, 3 rows affected, 3 warnings (0.00 sec) 
Records: 3  Deleted: 0  Skipped: 0  Warnings: 3 

mysql> select * from test; 

+-------------+---------------------+---------+ 
| telnum      | reg_date            | remarks | 
+-------------+---------------------+---------+ 

      |12222 | 0000-00-00 00:00:00 | 
      |23333 | 0000-00-00 00:00:00 | 
      |34444 | 0000-00-00 00:00:00 | 
+-------------+---------------------+---------+ 
3 rows in set (0.00 sec)

Only 5 characters are remained from 11 characters. 6 characters disappeared.

And second problem is 'warnings'. Reason of warnings is like below.

mysql> show warnings; 
+---------+------+---------------------------------------------------+ 
| Level   | Code | Message                                           | 
+---------+------+---------------------------------------------------+ 
| Warning | 1264 | Out of range value for column 'reg_date' at row 1 | 
| Warning | 1264 | Out of range value for column 'reg_date' at row 2 | 
| Warning | 1264 | Out of range value for column 'reg_date' at row 3 | 
+---------+------+---------------------------------------------------+ 

3 rows in set (0.00 sec)

I did'nt put anything into the reg_date field. But the message says out of range value for column 'reg_date'.

What are the reasons and how can I solve these problems?

The question being long adds a little confusion, can you create a sqlfiddle example — skv

peterm peterm · Accepted Answer · 2013-08-25T07:10:58

Try to change the line delimiter. On Windows it's usually \r\n rather then \n which is default if you omit LINES TERMINATED BY clause

LOAD DATA INFILE "/tmp/1.txt"
IGNORE INTO TABLE test
LINES TERMINATED BY '\n'

load data infile partially cuts phone numbers?

1 Answers