MYSQL upload from CSV maintaining nulls

Question

I have a MYSQL database containing a bunch of fields, many of which are configures as numerical values, either:

thickness double(7,2) DEFAULT NULL,

or

has_input tinyint(4) DEFAULT NULL,

I have to import a csv file into this table and have been using

load data local infile 'myfilepath/file.csv' into table combined fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

but all numerical fields are having null values replaced by zero. This is really annoying as some fields have a legitimate value of zero and I need to be able to distinguish these from the fields with no value.

As you can see above, the field defaults are set to null and, on other advice, I've entered \N in all the blank fields in the csv to indicate null.

Nevertheless, the nulls are still being replaced with zero. Can you help?

Solution 1

As the rules for handling NULL columns while importing CSV are rather complexes, there is a dedicated section on handling NULL value in the doc for LOAD DATA INFILE:
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

In your specific case:

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

---

Try to pre-process the CSV file by replacing missing values by the word NULL (without quotes).

If you have something like that in your orginal CSV file:

0,1,2,,5

It should be transformed like that in order for MySQL to correctly insert NULL in the 4th column:

0,1,2,NULL,5

Solution 2

It could be treated in the same sentence.

Given the following table:

CREATE TABLE `testcsv` (
  `col0` INT(11) NOT NULL,
  `col1` VARCHAR(20) DEFAULT NULL,
  `col2` DECIMAL(5,2) DEFAULT NULL
);

and the following file .csv: test.csv

1,\N,0
2,"string",102.20
3,\N,
4,"string",\N
5,"string",NULL
6,"string",

to run:

LOAD DATA INFILE 'path/test.csv' INTO TABLE `testcsv`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(`col0`, `col1`, @col2)
SET `col2` = IF(CHAR_LENGTH(TRIM(@col2)) = 0, NULL, @col2);

result:

mysql> SELECT `col0`, `col1`, `col2` FROM `testcsv`;

+------+--------+--------+
| col0 |  col1  |  col2  |
+------+--------+--------+
|    1 | NULL   |   0.00 |
|    2 | string | 102.20 |
|    3 | NULL   |   NULL |
|    4 | string |   NULL |
|    5 | string |   NULL |
|    6 | string |   NULL |
+------+--------+--------+
6 ROWS IN SET (0.00 sec)

Comments

[Kumar Deepak]

Is there an easy way to convert csv with blanks between delimiters to the one with \N between delimiters where it was blank. Simple regex based solutions fail when more than 2 delimiters are together.