CODEWITHSUNDEEP
pythonmysqlreplaceload-data-infile
Paul Munters
Paul Munters

Reputation: 1

How do I properly use LOAD DATA LOCAL INFILE IGNORE 1 LINES to update an existing table?

I successfully loaded large CSV files (with 1 header row) to a MySQL table from Python with the command:

LOAD DATA LOCAL INFILE 'file.csv' 
INTO TABLE 'table" 
FIELDS TERMINATED BY ';' 
IGNORE 1 LINES 
(@vone, @vtwo, @vthree) 
   SET DatumTijd = @vone, 
       Debiet = NULLIF(@vtwo,''),
       Boven = NULLIF(@vthree,'')

The file contains historic data back to 1970. Every month I get an update with roughly 4320 rows that need to be added to the existing table.

Sometimes there is an overlap with the existing table, so I would like to use REPLACE. But this does not seem to work in combination with IGNORE 1 LINES. The primary key is DatumTijd, which follows the MySQL-datetime format.

I tried several combinations of REPLACE and IGNORE in different order, before the INTO TABLE "table" and behind FIELDS TERMINATED part.

Any suggestions how to solve this?

Upvotes: 0

Views: 214

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

Apart from the possible typo of enclosing the table name in single quotes rather than backticks the load statement works fine on my windows device given the following data

one;two;three
2023-01-01;1;1
2023-01-02;2;2
2023-01-01;3;3
2022-01-04;;;

note I prefer coalesce to nullif and have included an auto_increment id to demonstrate what replace actually does , ie delete and insert.

drop table if exists t;
create table t(
id int auto_increment primary key,
DatumTijd date,
Debiet varchar(1),
boven varchar(1),
unique index key1(DatumTijd)
);

LOAD DATA INFILE 'C:\\Program Files\\MariaDB 10.1\\data\\sandbox\\data.txt' 
replace INTO TABLE t 
FIELDS TERMINATED BY ';' 
IGNORE 1 LINES (@vone, @vtwo, @vthree) 
SET DatumTijd = @vone, 
Debiet = coalesce(@vtwo,''),
Boven = coalesce(@vthree,'')
;

select * from t;

+----+------------+--------+-------+
| id | DatumTijd  | Debiet | boven |
+----+------------+--------+-------+
|  2 | 2023-01-02 | 2      | 2     |
|  3 | 2023-01-01 | 3      | 3     |
|  4 | 2022-01-04 |        |       |
+----+------------+--------+-------+
3 rows in set (0.001 sec)

It should not matter that replace in effect creates a new record but if it does to you consider loading to a staging table then insert..on duplicate key to target

Upvotes: 0

Related Questions