csv - MySQL Load Data Infile Null and Expression -

csv - MySQL Load Data Infile Null and Expression -

i take info simple csv file , set mysql table (which have prepared). of columns have values of 'null' strings, , can convert null values on import. however, values not null set through conversion formula, can successfully. problem cannot seem both @ once. is, want take values column in csv file, set them through formula before import, , yet take out 'null' strings , replace them null values in mysql. far code this:

load info infile 'c:/users/spencer/desktop/webprojects/weatherman/csvdata/day2/alabama_day2.csv' table alabama_d fields terminated ',' lines terminated '\n' ignore 1 rows (primary_id, station_id, @date, @precipitation, @snowfall, @maxtemp, @mintemp) set date = str_to_date(@date, '%m/%d/%y'), precipitation = nullif(@precipitation, 'null'), precipitation = @precipitation/100 * 2.54, snowfall = nullif(@snowfall, 'null'), snowfall = @snowfall/100 * 2.54, maxtemp = nullif(@maxtemp, 'null'), maxtemp = @maxtemp/10 * 9/5 + 32, mintemp = nullif(@mintemp, 'null'), mintemp = @mintemp/10 * 9/5 + 32;

if set nullif's first, null values correctly imported, info not go through formulas. if set formulas first, work, null values not imported. stuck how can both @ once. ideas appreciated. thanks.

turns out not making utilize of one-line if statement. code works. is:

load info local infile 'c:/users/spencer/desktop/webprojects/weatherman/csvdata/day2/alabama_day2.csv' table alabama_d fields terminated ',' lines terminated '\n' ignore 1 rows (primary_id, station_id, @date, @precipitation, @snowfall, @maxtemp, @mintemp) set date = str_to_date(@date, '%m/%d/%y'), precipitation = if(@precipitation = 'null', null, @precipitation/100 * 2.54), snowfall = if(@snowfall = 'null', null, @snowfall/10 * 2.54), maxtemp = if(@maxtemp = 'null', null, @maxtemp/10 * 9/5 + 32), mintemp = if(@mintemp = 'null', null, @mintemp/10 * 9/5 + 32);

mysql csv import null formula