Convert String or Float to Int when Importing a CSV into MySQL

I need to import Data from a CSV-File into MySQL/MariaDB. Here is an example of the data:

01.01.2021 00:15;0,000000;W;123,000000;W;9,000000;W;0,000000;W;9,000000;W
01.01.2021 00:30;0,000000;W;126,000000;W;9,000000;W;0,000000;W;9,000000;W
01.01.2021 00:45;0,000000;W;119,000000;W;6,000000;W;0,000000;W;6,000000;W

I want to use Fields Nr. 1, 4, 6. The Date in the first column needs to be converted into SQL datetime. I managed all of that. But I am stuck when converting the 4th and 6th value into an Int (or an Float if some one will add decimals to the measurement).

My current code which works fine is:

LOAD DATA LOCAL INFILE '/tmp/eam_energy.csv' 
INTO TABLE  grafana.elec_eam_delivered 
FIELDS OPTIONALLY ENCLOSED BY '"' 
TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 18 ROWS 
(@datetime, @dummy, @dummy, active_power, @dummy, reactive_power, @dummy, @dummy, @dummy, @dummy, @dummy) 
SET datetime = STR_TO_DATE(@datetime, '%d.%m.%Y %H:%i');

active_power and reactive_power are integers in the schema. When importing the values are cut at the ',' (which is the German decimal delimiter). But I get a warning in SHOW WARNINGS:

Warning | 1265 | Data truncated for column 'reactive_power' at row 32

I want the import to work without any warnings. How can I convert the 123,000000 into either 123 or 123.000000 ?

Thanks in advance.

Solution

You can also do the conversion direct by the import like this:

LOAD DATA LOCAL INFILE '/tmp/eam_energy.csv' 
INTO TABLE  grafana.elec_eam_delivered 
FIELDS OPTIONALLY ENCLOSED BY '"' 
TERMINATED BY ';' 
LINES TERMINATED BY '\n' 
IGNORE 18 ROWS 
(@datetime, @dummy, @dummy, @active_power, @dummy, @reactive_power, @dummy, @dummy, @dummy, @dummy, @dummy) 
SET DATETIME = STR_TO_DATE(@datetime, '%d.%m.%Y %H:%i'),
active_power = SUBSTRING_INDEX(@active_power,',',1),
reactive_power = SUBSTRING_INDEX(@reactive_power,',',1);