MySQL load data infile ("NULL" String in CSV for datetime)

Question

Hi I am have the following CSV format

id   (tab)  creation_date
-------------------------
1           2012-05-04 15:26:45.000
2           NULL

For the importing I am using:

load data local infile './test.csv' into table TEST fields terminated by '\t'
  lines terminated by '\n'
  IGNORE 1 LINES
    (id,@creation_date)
  SET
    creation_date = nullif(@creation_date,'NULL');

But unfortunately the NULL value (String) is not interpreted and set to a NULL in the column. Instead I get a warning about data truncation and a default date is inserted 0000-00-00....

How can I check: Is String = "NULL" then insert NULL into column?

If you are able to modify the CSV before loading to replace NULL with \N, you can load it correctly without the SET: dev.mysql.com/doc/refman/5.7/en/… — Jacob Lambert
– Jacob Lambert, Commented Mar 2, 2020 at 23:17

Hmerman6006 · Accepted Answer · 2021-11-27 09:48:01Z

1

The documentation has rules to how NULL is interpreted in the file depending on you query fields and lines used, in simple terms:

If FIELD and LINES is default values in your query use \N (NO QUOTES).

If FIELD ENCLOSED BY and/or FIELD ESCAPED BY is used, use the word NULL (NO QUOTES).

With fixed-row format (which is used when FIELDS TERMINATED BY and FIELDS ENCLOSED BY are both empty), NULL is written as an empty string i.e. "".

Your use case is the first bullet.

**Read more from the docs in the bottom paragraph Handling of NULL values varies according to the FIELDS and LINES options in use: of the section link above.

edited Nov 27, 2021 at 9:48

answered Nov 27, 2021 at 9:41

Hmerman6006

2,0393 gold badges29 silver badges60 bronze badges

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

Comments

Pravellika · Accepted Answer · 2014-03-10 12:37:22Z

0

You can use "TRAILING NULLCOLS" to insert null values from the file into database.

Ex:

load data
infile 'test1.csv'
APPEND
into table tab1
TRAILING NULLCOLS
( 
id integer <data type> TERMINATED BY ',',
creation_date <data type>
 )

answered Mar 10, 2014 at 12:37

Pravellika

18214 bronze badges

1 Comment

cloudnaut Over a year ago

Hi, sorry I can't follow :( (...and I don't find now docs about TRAILING NULLCOLS). I found out that the value is not only "NULL" but also contains the carriage return cause it s the last row

cloudnaut · Accepted Answer · 2014-03-11 10:27:41Z

Beware, it#s so stupid but I managed the error. Since the date is the last column in the row and no '"' are used to split the columns mysql is of cause interpreting the column with a trailing carriage return!

Solution:

load data local infile './test.csv' into table TEST fields terminated by '\t'
  lines terminated by '\n'
  IGNORE 1 LINES
    (id,@creation_date)
  SET
    creation_date = nullif(@creation_date,'NULL\r');

I know it looks soo dirty!

EDITED: so of course using the line termination for this would be wiser ;)

load data local infile './test.csv' into table TEST fields terminated by '\t'
  lines terminated by '\r\n'
  IGNORE 1 LINES
    (id,@creation_date)
  SET
    creation_date = nullif(@creation_date,'NULL');

... maybe someone ends up with the same problem and want to follow my path :)

user9846973 · Accepted Answer · 2021-03-17 15:00:07Z

0

Use \N insted of NULL string and it's work perfectly

answered Mar 17, 2021 at 15:00

user9846973

572 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQL load data infile ("NULL" String in CSV for datetime)

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related