0

I have a Python process to load CSV data into MySQL. I am using the 'LOAD DATA' command, and have preprocessed my CSV file to contain '\N' instead of '' so NULL is used on the load instead of an empty string/0. I am using this question as a reference: MySQL load NULL values from CSV data

So, a row of the CSV (pipe delimited) would look like this:

2020-06-11|A|T321|G123|\N|\N|\N

My load command looks like:

LOAD DATA LOCAL INFILE 'xyz.csv'
INTO TABLE schema.table
FIELDS TERMINATED BY '|'
IGNORE 1 ROWS
(
col_1...,
col_n
)

NULL will be loaded into the table for columns 4 & 5, but the final column is not treated the same way. If the final column is '\N', NULL is not used but it will insert 'N' instead. I assume because the default LOAD DATA command looks for "LINES TERMINATED BY '\n'" implicitly? But in these cases, the end of a line will be '|\N\n', so why does it insert the 'N' if there is a newline char after it? If it was just looking for the first newline char at the end of the row, I would assume it would insert the empty string, but why just 'N'?

I have tried to use 'NULL' instead of '\N' in the CSV, but it doesn't appear to work for our MySQL configuration. Is there a simple way to tell 'LOAD DATA' that the final column of the row can contain the NULL indicator '\N'?

Russell Quinlan
  • 31
  • 5
  • \ is always a bad choice, why not keep the NULL or something like ~N – nbk Oct 19 '21 at 21:43
  • MySQL documentation says 'NULL' can be used some cases instead of '\N', but it does not work in my case. It seems these 2 are the only options for LOAD DATA that can specify nulls. – Russell Quinlan Oct 19 '21 at 21:47
  • that thread is from 2010 and a lot of water has passed the rhine, so show the error with NULL or '' – nbk Oct 19 '21 at 21:56

0 Answers0