SQLite, shell script, CSV, google Spreadsheet, Ducksboard

Posted on
Wed Aug 28, 2013 2:20 pm
henkjanvries offline
User avatar
Posts: 108
Joined: May 05, 2012

SQLite, shell script, CSV, google Spreadsheet, Ducksboard

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!

Posted on
Wed Aug 28, 2013 4:02 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

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.

Image

Posted on
Wed Aug 28, 2013 5:08 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

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

Posted on
Sun Sep 01, 2013 2:31 am
henkjanvries offline
User avatar
Posts: 108
Joined: May 05, 2012

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

could you send the script? im really interested in how to do that

Posted on
Sun Sep 01, 2013 7:39 am
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

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



Posted on
Sun Sep 01, 2013 9:31 pm
hamw offline
Posts: 1212
Joined: Mar 31, 2008

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

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?

Posted on
Sun Sep 01, 2013 9:53 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

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

Posted on
Wed Jul 16, 2014 12:16 pm
mrosenblatt offline
Posts: 2
Joined: Apr 09, 2013

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

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

Posted on
Wed Jul 16, 2014 10:02 pm
kw123 offline
User avatar
Posts: 8333
Joined: May 12, 2013
Location: Dallas, TX

Re: SQLite, shell script, CSV, google Spreadsheet, Ducksboar

welcome..

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest