csv import mysql null

Question

I have to import some csv-formatted data into a mysql (mariadb) database. There is no csv header and the lines look like this:

00323acd-7909-41a4-a849-073ca3391dcf,2014-05,\N
00323acd-7909-41a4-a849-073ca3391dcf,2014-05,1

So they contain some hex-id, a year/month combination and a optional int value. I use the \N (hex: 5c 4e) to mark NULL values (mysql uses this style to export null values as well).

LOAD DATA LOCAL INFILE 'path/to/data.csv' INTO TABLE data_table 
FIELDS TERMINATED BY ',' ENCLOSED BY '' 
LINES  TERMINATED BY '\n' (id, @date_time_variable, value) 
SET date = STR_TO_DATE(@date_time_variable, '%Y-%m');

But it seems like the database does not recognise the '\N'-coded Null-values.

Query OK, 38581 rows affected, 14596 warnings (0.54 sec)
Records: 38581  Deleted: 0  Skipped: 0  Warnings: 14596

MariaDB [run5]> show warnings;
+---------+------+-------------------------------------------------------------+
| Level   | Code | Message                                                     |
+---------+------+-------------------------------------------------------------+
' for column 'value' at row 1   |ger value: 'N
' for column 'value' at row 2   |ger value: 'N
' for column 'value' at row 3   |ger value: 'N

The schema looks like this:

CREATE TABLE `data_table` (
  `id` char(36) NOT NULL,
  `date` date NOT NULL,
  `value` int(11) DEFAULT NULL,
  KEY `mbid` (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Does someone know this error or a solution?

EDIT:

here is the output of show warnings \G;:

MariaDB [run5]> show warnings \G;
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 1lue: 'N
*************************** 2. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 2lue: 'N
*************************** 3. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 3lue: 'N
*************************** 4. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 4lue: 'N
*************************** 5. row ***************************
  Level: Warning
   Code: 1366
' for column 'value' at row 5lue: 'N
...

And here is a hexdump of the file:

~/D/path ❯❯❯ head -n 2 data/file.csv | hexdump -C                                           master ✱ ◼
00000000  30 30 33 32 33 63 63 64  2d 37 39 30 39 2d 34 31  |00323ccd-7909-41|
00000010  61 34 2d 61 38 34 39 2d  30 37 33 63 61 33 33 39  |a4-a849-073ca339|
00000020  31 64 63 66 2c 32 30 31  34 2d 30 35 2c 5c 4e 0d  |1dcf,2014-05,\N.|
00000030  0a 30 30 33 32 33 63 63  64 2d 37 39 30 39 2d 34  |.00323ccd-7909-4|
00000040  31 61 34 2d 61 38 34 39  2d 30 37 33 63 61 33 33  |1a4-a849-073ca33|
00000050  39 31 64 63 66 2c 32 30  31 34 2d 31 32 2c 5c 4e  |91dcf,2014-12,\N|
00000060  0d 0a                                             |..|
00000062

The third row contains 2c 5c 4e, which stands for ,\N and this is correct, or isn't it?

EDIT 2:

I updated the query because I used the wrong line terminator (\n instead of \r\n). Now I get a single error message regarding this wrong value:

MariaDB [run5]> LOAD DATA LOCAL INFILE '/path/data.csv' INTO TABLE data_table FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES  TERMINATED BY '\n\r' (id, @date_time_variable, value) SET date = STR_TO_DATE(@date_time_variable, '%Y-%m');
Query OK, 1 row affected, 1 warning (0.01 sec)       
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

MariaDB [run5]> show warnings \G
*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'N
00323ccd-7909-41a4-a849-073ca3391dcf' for column 'value' at row 1
1 row in set (0.00 sec)

Why are you using hexdump to analyse the data? What format is the csv file in? (`file file.csv`) — Gene, Aug 21 '15 at 09:35
I updated my query (changed the line terminator) but now there is only one row in the table and only a single warning. — NaN, Aug 21 '15 at 09:43
You should have lines terminated by \r\n, not the other way around, for standard Windows format text files. — Jules, Aug 21 '15 at 09:49

score 3 · Accepted Answer · answered Aug 21 '15 at 09:46

3

I haven't tried this, but my reading of the manual for LOAD DATA INFILE is that interpretation of "\N" sequences is controlled by the ESCAPED BY clause, and that the default is not to perform escape sequence processing. If this is right, adding "ESCAPED BY '\\'" to your query should have the results you desire.

answered Aug 21 '15 at 09:46

Jules

240
2
11

Thanks, this solved the problem! I thought this is the default value so I did't set it. – NaN Aug 21 '15 at 09:51

score 1 · Answer 2 · answered Aug 21 '15 at 09:13

1

I suspect that the data that is being imported is malformed. Specifically \N is missing \ in some or all cases.

Verify the data is correct. You can do something like this:

$ awk -F, '$3=="\\N"{print $0}' moo.csv
00323acd-7909-41a4-a849-073ca3391dcf,2014-05,\N

$ awk -F, '$3=="N"{print $0}' moo.csv
00323acd-7909-41a4-a849-073ca3391dcf,2014-05,N

Also, the warning output is cut off. Try the import again then use the following:

show warnings \G

answered Aug 21 '15 at 09:13

Gene

3,663
20
39

Both commands return nothing (not even a single line). I will add a hex dump output to my question to show that there is a valid \N – NaN Aug 21 '15 at 09:26

csv import mysql null

2 Answers2