CODEWITHSUNDEEP
mysqlsqlimport
cloudnaut
cloudnaut

Reputation: 982

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

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?

Upvotes: 3

Views: 7739

Answers (4)

Hmerman6006
Hmerman6006

Reputation: 1931

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.

Upvotes: 1

user9846973
user9846973

Reputation: 57

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

Upvotes: 0

cloudnaut
cloudnaut

Reputation: 982

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 :)

Upvotes: 0

Pravellika
Pravellika

Reputation: 182

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>
 )

Upvotes: 0

Related Questions