Domoticz chart2csv

[Copitano]

Hi everybody,
I export daily from my laptop from 12 different widget charts the (5 minute) data to separate .csv files. I then use the data to make analyzes in Excel.

During holidays I can also do this via VNC on my iPhone, but that is always very slow and demands a lot from the Pi3B+ on which domoticz runs, sometimes resulting in the pi crashing. So that doesn't really work.

Now I want to do that automatically every day with a dzVents script, where the data ends up in separate numbered .csv files and is stored on the pi in folder xxx. .csv's may therefore not be overwritten, but must be numbered consecutively.

Now I've looked at:viewtopic.php?t=30725 but that doesn't quite do what I want. The outputs are totals per day, so I would like to have 5 minutes of data per day. In addition, the output seems to be overwritten the next time the script runs. I manage to make simple dzVents scripts, but this one is just too complicated for me.

I'm testing on a Pi3b+ but eventually it will run on a Pi4B+ with 8GB of internal memory. On the Pi4B, Bullseye is installed on the 3B+ Buster. On both dzVents 3.1.8. On the 3B+ domoticz 2022.2 on the 4B+ 2023.1 both the stable version.

Who can help build the script to do what I'm trying to achieve? Maybe @waaren?

[waltervl]

@Waaren passed away unfortunately almost 2 years ago....
If you need something like this better setup an influxdb database and do the detailed analyses in there instead with Excel.
See wiki https://www.domoticz.com/wiki/Influxdb

[Copitano]

Oeps, I am so sorry. I did'nt know.

I had already thought about influxdb and grafana, but the analysis is part of an ongoing process in which the data is read into Excel using a macro and is added to the data that is already there. The simplest thing for me is to continue it that way.

But I will see what I can possibly get out of the influxdb. It is important that the format in the csv files is the same as the format that is downloaded via the widget. I think it will be mainly in the date-time format.

[gizmocuz]

Another option is to use the same API calls the Domoticz webinterface is using in the graphs?
Open the browsers developers console (F12), dock it to the bottom for easier reading.
Select the network tab, now navigate to the device graph
You will see a daily call, you can use this call to retrieve the data

[Copitano]

I will look into that tomorrow

[Copitano]

@gizmocuz
I do'nt see anything. What am I doing wrong?

[gizmocuz]

This is because there is no room to display, make it higher... and/or close the bottom console with the cross on the right (seen from the bottom)
Then refresh the page, or go to another view, clear the network log (circle with the stripe next to the record button) and navigate back to the log

[Copitano]

Then I'm presumably looking for this?

[Copitano]

@waltervl @ gizmocuz
In the meantime i managed to modify waaren's script viewtopic.php?p=259542#p259542 to my needs.
Here is the full modified script:

Code: Select all

--[[ -- sqliteDump.lua

        Dumps daily data as csv file

        requires sqlite3
            install command on linux:    sudo apt install sqlite3
            install command on openwrt:  opkg install sqlite3-cli
            install command on synology: sudo /opt/bin/opkg install sqlite3-cli

            History:
            20191231 first public release
            20200102 Add sudo chmod to make csvfile readable for all
            20201023 Add option to use MultiMeter
            20201026 Added option to get daily data, and included yesterdays' date to csv filename
]]--

return
{
    on =
    {
        timer =
        {
            --'at 00:04',
            'at 17:34',
        },
        devices =
        {
            'sqlDump',   -- test trigger can be ignored
        },
    },

    logging =
    {
        level = domoticz.LOG_DEBUG, -- set to domoticz.LOG_ERROR when all is OK
        marker = 'sqliteDump',
    },

    execute = function(dz)

    -- =======================  enter you settings below this line ==================

        local path = "/home/pi/domoticz/" -- full qualified path to your domoticz directory
        local database = 'domoticz.db'    -- database filename + extension
        local sqlite = '/usr/bin/sqlite3' -- location of your sqlite3 tool; check with 'which sqlite3'
        local csvFile = "/media/pi/KINGSTON/Pyranometer/Sunpower_Berkhout" --full path to the csv filename for the dump, without the .csv extension (will be added by script, together with date)

        -- set to '' for the types you don't need

	local allPercentages = '337,589' -- comma separated list of Percentage sensors to export

        -- =======================  No modification needed below this line ==================

        local csvFile = csvFile .. "_" .. os.date("%Y-%m-%d",os.time()-24*60*60) .. ".csv"
        local copy = 'safeCopy.db'
        local baseCommand = 'sudo ' .. sqlite .. ' -header -csv ' .. copy
        local closure     = ' >> '  .. csvFile
        local collects = {}


        --==Query for minute data
                                
        collects.Percentages =  'SELECT a.ID ID, a.Name Name, b.Percentage Percentage, b.Date Date '.. 
        			' FROM DeviceStatus a , Percentage b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allPercentages .. ')'        


        --==end query minute data

        local function osCommand(cmd)
            dz.log('Executing Command: ' .. cmd,dz.LOG_DEBUG)

            local fileHandle = assert(io.popen(cmd .. ' 2>&1 || echo ::ERROR::', 'r'))
            local commandOutput = assert(fileHandle:read('*a'))
            local returnTable = {fileHandle:close()}

            if commandOutput:find '::ERROR::' then     -- something went wrong
            dz.log('Error ==>> ' .. tostring(commandOutput:match('^(.*)%s+::ERROR::') or ' ... but no error message ' ) ,dz.LOG_DEBUG)
            else -- all is fine!!
                dz.log('ReturnCode: ' .. returnTable[3] .. '\ncommandOutput:\n' .. commandOutput, dz.LOG_DEBUG)
            end

            return commandOutput,returnTable[3] -- rc[3] contains returnCode

        end

        local function sqliteDump()
            local result, rc
            if dz.utils.fileExists(path .. database) then
                if dz.utils.fileExists(sqlite) then
                    result, rc  = osCommand('cp '  .. path .. database .. ' ' .. copy)
                    if rc ~= 0 then return rc, result end
                    result, rc  = osCommand('rm -f '  .. csvFile)
                    if rc ~= 0 then return rc, result end
                    for _, sql in pairs (collects) do
                        result, rc  = osCommand(baseCommand .. ' "' .. sql .. '" ' .. closure)
                        if rc ~= 0 then return rc, result end
                    end
                    result, rc  = osCommand('rm -f '  .. copy)
                    if rc ~= 0 then return rc, result end
                    result, rc  = osCommand('sudo chmod 777 '  .. csvFile)
                    if rc ~= 0 then return rc, result end
                else
                    return -1,'sqlite3 not installed / not found'
                end
            else
                return -1,'wrong path to database'
            end
            return 0, commandOutput
        end

        -- main program
        local rc, result = sqliteDump()
        if rc ~= 0 then dz.log(result,dz.LOG_ERROR) end
    end
}

@ gizmocuz
I'm still curious about your alternative solution. Am I on the right track with the picture above?
And how then?

[peterbos]

Hi,

I'm not exactly sure where you're looking for but maybe this will help you. Every hour I write my power and gas consumption to a csv-file on my Synology NAS. I have an Excel spreadsheet that imports the csv-file and creates the graphs.

Code: Select all

return {
	on = {
		timer = {
			'every hour', 
			} 
	},

	execute = function(dz, timer)

 	    local datum         = tostring(os.date('%d-%m-%Y', os.time()))
	    local uur           = tostring(os.date('%H', os.time()))
	    -- round consuption to 2 decimals
	    local gas = string.format("%.2f", dz.devices('Gas').counter)
	    gas = string.gsub(gas, '%.', ',')
	    local elektriciteit = string.format("%.2f", dz.devices('Elektriciteit').counter)
	    elektriciteit = string.gsub(elektriciteit, '%.', ',')

	    local tekst = datum ..';' .. uur .. ';' .. gas .. ';' .. elektriciteit .. '\n'
            -- at 24:00/0:00 write data for 24:00 (the day before) and 0:00
            if uur == '00' then
            datum = tostring(os.date('%d-%m-%Y', os.time()-60*60)) 
	        tekst = datum ..';24;' .. gas .. ';' .. elektriciteit .. '\n' .. tekst
            end

            -- write data to csv-file
	    file = io.open("/mnt/documents/Domoticz/uurverbruik.csv", "a+")
	    file:write(tekst)
            file:close()
    end
}

Peter

[Copitano]

Hi,

I'm not exactly sure where you're looking for but maybe this will help you. Every hour I write my power and gas consumption to a csv-file on my Synology NAS. I have an Excel spreadsheet that imports the csv-file and creates the graphs.

Code: Select all

return {
	on = {
		timer = {
			'every hour', 
			} 
	},

	execute = function(dz, timer)

 	    local datum         = tostring(os.date('%d-%m-%Y', os.time()))
	    local uur           = tostring(os.date('%H', os.time()))
	    -- round consuption to 2 decimals
	    local gas = string.format("%.2f", dz.devices('Gas').counter)
	    gas = string.gsub(gas, '%.', ',')
	    local elektriciteit = string.format("%.2f", dz.devices('Elektriciteit').counter)
	    elektriciteit = string.gsub(elektriciteit, '%.', ',')

	    local tekst = datum ..';' .. uur .. ';' .. gas .. ';' .. elektriciteit .. '\n'
            -- at 24:00/0:00 write data for 24:00 (the day before) and 0:00
            if uur == '00' then
            datum = tostring(os.date('%d-%m-%Y', os.time()-60*60)) 
	        tekst = datum ..';24;' .. gas .. ';' .. elektriciteit .. '\n' .. tekst
            end

            -- write data to csv-file
	    file = io.open("/mnt/documents/Domoticz/uurverbruik.csv", "a+")
	    file:write(tekst)
            file:close()
    end
}

Peter

Hi Peter,
I'm definitely going to watch it, if only to learn something from it.
The script from my last post works for me. I also read the .csv's with a macro in the Excel file. But always be open to better/faster solutions.

By the way, do you know if it is possible to repeat the same action within the same dzVents script by copying and pasting the first one under it and then renaming the .csv file to produce a sencond .csv file etc..
I have 12 sensors that I want in 12 separate .csv files. Of course I can just make 12 separate dzVents scripts, but one might be more efficient.