Page 1 of 2
No data from sbfspot to Domoticz
Posted: Saturday 02 November 2019 9:08
by ravenrider
Hey,
I am new to raspberri pi and domoticz. Last night I managed to get sbfpost running and it is updating it's database and also creating CSV files.
However in domoticz I can add SBFpost he doesn't read the data?
Any help will be appreciated
Some screenshots off my settings:
Location data
location config file sbfspot
Settings Domoticz
No data
About Domoticz
Version: 4.10717
Build Hash: b38b49e5
Compile Date: 2019-05-09 13:04:08
dzVents Version: 2.4.19
Python Version: 3.7.3 (default, Apr 3 2019, 05:39:12) [GCC 8.2.0]
Also I noticed a bug.
When I typed a directory instead of a file for the location in Domoticz. Then Domoticz crashed and couldn't start anymore.
The only way I could restart Domoticz was by deleting the domoticz folder, the files in the Etc folder and then reinstalling.
Re: No data from sbfspot to Domoticz
Posted: Tuesday 19 November 2019 17:46
by bontwoody
I am having exactly the same issues as you describe. There is some information in the forum about changing the spot file names in the SBFspot.cfg file but I havent managed to get anything to work yet have you?
Cheers Mark
Re: No data from sbfspot to Domoticz
Posted: Tuesday 19 November 2019 20:22
by Pjedr
My config is still working, maybe it helps you, comparing settings.
Code: Select all
# ____ ____ _____ _
# / ___|| __ )| ___|__ _ __ ___ | |_
# \___ \| _ \| |_ / __| '_ \ / _ \| __|
# ___) | |_) | _|\__ \ |_) | (_) | |_
# |____/|____/|_| |___/ .__/ \___/ \__|
# |_|
...
# OutputPath (Place to store CSV files)
#
# Windows: C:\Users\Public\SMAdata\%Y
# Linux : /home/pi/smadata/%Y
# %Y %m and %d will be expanded to Year Month and Day
OutputPath=/mnt/WD_Elements_4TB/peter/smadata/%Y
# OutputPathEvents (Place to store CSV files for events)
# If omitted, OutputPath is used
OutputPathEvents=/mnt/WD_Elements_4TB/peter/smadata/%Y/Events
# Position of pv-plant http://itouchmap.com/latlong.html
# Example for Ukkel, Belgium
Latitude=53.20
Longitude=6.12
# Calculate Missing SpotValues
# If set to 1, values not provided by inverter will be calculated
# eg: Pdc1 = Idc1 * Udc1
CalculateMissingSpotValues=1
# DateTimeFormat (default %d/%m/%Y %H:%M:%S)
# For details see strftime() function
# http://www.cplusplus.com/reference/clibrary/ctime/strftime/
DateTimeFormat=%d/%m/%Y %H:%M:%S
# DateFormat (default %d/%m/%Y)
DateFormat=%d/%m/%Y
# DecimalPoint (comma/point default comma)
DecimalPoint=comma
# TimeFormat (default %H:%M:%S)
TimeFormat=%H:%M:%S
# SynchTime (0-30 - 0=disabled, 1=once a day (default), 7=once a week, 30=once a month)
# If set to non-zero value, the plant time is synchronised with local host time
# Some inverters don't have a real-time clock
SynchTime=0
# SynchTimeLow (1-120 - default 1)
# SynchTimeHigh (1200-3600 - default 3600)
# Plant time is adjusted to local host time when SynchTime=1 and
# time difference is between SynchTimeLow and SynchTimeHigh limits
SynchTimeLow=1
SynchTimeHigh=3600
# SunRSOffset
# Offset to start before sunrise and end after sunset (0-3600 - default 900 seconds)
SunRSOffset=900
# Locale
# Translate Entries in CSV files
# Supported locales: de-DE;en-US;fr-FR;nl-NL;es-ES;it-IT
# Default en-US
Locale=nl-NL
# Timezone
# Select the right timezone in date_time_zonespec.csv
# e.g. Timezone=Europe/Brussels
Timezone=Europe/Amsterdam
# BTConnectRetries
# Number of Bluetooth Connection attempts (1-15; Default=10)
BTConnectRetries=10
###########################
### CSV Export Settings ###
###########################
# With CSV_* settings you can define the CSV file format
# CSV_Export (default 1 = Enabled)
# Enables or disables the CSV Export functionality
CSV_Export=1
# CSV_ExtendedHeader (default 1 = On)
# Enables or disables the SMA extended header info (8 lines)
# isep=;
# Version CSV1|Tool SBFspot|Linebreaks CR/LF|Delimiter semicolon|Decimalpoint comma|Precision 3
# etc...
# This is usefull for manual data upload to pvoutput.org
CSV_ExtendedHeader=1
# CSV_Header (default 1 = On)
# Enables or disables the CSV data header info (1 line)
# dd/MM/yyyy HH:mm:ss;kWh;kW
# This is usefull for manual data upload to pvoutput.org
# If CSV_ExtendedHeader is enabled, CSV_Header is also enabled
CSV_Header=1
# CSV_SaveZeroPower (default 1 = On)
# When enabled, daily csv files contain all data from 00:00 to 23:55
# This is usefull for manual data upload to pvoutput.org
CSV_SaveZeroPower=1
# CSV_Delimiter (comma/semicolon default semicolon)
CSV_Delimiter=semicolon
Re: No data from sbfspot to Domoticz
Posted: Wednesday 20 November 2019 15:07
by bontwoody
Your config file is similar to mine. The naming issue is mentioned at the bottom of:
https://www.domoticz.com/wiki/SMASpot_M ... n_Domoticz
"Internally, domoticz relies on SBFspot to generate logfiles in the spot format. It is therefore important that SBFspot is kept running in the background (e.g. as cronjob). See SBFspot.cpp for details.
Search for 'OutputPath', 'Plantname', 'DateFormat', and 'TimeFormat' in the config file
Build output path filename from given config strings (sprintf(szLogFile, "%s%s-Spot-%s.csv", strftime_t(m_SBFDataPath.c_str(), atime), m_SBFPlantName.c_str(), szDateStr);)"
Im not quite sure what this means, I tried changing the Output path in my file from:
OutputPath=/mnt/WD_Elements_4TB/peter/smadata/%Y
to:
OutputPath=/mnt/WD_Elements_4TB/peter/smadata/(sprintf(szLogFile, "%s%s-Spot-%s.csv", strftime_t(m_SBFDataPath.c_str(), atime), m_SBFPlantName.c_str(), szDateStr);)
but it just prodiced some wierdly named folders.
I did try the setup option on the SBFspot hardware in Domotitcz for a second time and this time it did load some historical data in for last week and last month but the last year data was just the same as the last month data. These are nicely graphed in the Log section of the device. However no Day data is shown and the Data line in the device tab just stays subbornly at 0.000kWh. The fact that the historical data was loaded however makes me think that the format of the data files must be ok.
Its very frustrating to be so close. Im going to play around a bit more this pm but the worst case scenario I think is to use a python script to directly take the spot values from the SBFspot db and put them in the Domoticz db via a virtual device. Ill let you know if I make any progress.
Mark
Re: No data from sbfspot to Domoticz
Posted: Friday 22 November 2019 18:04
by bontwoody

- Virtualdevice.png (108.83 KiB) Viewed 3276 times
OK, so I havent been able to get the inbuilt import function to work properly but I have managed to use a virtual sensor and a python script to do what is required. There are a couple of glitches with the data in the image but Im very close. Im still fiddling with it to get the best results but Im happy to share how I did it with you if you want. Basically I an querying the SBFspot database directly and then inserting the values into the Domoticz database through the python code which is run every 5 minutes via crontab.
Regards Mark
Re: No data from sbfspot to Domoticz
Posted: Sunday 01 December 2019 17:45
by bontwoody
Just for anyone else with the same issue Im going to share the finished python code below. its been working fine for several days now. Just run the python script from crontab every 5 minutes with the relevent paths changed for your system.
#!/usr/bin/python
from sqlite3 import connect
from datetime import datetime
conn = connect('/home/pi/smadata/SBFspot.db')
curs = conn.cursor()
#Get last 20 rows of day file for latest reading of solar output. get more than 1 in case poor bluetooth connection
curs.execute("select Timestamp, Power, TotalYield from vwDayData ORDER BY Timestamp DESC LIMIT 20")
rows1=curs.fetchall()
curs.close
conn.close
conn = connect('/home/pi/domoticz/domoticz.db')
curs = conn.cursor()
#get last reading added to domoticz
curs.execute("select Date from Meter ORDER BY Date DESC LIMIT 1")
rows2=curs.fetchall()
#get last timestamp added to domoticz
print(rows2)
DT=rows2[0][0]
a=0 # variable for flag to append date - 0=append, 1=dont append
for n in range(19):
if DT!=rows1[n][0] and a==0:#append data
#print("append", DT, rows1[n][0],a, rows1[n][1], rows1[n][2])
val=rows1[n][1]
tot=rows1[n][2]
params = (11,val*10,tot,rows1[n][0])
curs.execute("INSERT INTO Meter(DeviceRowID,Usage,Value,Date) VALUES (?,?,?,?)", params)
conn.commit()
elif DT==rows1[n][0] and a==0: #set 'dont append'flag
#print("set flag", DT, rows1[n][0],a)
a=1
else:
print("do nothing", DT, rows1[n][0],a)
curs.close()
conn.close()
Re: No data from sbfspot to Domoticz
Posted: Tuesday 09 June 2020 8:18
by Twoink
Hi Mark,
I'm having the exact same situation. Did you solve this issue or do you still use your own python script?
Re: No data from sbfspot to Domoticz
Posted: Tuesday 09 June 2020 10:49
by bontwoody
Hi. I’m still using the python script. To be honest I haven’t tried to use the original method again
Re: No data from sbfspot to Domoticz
Posted: Tuesday 09 June 2020 11:30
by Twoink
Thanks for your answer. Maybe its good to inform this as a bug to domoticz then
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 10:14
by Mithrandir
It seems i have the same issue.. SBFspot is working and provides correct input. In Domoticz I added the "hardware" pointing to the right location of the cfg file.. but nothing happens. The sensor says last seen on the moment of adding SBFspot (SMA).
So, how does your python script work? I saved it as SMA.py in domoticz/scripts/ but dont know the next step.
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 13:43
by bontwoody
You can run the script at regular time intervals from crontab. Type crontab -e to start editing the crontab file. There are loads of tutorials about crontab on the net. Mine looks like the one below.
## run SBFspot jobs
*/5 6-22 * * * /usr/local/bin/sbfspot.3/daydata
55 05 * * * /usr/local/bin/sbfspot.3/monthdata
## Run temperature measurement evry 5 min
*/5 * * * * /home/pi/PythonScripts/ReadTemp.py
## Read solar output every 5 mins
*/5 6-22 * * * /usr/bin/python /home/pi/PythonScripts/ReadSolar6.py
## reboot at 1am
0 01 * * * reboot
##restart domoticz service at 5am
00 05 * * * sudo service domoticz.sh restart >> restartdom.txt 2>&1
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 15:01
by Mithrandir
sorry, that was not clear from my post. I know crontab but I am not sure how to add this to domoticz as virtual sensor.
I found the dummy, but still not sure how to get the results in my dashboard.
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 15:27
by bontwoody
Right. Its been a long time since I did that and cant remember exactly. Ive had a play and first you go to setup-hardware and create a new hardware device. I think the type was important. Ill have a look in the actual Domotitcz database table and see what the settings are and get back to you.
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 15:49
by bontwoody
Right. I think I see what I did. Its easier if you can directly read the domotictz database. I use DB Browser for SQLite for that. To get the right display units, the device I created had a Hardware ID of 7, Type 243 and Subtype 29. These are shown in the DeviceStatus Table of the domoticz database. When you create a vitual sensor using the dashboard its details are stored there. You can even alter them directly in the table. The virtual device I created had an ID of 11 and its this value I used in my python script when inserting the solar readings into Meter table in the Domoticz db. The part of the python script that does this is:
params = (11,val*10,tot,rows1[n][0])
curs.execute("INSERT INTO Meter(DeviceRowID,Usage,Value,Date) VALUES (?,?,?,?)", params)
In your script just change the 11 for the ID of your virtual sensor and then the data is stored against its ID and the dashboard should plot it. Easy eh!!

Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 15:59
by bontwoody
Just to prove its worth the effort

Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 16:52
by Mithrandir
would it be possible to check the name of the hardware in your domoticz setup/hardware? my domoticz is on a RPi zero, so no graphical interface for DB browser available. and within sqlite3 commandline it's a bit difficult to add it like this.
So it would be really helpful if you could check in hardware for the name and within devices for the right virtual sensor. The rest is pretty clear I guess.
thanks!
ps double check 'DummySensor'.. but output in the database is
4|DummySensor|1|15||0|||||0|0|0|0|0|0|0|
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 18:37
by bontwoody
Im not sure this is going to help but ill screenshot what I have. If you need anything else feel free to ask

- Hardware.jpg (146.19 KiB) Viewed 2646 times

- Devices.jpg (177.17 KiB) Viewed 2646 times
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 20:29
by Mithrandir
Yes, almost ready... thanks for the support! For future reference it’s the ‘electric (instant+counter)‘
But now it seems the python script has an error. When I run it from commandline it produces the following error:
File "SMApython.py", line 29
if DT!=rows1[n][0] and a==0: #append data
^
IndentationError: expected an indented block
Any thoughts?
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 20:43
by bontwoody
I knew it was a particular type
Most likely it is just a tab that has gotten in the wrong place on line 29. Could be just a space next to it (hard to spot). If you could screenshot the code I might be able to spot it for you. The line under "if DT!=rows1[n][0] and a==0: #append data" should have a single tab indent
Re: No data from sbfspot to Domoticz
Posted: Friday 23 October 2020 20:55
by bontwoody
Just spotted that all the tabs have become misplaced in the code I posted. I will repost a screenshot of my updated code so you can see where the tabs should be.