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

Question

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.

Does this answer your question? import numbers into mysql from text file where comma is used as decimal separator — kmoser
– kmoser, Commented Feb 21, 2022 at 16:28

Bernd Buffen · Accepted Answer · 2022-02-24 20:28:30Z

1

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);

edited Feb 24, 2022 at 20:28

answered Feb 21, 2022 at 16:52

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Matze Q Over a year ago

Great! That was exactly what I was looking for. There is a small syntax error in your answer: The multipe SET have to be separated by ,:

(@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);

Bernd Buffen Over a year ago

@Matze Q - thx - i have change my answer

Matze Q Over a year ago

Please change your answer again :) there should be no SET before the second and third occurrence of the commands. One SET and then all commands separated by comma. Never the less is your answer working great!

Bernd Buffen Over a year ago

done :-) , OMG its not my day

kmoser · Accepted Answer · 2022-02-21 16:25:51Z

0

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).

answered Feb 21, 2022 at 16:25

kmoser

9,3693 gold badges28 silver badges45 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related