Load Data with Null Column at the end of the row into MySql

Asked Nov 16 '15 at 18:02

Active Nov 16 '15 at 18:09

Viewed 486 times

I am having trouble loading a txt file into MySql that has a null value at the end of the row.

The txt file looks like

"a"|"b"|"c"|"d"|"e"|"f"|"g"|"h"|"i"
"1"|1997-09-30|"1"|1997-09-30|.01|1997-10-01|1997-09-29|"0"|
"2"|1997-10-31|"2"|1997-10-31|.02|1997-11-03|1997-10-30|"0"|1.5
"3"|1997-11-28|"3"|1997-11-28|.03|1997-12-01|1997-11-27|"0"|

notice the empty column at the end of the row

I am trying to insert into MySql via

LOAD DATA INFILE 
'C:/temp/test.txt' 
INTO TABLE test 
FIELDS TERMINATED BY '|' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

I receive the error

`Error Code: 1265. Data truncated for column 'i' at row 1

Any ideas how I can insert this correctly? Accounting for the null column at the end of the row.

edited Nov 16 '15 at 18:09

asked Nov 16 '15 at 18:02

Roni Hoffman

try adding `(a,b,c,d,e,f,g,h)` after `test` that will set all rows for "i" to null i think, worked for me in the past – Andriy Lysak Nov 16 '15 at 18:06
Sorry Andriy edited the question, "i" column is not always null, so I cant just omit it. – Roni Hoffman Nov 16 '15 at 18:10
1

http://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data This might help – Edwin Krause Nov 16 '15 at 18:35
Thanks Edwin that helped – Roni Hoffman Nov 16 '15 at 19:42

Load Data with Null Column at the end of the row into MySql

0 Answers0