help - SQL script for reading data from a txt file

Databases
1

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

2021.10.25
12:25:01
<response><out0>0</out0><out1>1</out1><out2>1</out2><out3>0</out3><out4>0</out4><out5>0</out5><out6>1</out6><di0>up</di0><di1>up</di1><di2>up</di2><di3>up</di3><ia0>228</ia0><ia1>115</ia1><ia2>0</ia2><ia3>0</ia3><ia4>4326</ia4><ia5>0</ia5><ia6>760</ia6><ia7>226</ia7><ia8>-600</ia8><ia9>226</ia9><ia10>-600</ia10><freq>5008</freq><duty>500</duty><pwm>0</pwm><sec0>9</sec0><sec1>45</sec1><sec2>23</sec2><sec3>2</sec3><sec4>1635164702</sec4></response>

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

2

What is ind?

And where is the temperature in that file?

3

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

<ia0>228</ia0>

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

4

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

5

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.

6

No one will help or know how to do it?

7 
LOAD XML
INFILE textfile.txt
INTO TABLE mytable
ROWS IDENTIFIED BY '<response>'
IGNORE 2 LINES
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
8

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

9

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

10

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