conditionally ignore rows load data mysql

Question

I am loading a large data set (60 gigs) into a database. There are some records that can be skipped because they contain values that are missing. How do I tell MySQL to skip rows that contain certain (missing in this case) values? For example, my file looks like this

Value1, Value2
1,2
3,4
,5
9,10

The third row can be skipped and not loaded. I know that I can load everything and then just delete it but processing a 60 gig file takes a long time so I want to save on the computing power.

Thanks

How are you loading the data? – OMG Ponies Feb 27 '12 at 05:27 — OMG Ponies, Feb 27 '12 at 05:27
I am loading using the load data command – Alex Feb 27 '12 at 21:30 — Alex, Feb 27 '12 at 21:30

score 5 · Answer 1 · answered Feb 27 '12 at 07:31

If 'Value1' is a unique field, then you can play with an IGNORE option. For example:

CREATE TABLE table1(
  Value1 INT(11) NOT NULL,
  Value2 INT(11) DEFAULT NULL,
  PRIMARY KEY (Value1)
);

LOAD DATA INFILE 'file.txt' 
  IGNORE -- forces to ingore existed records
  INTO TABLE table1
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\r\n'
  IGNORE 1 lines
  (@var1, Value2)
  SET Value1 = IF(@var1 = 0, 1, @var1)

'IF(@var1 = 0, 1, @var1)' helps us to change missing values '0' with EXISTED KEY VALUE '1', and these records will be ignored.

Naveen Kumar · Accepted Answer · 2012-02-28T05:06:40.567

1

Create a trigger on the before insert on the table which checks if the particular field is null

CREATE TRIGGER before_insert_test
BEFORE INSERT ON test FOR EACH ROW
BEGIN
    -- condition to check
    IF NEW.Value1 is null THEN
       set msg = concat('MyTriggerError: Trying to insert a null value
                  in trigger_test: ', cast(new.Value1 as char));
   signal sqlstate '45000' set message_text = msg;
    END IF;
END$$

then run your Load data command

edited Feb 28 '12 at 05:06

answered Feb 27 '12 at 08:26

Naveen Kumar

4,543
1
18
36

thank you for answering, could you please explain what a trigger does? i am not familiar with this concept. your help is much appreciated. – Alex Feb 27 '12 at 21:32
The above trigger, checks before each insert on the table if the value is null then row is not inserted. – Naveen Kumar Feb 28 '12 at 05:07
This doesn't seem to work for me; instead none of my rows are inserted; I have posted this question; http://stackoverflow.com/questions/28595441/mysql-trigger-to-skip-certain-rows-during-load – user1420372 Feb 18 '15 at 22:47

score 0 · Answer 3 · edited Apr 02 '20 at 16:47

0

I have been experiencing the same issue, you just need to add IGNORE to your statement.

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

see https://mariadb.com/kb/en/library/load-data-infile/

edited Apr 02 '20 at 16:47

enharmonic

1,800
15
30

answered Jul 09 '19 at 16:10

Vince

1

1

that SQL does not look as if it is anything to do with the example in the question. Would you like to update it to be applicable to the question? – Mike Poole Jul 09 '19 at 16:18

conditionally ignore rows load data mysql

3 Answers3