current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have CSV file with exported table, that has rows with fields that do not contain values. Columns are separated using "," and if fields have no values they look like that: ,,,,,, I tried to load data using following code:

LOAD DATA INFILE "C:/table.csv"
INTO TABLE target_table 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

Columns in target table (to be loaded with data from this CSV file) are nullable, but server returns error:

"Incorrect integer value"

When I put NULL between commas in CSV file, server do not return any error. Is it possible to change some setting to have table loaded without changing the CSV file?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Something like this, checking every value for '', and inserting NULL instead, may help. Change a,b,c,d for the actual name and number of rows on the table:

$ cat /tmp/test.csv 
test1, test2, test3, test4
,,,
1,2,3,4
,,,

mysql> LOAD DATA INFILE "/tmp/test.csv" INTO TABLE test.test  
          FIELDS TERMINATED BY ','  
          OPTIONALLY ENCLOSED BY '"' 
          LINES TERMINATED BY '\n' 
          IGNORE 1 ROWS 
       (@a, @b, @c, @d) 
       SET a = IF(@a = '', NULL, @a), 
           b = IF(@b = '', NULL, @b), 
           c = IF(@c = '', NULL, @c), 
           d = IF(@d = '', NULL, @d);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM test.test;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
| NULL | NULL | NULL | NULL |
|    1 |    2 |    3 |    4 |
| NULL | NULL | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

Check the syntax for LOAD DATA for more details.

share|improve this answer
    
I have to perform such operation for multiple files, how to make it more general, like for each empty field (,,) change to NULL at command level ? – R. Nec Apr 15 at 13:10
    
@R.Nec Which OS? It may be better to convert ,, to ,NULL, or \n on command line, on-the-fly, and then use your original command. – jynus Apr 15 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.