How can I set Mysql loaded table items in a column using a case when statement

Question

A column in a csv file that I am has 3 letters: c, e, and n. When I am importing the data, I want them to change to 2, 1, and 0, respectively. When I run CASE WHEN in the SET statement, the table only returns 2 for every row. What am I doing wrong here?

load data local infile 'C:\\Scripts\\Storagefile.csv'
into table  infra.storage
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
ignore 1 rows

(status, gasday, gasinstorage, percent_full, net_change, injection, withdrawal, workingstorage, injection_cap, withdrawal_cap, @dummy)

set 
status = (SELECT case
        when status = 'C' then 2
        when status = 'E' then 1
        when status = 'N' then 0 end),  
entity = 'EU',
gasinstorage = gasinstorage * 3.142142,
net_change =  net_change * 3.142142,
injection = injection  * 3.142142,
withdrawal = withdrawal * 3.142142,
workingstorage = workingstorage * 3.142142,
injection_cap = injection_cap  * 3.142142,
withdrawal_cap = withdrawal_cap * 3.142142,
reportdate = current_timestamp()

Answer 1

Here is a sql solution for that purpose. To preprocess the input, you need to first assign the CSV value to a variable, and then use the set clause.

This should be close to what you want:

load data local infile 'C:\\Scripts\\Storagefile.csv'
into table infra.storage (
    @status, 
    gasday, 
    @gasinstorage, 
    percent_full, 
    @net_change, 
    @injection, 
    @withdrawal, 
    @workingstorage, 
    @injection_cap, 
    @withdrawal_cap, 
    @dummy
)
fields terminated by ','
enclosed by '"'
lines terminated by '\r\n'
ignore 1 rows
set 
    status = case @status
        when 'C' then 2
        when 'E' then 1
        when 'N' then 0
    end,
    entity = 'EU',          
    gasinstorage = @gasinstorage * 3.142142,
    net_change =  @net_change * 3.142142,
    injection = @injection  * 3.142142,
    withdrawal = @withdrawal * 3.142142,
    workingstorage = @workingstorage * 3.142142,
    injection_cap = @injection_cap  * 3.142142,
    withdrawal_cap = @withdrawal_cap * 3.142142,
    reportdate = current_timestamp()
;