5

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?

Improve this question
1
  • For situations like this I would use python library d6tstack which makes it easier to deal with null values, data schema changes, multiple files etc. See d6tstack SQL examples
    – citynorman
    Commented Oct 14, 2018 at 23:01

2 Answers 2

Reset to default
6

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.

Improve this answer
2
  • 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
    Commented Apr 15, 2015 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
    Commented Apr 15, 2015 at 13:19
0

I had '|' field separators. I opened csv in good editor like vim and substituted || with |\N|. Yes Uppercase N. That's what a tutorial said to use. In vim:
:% s/||/|\\N|/gc

gc is 'global" 'confirm'.
In mine there were two blank fields together ie: |||
Vim would only do the first for some reason. So I had to do the command a second time which got the second.

Then mysql loaded the file.

Improve this answer
1
  • FYI: Vim would have changed ||| to |\\N||, leaving its cursor between the second and third pipes (i.e., |\\N|<here>|. From that point, it only sees one more pipe, so it doesn't make a second change.
    – RDFozz
    Commented Jan 8, 2019 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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