MySQL Forums
Forum List  »  General

LOAD DATA INFILE with variable number of columns
Posted by: Miguel Ceriani
Date: December 25, 2005 09:41AM

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

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.