1

I have a .csv file that contains 5 columns of data. Occasionally, the last column contains a NULL value, like so.

2014-07-11 23:55:00,1,245,0.05,0.01,0.0003
2014-07-11 23:57:00,1,245,0.05,0.01,\N
2014-01-17 20:14:00,2,215,0.05,0.009,0.002

I'm attempting to load this into a local database, so from the MySQL console I run the following code:

LOAD DATA LOCAL INFILE 'C:/<redacted>/data.csv' 
INTO TABLE tbl_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

This method does work just fine when the column that contains the NULL value is moved so that it isn't in the last column, but it throws warnings back at me when the data is formatted as shown above.

Query OK, 71735 rows affected, 49 warnings
' for column 'energy' at row 5253    |  value: 'N

I followed advice from this thread, and attempted to utilize a local variable, but that didn't appease the MySQL gods either.

LOAD DATA LOCAL INFILE 'C:/<redacted>/data.csv' 
INTO TABLE tbl_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
(time, device, voltage, amps, power, @energy)
SET energy = nullif(@energy,'\N')

Oh, and the table structure is set up like so, and I'm running MySQL 5.6.17:

+--------+-------------+-----+---------+
|NAME    |TYPE         |NULL | DEFAULT |
|time    |datetime     |No   | None    |
|device  |tinyint(1)   |No   | None    |
|voltage |smallint(3)  |No   | None    |
|amps    |decimal(6,3) |No   | None    |
|power   |decimal(6,3) |No   | None    |
|energy  |decimal(7,4) |Yes  | NULL    |
+--------+-------------+-----+---------+

What am I doing wrong here??

Community
  • 1
  • 1
andrewgcross
  • 253
  • 2
  • 13
  • You must escape "\" so try: `... SET energy = nullif(@energy,'\\N')` – Rimas Sep 15 '14 at 22:01
  • That didn't seem to do the trick, either. I should mention I've also tried editing the original data to indicate the NULL fields as NULL, '', and 'baloney' and adjusting the nullif command accordingly: all to no avail. – andrewgcross Sep 15 '14 at 22:14
  • 3
    Try to use your first load statement, but change last clause to: `LINES TERMINATED BY '\r\n'` – Rimas Sep 16 '14 at 04:50

1 Answers1

3

Rimas was correct, the last clause in my initial statement should have been: LINES TERMINATED BY '\r\n'

The relevant documentation is worded as follows:

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

andrewgcross
  • 253
  • 2
  • 13