MySQL load infile from csv NULL last column

Question

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??

You must escape "\" so try: ... SET energy = nullif(@energy,'\\N') — Rimas
– Rimas, Commented Sep 15, 2014 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
– andrewgcross, Commented Sep 15, 2014 at 22:14
Try to use your first load statement, but change last clause to: LINES TERMINATED BY '\r\n' — Rimas
– Rimas, Commented Sep 16, 2014 at 4:50

andrewgcross · Accepted Answer · 2014-09-16 16:28:40Z

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

answered Sep 16, 2014 at 16:28

andrewgcross

2633 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL load infile from csv NULL last column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related