CODEWITHSUNDEEP
mysqlsqlcsv
xing lee
xing lee

Reputation: 11

MySQL import with wizard, No null values

I tried to import data from csv to mysql with wizard, rows contain null values are not included in the imported table.

How to fix it?

Upvotes: 1

Views: 773

Answers (1)

jMarcel
jMarcel

Reputation: 993

You need to handle the NULL in you csv file, as described on MySQL manual :

Handling of NULL values varies according to the FIELDS and LINES options in use:

For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is ).

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'.

If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.

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. This causes both NULL values and empty strings in the table to be indistinguishable when written to the file because both are written as empty strings. If you need to be able to tell the two apart when reading the file back in, you should not use fixed-row format. So you if there is NULL in csv data, replace it by \N.

E.g.: replace

1, foo, NULL, bar

by :

1, foo, \N, bar

Upvotes: 1

Related Questions