No data from sbfspot to Domoticz

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

[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

[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

[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

[bontwoody]

Virtualdevice.png (108.83 KiB) Viewed 3136 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

[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()

[Twoink]

Hi Mark,

I'm having the exact same situation. Did you solve this issue or do you still use your own python script?

[bontwoody]

Hi. I’m still using the python script. To be honest I haven’t tried to use the original method again

[Twoink]

Thanks for your answer. Maybe its good to inform this as a bug to domoticz then

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

[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

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

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

[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!!

[bontwoody]

Just to prove its worth the effort

[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|

[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 2506 times

Devices.jpg (177.17 KiB) Viewed 2506 times

[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?

[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

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