help - SQL script for reading data from a txt file

the /home/tp.txt file is regularly updated on the server and its content looks like this:


As I am a beginner and I do not know how to do it, please help. From the text I need to take the date, time, and temperature and write to the database with the name of the temperature table in this format - id, date, time, ind, temp:
where id is automatically, date in format 2021-10-25, time in format 12:40:01, ind always add 5, temperature is <ia0> 228 </ia0> and I need to save it as 22.80 and end the script run. I will run the script with the scheduler every 5 minutes.
Thank you so much for your help

What is ind?

And where is the temperature in that file?

I need to add the number 5 to the text in each start-up - it’s the temperature sensor number, nothing more. And the temperature is hidden here between ia0


which means temperature 22.8 and I need to write it as 22.80

This seems to be a job for your server language rather than SQL. How far have you got with this?

I need an expression like: mysql> LOAD DATA LOCAL INFILE ‘/home/tp.txt’ INTO TABLE temperature
and further parsing from txt file. I can’t use php for that, I have to run it using mysql and script. I would need that - but I can’t regularly write an expression directly in sql. Thank you.

No one will help or know how to do it?

INFILE textfile.txt
INTO TABLE mytable
SET ia0 = @ia0 /100

(It’s been a really long time since I’ve done preprocessing using MySQL’s infeed… this is really not the recommended method.)

1 Like

I know it’s non-standard, but I need to get the date and time there, not just the temperature, and you are on lines 1 and 2, and I still need that date in the format above.
Thank you

The date and time is already in your data. sec4 is a timestamp.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.