1

Trying to understand, is there any way to Import specific columns from text-file into mysql with matching existing value in the table.

Ex: I have a table, Ref_by and city does not have any values, wanted to load data from txt file. For values id 1 - Ref_by is 'Name1' and city 'Mumbai' & id 2 - Ref_by is 'Name2' and city 'Chennai'

drop table table1;

create table table1 (id int, name varchar(50),Ref_by varchar(50),Dept varchar(50), city varchar (100));
insert into table1 (id,name,Dept) values (1,'a','IT');
insert into table1 (id,name,Dept) values (2,'b','IT');

select * from table1;

enter image description here

If I load: it is going to as next rows, not sure how to match ID column from MYSQL to text files. Is it possible?

LOAD DATA INFILE 'K:/Load/db/table1_columns.txt' INTO TABLE table1 FIELDS TERMINATED BY '|' ENCLOSED BY ''  (Ref_by,city);

enter image description here

Needed result:

1 a name1 IT Mumbai 
2 b name2 IT Chennai

2 Answers 2

3

You can do this by first loading data into a temp or data holding table:

e.g.

LOAD DATA INFILE 'K:/Load/db/table1_columns.txt' INTO TABLE hold_table1 FIELDS TERMINATED BY '|' ENCLOSED BY ''  (Ref_by,city);

Then use join to match values from 'hold_table1' with target table 'table1' and either ADD or Update records accordingly per your condition

2
  • Then use join to match values from 'hold_table1' with target table 'table1' and either ADD or Update records accordingly per your condition Your code does not allow to set strict one-to-one rows matching in UPDATE.
    – Akina
    Dec 16, 2022 at 11:12
  • Thank you, I will try it Dec 16, 2022 at 14:33
2

You must load your data in separate table, static or temporary, and update current table with another query.

In your case this can be:

-- create temptable for data loading
CREATE TEMPORARY TABLE tmp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_by VARCHAR(255),
    city VARCHAR(255)
    ) ENGINE = Memory;

-- load data from CSV to this table
LOAD DATA INFILE 'K:/Load/db/table1_columns.txt' 
INTO TABLE table1 
FIELDS TERMINATED BY '|'
LINES TERMINATED BY `\r\n`
(Ref_by,city);

-- update working table
UPDATE table1 
JOIN tmp USING (id)
SET table1.ref_by = tmp.ref_by,
    table1.city = tmp.city;

id AI PK column in temptable provides imported rows enumeration which is used in UPDATE in rows matching criteria.

If id values in destination table table1 contains holes then you'd enumerate the rows in this table and use this number in joining criteria.

1
  • Thank you, I will try it Dec 16, 2022 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.