CODEWITHSUNDEEP
mysqlcsvfile-ioload
andrewgcross
andrewgcross

Reputation: 263

MySQL load infile from csv NULL last column

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

Upvotes: 1

Views: 2026

Answers (1)

andrewgcross
andrewgcross

Reputation: 263

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

Upvotes: 3

Related Questions