3

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?

  • 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 Oct 14 '18 at 23:01
4

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.

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

  • 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 Jan 8 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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