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?

Sylvain Leroux · Accepted Answer · 2013-08-02 10:53:02Z

12

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

answered Aug 2, 2013 at 10:53

Sylvain Leroux

52.4k8 gold badges114 silver badges136 bronze badges

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

1 Comment

Kumar Deepak Over a year ago

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.

wchiquito · Accepted Answer · 2013-08-02 12:24:17Z

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)

Collectives™ on Stack Overflow

MYSQL upload from CSV maintaining nulls

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related