How to change particular column entries in a mysql table when uploading data from csv file?

Question

I upload data into a mysql table from csv file in a standard way like this:

TRUNCATE TABLE table_name;
load data local infile '/path/to/file/file_name.csv' into table table_name
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
(id, name, type, deleted);

All 'deleted' column entries in csv file has either 'current' or 'deleted' value.

Question: When csv data is being loaded into table, I want to put current date in table for all those corresponding 'deleted' entries in csv file. And null for 'current' entries. How can I do this?

Example: csv file:

id_1, name_1, type_1, current
id_2, name_1, type_2, deleted
id_3, name_3, type_3, current

Table after loading this data should look like this:

id_1, name_1, type_1, null
id_2, name_1, type_2, 2010-05-10
id_3, name_3, type_3, null

Edit Probably, I could run another separate query after loading csv file. Wondering if it could be done in same query?

Answer 1

Using another query to change those deleted entries is quite simple. I'm going with this solution.

Answer 2

You can use SET clause in LOAD DATA INFILE syntax:

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(column1, column2)
SET column3 = CURRENT_TIMESTAMP;

Answer 3

Couldn't you just load the CSV file into an array and then iterate through it? Or just iterate through the file itself?

Then you would just update/add the date to each record in your table as necessary (if field[2] == "deleted" or something like that).