Search code examples
mysqlconcatenationfile-ioifnull

LOAD DATA INFILE should fill up empty values with specific content

LOAD DATA INFILE should fill up only empty values with a specific content.

CSV:

Value1, Value2
-------------- 
1       123345 
2        
3       678901

EXPECTED RESULT:

Value1, Value2
--------------
1       123345
2       k0000123345
3       678901

LOAD DATA INFILE .... SET .... ean = IFNULL(@d3, CONCAT('k0000',an))

...will result in still empty database values.

Solution

  • When you're using LOAD DATA INFILE an empty field in the input file is treated as an empty string (for string types) An empty string is not the same as null.

    So for your input, your empty fields will fail the IFNULL() test. Check the length of the incoming data in your field and concatenate if it's zero:

    LOAD DATA INFILE .... SET .... ean = IF(CHAR_LENGTH(@d3)>0,@d3,  CONCAT('k0000',an))