load data terminated by does not import

0
votes

Table: ID int not null primary key auto_increment number int not null unique

my file: 111 222 333

LOAD DATA LOCAL INFILE 'file.txt' IGNORE INTO TABLE MyTable fields terminated by '\n' (number); - everything works fine. But if I have:

file: 111;222;333

LOAD DATA LOCAL INFILE 'file.txt' IGNORE INTO TABLE MyTable fields terminated by ';' (number); - it imports only 111 and stops. Why?

1
mysql

1 Answers

0
votes

If you want to add a list of values separated by a ";" into a single table field use this. This will basically treat each value as a separate record.

LOAD DATA LOCAL INFILE 'file.txt' IGNORE 
INTO TABLE MyTable 
LINES TERMINATED BY ';'
(number) 
;

If you want to insert the 3 fields from the file into the first three fields in the table, remove (number). By including (number) you were specifying that you only wanted to insert data only into the number field.

LOAD DATA LOCAL INFILE 'file.txt' IGNORE 
INTO TABLE MyTable fields terminated by ';';

If you want to insert the three fields from the file into three specific fields in the table (not necessarily the first three) you need to list all three. For example if you wanted to insert them into the fields number, field2 and field3 the command would be:

LOAD DATA LOCAL INFILE 'file.txt' IGNORE 
INTO TABLE MyTable fields terminated by ';' (number, field2, field3);