LOAD DATA INFILE with variable number of columns
Hi evreybody,
I'm trying to import to a table the data in a file that has variable number of columns for each line (record).
I used the following command:
LOAD DATA INFILE 'C:\\LUCA\\SCOP files\\dir.com.scop.txt_1.69'
INTO TABLE scop_comments
FIELDS TERMINATED BY '!' ENCLOSED BY '' ESCAPED BY '\\'
IGNORE 4 LINES
( SCOP_ID,
COMMENT_0, COMMENT_1, COMMENT_2, COMMENT_3, COMMENT_4,
COMMENT_5, COMMENT_6, COMMENT_7, COMMENT_8, COMMENT_9 );
From the documentation I expected that "If an input line has too few fields, the table columns for which input fields are missing are set to their default values", in this case being NULL for the COMMENT_X fields.
Instead I got the following
ERROR 1261 (01000): Row 1 doesn't contain data for all columns
After that I tried to load just the first 2 columns with the following command
mysql> LOAD DATA INFILE 'C:\\LUCA\\SCOP files\\dir.com.scop.txt_1.69'
-> INTO TABLE scop_comments
-> FIELDS TERMINATED BY '!' ENCLOSED BY '' ESCAPED BY '\\'
-> IGNORE 4 LINES
-> ( SCOP_ID, COMMENT_0 );
Ad I got the following error
ERROR 1262 (01000): Row 59 was truncated; it contained more data than there were
input columns
even if the documentation runs "If an input line has too many fields, the extra fields are ignored and the number of warnings is incremented".
Am I getting something wrong or could be the documentation is not exact for these cases?
Thanks,
Miguel