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

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.

CodePudding user response：

Change the schema for reactive_power and active_power to VARCHAR(255) and import your file. Then replace ',' with '.':

UPDATE
  grafana.elec_eam_delivered
SET
   reactive_power = REPLACE(reactive_power, ',', '.'),
   active_power = REPLACE(active_power, ',', '.')

Then change the schema for active_power and reactive_power to INT or FLOAT (your choice).

CodePudding user response：

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')
SET active_power = SUBSTRING_INDEX(@active_power,',',1)
SET reactive_power = SUBSTRING_INDEX(@reactive_power,',',1);