MySQL "LOAD DATA INFILE" is importing unquoted "NULL" string as `NULL`

Question

I'm using MySQL 5.7.35. If I use the LOAD DATA INFILE command on a CSV file with NULL as an unquoted string value in the CSV file, the value is imported as NULL in MySQL.

For example, if I import a CSV file with the following content:

record_number,a,b,c,d,e,f
1,1,2,3,4,5,6
2,NULL,null,Null,nUlL,,"NULL"

The imported table will have the following values:

+---------------+------+--------+--------+--------+--------+--------+
| record_number | a    | b      | c      | d      | e      | f      |
+---------------+------+--------+--------+--------+--------+--------+
|             1 | 1    | 2      | 3      | 4      | 5      | 6      |
|             2 | NULL | "null" | "Null" | "nUlL" | ""     | "NULL" |
+---------------+------+--------+--------+--------+--------+--------+

Is there any way to force column a, record 2, to be imported as a string without modifying the CSV file?

Update

@Barmar Pointed out that there's a paragraph in the MySQL documentation on this behavior here:

If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

Barmar · Accepted Answer · 2022-01-18 21:48:18Z

2

This is documented here:

If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, which is read as the string 'NULL'.

So you need to specify the quoting character with something like FIELDS ENCLOSED BY '"' and then write "NULL" in the CSV file.

You could check for a NULL value in your code and convert it to a string.

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (record_number, @a, @b, @c, @d, @e, @f)
  SET a = IFNULL(@a, 'NULL'),
      b = IFNULL(@b, 'NULL'),
      c = IFNULL(@c, 'NULL'),
      d = IFNULL(@d, 'NULL'),
      e = IFNULL(@e, 'NULL'),
      f = IFNULL(@f, 'NULL')

However, this can't distinguish between an intentional NULL written as \N and MySQL treating NULL as NULL.

edited Jan 18, 2022 at 21:48

answered Jan 18, 2022 at 21:06

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

C.Liddell Over a year ago

Ah, I see. So there's no way to import NULL as a string short of enclosing it in whatever character sequence is specified using the FIELDS ENCLOSED BY clause? If so, that's really disappointing. I often times don't have control over the CSV files which are being imported.

Barmar Over a year ago

I've updated the answer to show how you can check for it using user variables.

C.Liddell Over a year ago

You're updated suggested is looking like a good candidate solution. The only potential problem is if a field contained \N and it was converted to "NULL". Thanks for the help!

Barmar Over a year ago

Yeah, I don't think there's a complete solution.

Collectives™ on Stack Overflow

MySQL "LOAD DATA INFILE" is importing unquoted "NULL" string as `NULL`

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related