How can I load blank/NULL values with LOAD DATA INFILE from the MySQL command line

user_14384914 0
Viewed 677

I am using the following command from the MySQL command line to try and import a csv file into one of my tables:

LOAD DATA INFILE 'file path' INTO TABLE table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(BASEID, BIGID, StartDate, EndDate)

Some of my rows have no value for EndDate which I want to be represented as NULL values in the database.

Unfortunately when I execute the above command I get the following error:

for column 'EndDate' at row 141lue:

If I remove the rows with blank cells the command works, so it is clearly the blank values for EndDate which are causing the problem.

I have also tried changing my csv file so that the blank cells say NULL or \N. I have also tried the following command instead but I still get the same error message:

LOAD DATA INFILE 'file path' INTO TABLE Table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(BASEID, BIGID, StartDate, @EndDate)
SET EndDate = nullif(@EndDate, ' ')

How can I load csv files which have some blank values? The suggest solutions I have seen on other posts don't seem to work as outlined above.

mysql
1 Answers

Is the issue that the value for the end date is missing, or that the column itself is missing? These are not the same thing. For the former case, I think LOAD DATA should be able to handle this, assuming that the target column for the end date can tolerate missing/null values.

What I suspect here is that some of your input lines look like this:

1,1,'2020-10-03'

That is, there is no fourth column present at all. If this be the case, then the most prudent thing to do here might be to run a simple regex over your input CSV flat file to fix these missing fourth column edge cases. You may try:

Find:    ^([^,]+,[^,]+,'[^,]+')$
Replace: $1,

This would turn the sample line above into:

1,1,'2020-10-03',

Now, the date value is still missing, but at least LOAD DATA should detect that the line has four columns, instead of just three.

user_18632290
Related