Hello:
OH2.4M8 in a docker container
Ubuntu 18.0.4 Server
I am using rrdj4 persistences for my Novelan heatpump. Displaying the values in graphs and so on is working. For further calculations which I want to do in Excel I would need to have the values exported to an Excel compatible form (CSV etc)
All my research is always leading to hints to use persistence instead of an excel export. But this is in my case not a solution.
Thanks for any help which is highly appreciated
You can try to use the rest api to read the rrd4j database. But not all data is saved, it will be compress. Maybe this Thread can give you a hint.
Hmm ok …
Is this addon somewhere on github? Maybe I can get an idea how to extract data from heatpump and do not take the detour through rrdj4 …
You can install the REST API docs from the Misc tab in PaoerUI. From there you can query rrd4j which will give you the values in JSON format. From there you will need to write your own script to convert the JSON to CSV. There is no CSV export built into OH or rrd4j.
Thanks rlkoshak
so far the best hint (at least for me)
Is it possible to ask more than one itemnames at once in one request?
In which format is the time displayed in json output?
Thanks rlkoshak,
after some hours of coding I wrote a python script which is doing the job!
Thanks
Hi @Homer-Sim,
It would be nice if you could share your script as I’m looking for a solution to export power consumption data to CSV.
Sorry for my late replay
#!/usr/bin/python3
### Reihenfolge der Daten:
### Datum / Aussentemperatur / Erdkollektortemperatur / Heizenergie / Warmwasserenergie /
import json
import csv
import pandas
from datetime import date, timedelta
from urllib.request import urlopen
import os
heute = date.today()
gestern = date.today()-timedelta(days=1)
all_filenames = []
jahr = gestern.year
if ( gestern.month < 10 ):
monat = "0" + str(gestern.month)
else:
monat = gestern.month
folderadd = str(jahr) + "-" + str(monat) +"/"
### URLs für die Heizdaten
URL1 = ["Aussentemp", "http://192.168.100.11:8060/rest/persistence/items/HeatPump_Temperature_1?starttime="]
URL2 = ["Erdkollektortemp", "http://192.168.100.11:8060/rest/persistence/items/HeatPump_Probe_in?starttime="]
URL3 = ["Heizenergie", "http://192.168.100.11:8060/rest/persistence/items/HeatPump_Thermalenergy_Heating?starttime="]
URL4 = ["WWenergie", "http://192.168.100.11:8060/rest/persistence/items/HeatPump_Thermalenergy_Warmwater?starttime="]
URL5 = ["Gesamtstrom", "http://192.168.100.11:8060/rest/persistence/items/HeizstromGesamt?starttime="]
addend = str(gestern) + '&endtime=' + str(heute)
URLS = [URL1[0], URL1[1]+addend, URL2[0], URL2[1]+addend, URL3[0], URL3[1]+addend, URL4[0], URL4[1]+addend, URL5[0], URL5[1]+addend]
TEMP_PATH = "/home/homer/heizungsloggen/temp/"
SAVE_PATH = "/home/homer/heizungsloggen/logs/" + folderadd
SAFETY_PATH = "/media/daten/DOKUMENTE/LEITZORDNER/HAUS/00Heizungslogs/" + folderadd
def read_json(url):
data = json.loads(urlopen(url).read())
# print(data)
return data
def write_csv(data,filename):
all_filenames.append(filename)
pandas.io.json.json_normalize(pandas.read_json(json.dumps(data))['data']).to_csv(filename)
return all_filenames
def main():
try:
for i in range(0,9,2):
write_csv(read_json(URLS[i+1]), TEMP_PATH+str(gestern)+'_'+URLS[i]+'.csv')
except:
exit()
if __name__ == '__main__':
main()
It can be that some imports are obsolete because I deleted some unimportant code parts
Hi @Homer-Sim,
Thanks for sharing your script.
Could you tell me where you are calling it from or how it can be referenced? From a personal widget?
I would also be interested to know if this visually shows the .csv downloaded through the browser or if instead it is generated in a file within the server.
Thank you very much, greetings.
As far as I read the script it can be called standalone from the command line.
It would be possible to call it by using the exec binding or executecommandLine.
The extracted values are saved to a file ( call to write_csv ).