Page 1 of 1
SQLite, shell script, CSV, google Spreadsheet, Ducksboard
Posted:
Wed Aug 28, 2013 2:20 pm
by henkjanvries
Ive been trying to grab data from the sqlite db, export it to a csv file, than automagically import it to a google spreadsheet. From there use that info to fill a Ducksboard dashboard.
And ive actually done it!
but now my issue.
I have 5 temperature sensors. All installed and connected to rfxcom at the same time.
But for some reason the output of all 5 is different. The amount of rows put into the sqlite is not the same.
some have 50 records, anther just 10.
But all last records have similar timestamps.
and another thing thats weird is that the timestamp column is 2 hours earlier than the lastupdated column.
having all sensors input data into ducksboard and therefor have a nice chart with 5 lines only works if all data is the same (at least the amount of rows)
if anyone can help all data is available.
thanks!
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Wed Aug 28, 2013 4:02 pm
by matt (support)
Unfortunately I don't think the rows are going to match. The RFXCOM sensors I have seen have a moderate amount of variance in their broadcast durations, and Indigo only writes data to the SQLite database if the state (temperature) actually changes.
Ducksboard looks interesting. I've wanted a good configurable dashboard visualizer.
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Wed Aug 28, 2013 5:08 pm
by kw123
I have a script running that appends to a csv file every 2 minutes with
- all temperatures (internal + external) , humidity, a/c heat, fan , energy used KWH, KW etc
Then you can use either excel or .. what ever you like.
The parameters might have not been updated, but at least the time "column" is the same for all measurements, makes live easier.
Karl
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Sun Sep 01, 2013 2:31 am
by henkjanvries
could you send the script? im really interested in how to do that
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Sun Sep 01, 2013 7:39 am
by kw123
here you go, see attached applescript.
Karl
- Code: Select all
(*
write all info on temperatures, humidity, AC/(Heat to be added) on off, Fan on/off, etc to comma delimited file, to be used by excel in ie "xy plot"
V1.0
Karl Wachs
info per thermostat:
- set temp, actual tem fan on/off ac on/off, heat on / off for 4 thermostats
info from other temp sensors tem and hunidity
info from NOA on current humidity
AC on off is written as 0/100 so that in excel it can use the same scale as temperature (inside 65-80 outside 20-105)
call on a fixed schedule ie 2 or 5 minutes, to have equal bin size. makes plotting easier
all info has to be in variables:
as part of the scheduled action, copy all info from thermostat etc into variables
*)
-- get date string
set rightNow to (current date)
set theTime to text -8 thru -1 of ((current date) as «class isot» as string)
set YR to ((year of rightNow) as text)
set MO to ((month of rightNow) as integer)
if MO < 10 then set MO to "0" & MO as text
set MO to MO as text
set DA to ((day of rightNow) as integer)
if DA < 10 then set DA to "0" & DA as text
set DA to DA as text
set rightNow to YR & "/" & MO & "/" & DA & " " & theTime
set rightNow to rightNow as text -- YYYY/MM/DD HH:MM:SS in 24 hour mode
-- setup output file
set outputfile to "energyused.csv"
--create headerline if called first time
set myPath to "/Users/karlwachs/Documents/scripts/" & outputfile
set create to "yes"
try
POSIX file myPath as alias
set create to "no"
end try
if create = "yes" then
set outcmd to "echo \"YYYY/MM/DD HH:MM:SS, AccumKWH, KW, ACU, ACD, ACE, ACG, TempU, TempD, TempE, TempG,fanU, FanD, FanE, fanG, ACsetU, ACsetD, ACsetE, ACsetG, TempU2, TempD2, HumU, HumD, TempOut, HumOut \" > " & myPath
do shell script outcmd
end if
-- get variables
using terms from application "IndigoServer"
tell application "IndigoServer"
-- ac on / off
set ACU to (get value of variable "ThermostatUpstairsAC") as text
set ACD to (get value of variable "ThermostatDownstairsAC") as text
set ACE to (get value of variable "ThermostatExerciseRoomAC") as text
set ACG to (get value of variable "ThermostatGameRoomAC") as text
if ACU = "true" then
set ACU to 100
else
set ACU to 0
end if
if ACD = "true" then
set ACD to 100
else
set ACD to 0
end if
if ACE = "true" then
set ACE to 100
else
set ACE to 0
end if
if ACG = "true" then
set ACG to 100
else
set ACG to 0
end if
-- fan on / off
set fanU to (get value of variable "ThermostatUpstairsFan") as text
set fanD to (get value of variable "ThermostatDownstairsFan") as text
set fanE to (get value of variable "ThermostatExerciseRoomFan") as text
set fanG to (get value of variable "ThermostatGameRoomFan") as text
if fanU = "true" then
set fanU to 100
else
set fanU to 0
end if
if fanD = "true" then
set fanD to 100
else
set fanD to 0
end if
if fanE = "true" then
set fanE to 100
else
set fanE to 0
end if
if fanG = "true" then
set fanG to 100
else
set fanG to 0
end if
-- current actual temps
set tempU to (get value of variable "ThermostatUpstairsTemp") as text
set tempD to (get value of variable "ThermostatDownstairsTemp") as text
set tempE to (get value of variable "ThermostatExerciseRoomTemp") as text
set tempG to (get value of variable "ThermostatGameRoomTemp") as text
-- current setpoints
set ACsetU to (get value of variable "ThermostatUpstairsACSetPoint") as text
set ACsetD to (get value of variable "ThermostatDownstairsACSetPoint") as text
set ACsetE to (get value of variable "ThermostatExerciseRoomACSetPoint") as text
set ACsetG to (get value of variable "ThermostatGameRoomACSetPoint") as text
-- temps and humidity from additional sensors
set tempD2 to (get value of variable "Stanford_Inside_Temperature_Downstairs") as text
set tempU2 to (get value of variable "Stanford_Inside_Temperature_Upstairs") as text
set humD to (get value of variable "Stanford_Inside_Humidity_Downstairs") as text
set humU to (get value of variable "Stanford_Inside_Humidity_Upstairs") as text
set tempOut to (get value of variable "Stanford_outside_Temperature") as text
set humOut to (get value of variable "NOA_loveField_humidity") as text
-- accumulated KWH
set KWH to (get value of variable "KWH") as text
-- current KW
set KW to (get value of variable "KW") as text
end tell
end using terms from
-- now append info t csv file
set writeText to rightNow & ", " & KWH & ", " & KW & ", " & ACU & ", " & ACD & ", " & ACE & ", " & ACG & ", " & tempU & ", " & tempD & ", " & tempE & ", " & tempG & ", " & fanU & ", " & fanD & ", " & fanE & ", " & fanG & ", " & ACsetU & ", " & ACsetD & ", " & ACsetE & ", " & ACsetG & ", " & tempU2 & ", " & tempD2 & ", " & humU & ", " & humD & ", " & tempOut & ", " & humOut
set outcmd to "echo \" " & writeText & "\" >> " & myPath
do shell script outcmd
return true
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Sun Sep 01, 2013 9:31 pm
by hamw
That looks pretty neat. What would one have to change in the script to adapt it to a different system? Obviously it would be convenient to change the names, but does the POSIX path need to change or other stuff?
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Sun Sep 01, 2013 9:53 pm
by kw123
you have to
1. change the path of the file to where ever you want to put the file, change:
set myPath to "/Users/your userid here/Documents/" & output file
2. save the file applescript file to ....indigo 6/scripts/attachments as app (export from applescript editor as app)
in indigo reload attachments under PLUGINS
3. define/create the variables as you need
4. setup scheduled action with actions:
- update variables from device1
- update variables from device2
..
- execute script as file after delay of 1 sec to make sure that the variables are updated.
let me know if you need more info
Karl
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Wed Jul 16, 2014 12:16 pm
by mrosenblatt
Karl,
Just wanted to let you know I found this thread today, and used your script - was exactly what I was looking for.
I couldn't get it to run as a app, kept giving an alert box "Application Not Running", but when I embedded it in the Indigo schedule it worked fine.
Great work. Thank you for contributing.
Michael
Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar
Posted:
Wed Jul 16, 2014 10:02 pm
by kw123
welcome..