Export data from DB to Excel [SOLVED]

[waaren]

but is there a way to change the table to form like this?

It is possible but I guess there are better ways to do this then using Lua/ dzVents.
In Lua / dzVents it would require reading the file after creation and load it into a table / convert it to the requested format and write it back.

[waaren]

but is there a way to change the table to form like this?

One way to do this in dzVents / Lua would be

Code: Select all

return
{
    on =
    {
        devices =
        {
            'convertTrigger', -- change to timer or other trigger
        }
    },
    logging = {
        level = domoticz.LOG_DEBUG, -- change to domoticz.ERROR when OK
        marker = 'convert',
    },

    execute = function(dz)

        local csvIn = '/tmp/in.csv' -- full qualified filename
        local csvOut = '/tmp/out.csv' -- full qualified filename

        local function readFile2Table(f)
            line, lines = {}, {}
            local rowCounter = 1
            for row in io.lines(f) do
                line = dz.utils.stringSplit(row, '|')
                if rowCounter == 1 then
                    lines[1] = {}
                    lines[1] = { line[1], line[2] }
                elseif rowCounter == 3 then
                    lines[2] = {}
                    lines[2] = { line[1], line[2], line[3] }
                    table.insert(lines[1], line[4])
                elseif rowCounter > 3 then
                    table.insert(lines[2], line[3])
                    table.insert(lines[1], line[4])
                end
                rowCounter = rowCounter + 1
            end
            return lines
        end

        local function write2File(f, t)
            file = io.open(f, "w")
            file:write( table.concat(t[1],'|') .. "\n\n")
            file:write( table.concat(t[2],'|') )
        end

        csvTable = readFile2Table(csvIn)
        
        -- dz.utils.dumpTable(csvTable) -- debug only
        local myHeader = table.concat(csvTable[1],'|')
        local myData = table.concat(csvTable[2],'|')

        dz.log(myHeader, dz.LOG_DEBUG)
        dz.log(myData, dz.LOG_DEBUG)

        write2File(csvOut, csvTable)

    end
}

[tukiplus]

but is there a way to change the table to form like this?

One way to do this in dzVents / Lua would be

Code: Select all

return
{
    on =
    {
        devices =
        {
            'convertTrigger', -- change to timer or other trigger
        }
    },
    logging = {
        level = domoticz.LOG_DEBUG, -- change to domoticz.ERROR when OK
        marker = 'convert',
    },

    execute = function(dz)

        local csvIn = '/tmp/in.csv' -- full qualified filename
        local csvOut = '/tmp/out.csv' -- full qualified filename

        local function readFile2Table(f)
            line, lines = {}, {}
            local rowCounter = 1
            for row in io.lines(f) do
                line = dz.utils.stringSplit(row, '|')
                if rowCounter == 1 then
                    lines[1] = {}
                    lines[1] = { line[1], line[2] }
                elseif rowCounter == 3 then
                    lines[2] = {}
                    lines[2] = { line[1], line[2], line[3] }
                    table.insert(lines[1], line[4])
                elseif rowCounter > 3 then
                    table.insert(lines[2], line[3])
                    table.insert(lines[1], line[4])
                end
                rowCounter = rowCounter + 1
            end
            return lines
        end

        local function write2File(f, t)
            file = io.open(f, "w")
            file:write( table.concat(t[1],'|') .. "\n\n")
            file:write( table.concat(t[2],'|') )
        end

        csvTable = readFile2Table(csvIn)
        
        -- dz.utils.dumpTable(csvTable) -- debug only
        local myHeader = table.concat(csvTable[1],'|')
        local myData = table.concat(csvTable[2],'|')

        dz.log(myHeader, dz.LOG_DEBUG)
        dz.log(myData, dz.LOG_DEBUG)

        write2File(csvOut, csvTable)

    end
}

thanx for your quick respon, i'll try it as soon and report the result

[Jerby]

Great work, thanks!
I have this script running for some month but like to add switch data as well. Is that easy to do?

[BartSr]

Hello,
Anyone who can show me how to change the queries in way that data is sent to the CSV-file for monday / friday values of 7.00 in the morning and 19.00 in the evening only?
My goal is to get an overview of gasconsumption every day of the week (except sat and sun) between 7 oclock and 19 oclock
Thanks,
Bart

[waltervl]

Hello,
Anyone who can show me how to change the queries in way that data is sent to the CSV-file for monday / friday values of 7.00 in the morning and 19.00 in the evening only?
My goal is to get an overview of gasconsumption every day of the week (except sat and sun) between 7 oclock and 19 oclock
Thanks,
Bart

This is not possible for history data as Domoticz stores the history data ( eg for week/month/year graphs) only as a summary per day, not per hour or minute.
You can only use the data from the short log for this.
And then you will have to modify the below lines and add the time.

Code: Select all

       --==Query for minute data
        collects.meters =   'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Usage Usage, b.Date Date '.. 
        
                            ' FROM DeviceStatus a , Meter b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ')'

[BartSr]

Waltevl,

Sorry to say but currently I get a csv file showing values each 5 minutes (timedatestamp). So in my opnion this certainly should be possible. Maybe I didnot expressed clear enough as a weekly report would be OK.

Regards,
-Bart

[HvdW]

The owner is root and normaly I am pi

Quote of the year!

[waltervl]

Waltevl,

Sorry to say but currently I get a csv file showing values each 5 minutes (timedatestamp). So in my opnion this certainly should be possible. Maybe I didnot expressed clear enough as a weekly report would be OK.

Regards,
-Bart

I did not say anything different, it is possible but only for the short log which is maximum 1 week if your short log setting is 7 days.

[Huib]

Hi all, I would like to get the P1 information only per month. How can I change the script to get the data per month instead of per day and without the history of all way back? Thanks for the help!

[waltervl]

Hi all, I would like to get the P1 information only per month. How can I change the script to get the data per month instead of per day and without the history of all way back? Thanks for the help!

If you need it only once a month you can better use the export to Excel on the monthly report graph. https://www.domoticz.com/wiki/Managing_ ... graph_data

[FlyingDomotic]

As Domoticz don't keep detailed values over mid nor long period, I added a small script in order to save each change in Domoticz log. Then, I pase them with a Python script to save them in a csv file. I can then select these daily changes in order to restrict to a(some) specific(s) device(s), and do a summary by <whatever you want>.
Here's the script:

Code: Select all

-- Trace device changes to Domoticz's log
commandArray = {}
-- loop through all changed devices
for deviceName,deviceValue in pairs(devicechanged) do
    if otherdevices[deviceName] ~= nil then
        print ("Device based event fired on '"..deviceName.."', value '"..tostring(deviceValue).."'");
    end
end
return commandArray

I've got the same for variables.
Here's the python script extracting Domoticz log file:

Code: Select all

#!/usr/bin/python3
#   Extract device/variable changes from Domoticz log file V1.0.0
#

import glob
import datetime
import pathlib
import getopt
import sys
import os

# Extract device/variable changes from Domoticz log file
def extractChanges(logFile):
    fileEncoding = 'ISO-8859-1'
    # Open export file, read data and extract it
    print('Reading '+logFile+' encoded as '+fileEncoding)
    inp = open(logFile, 'rt', encoding=fileEncoding, errors='ignore')

    for line in inp.readlines():
        pos = line.find('based event fired on')		# Must be the same as in device/variable login script
        if pos >= 0:
            items = line.split("'")
            if selectedDevices:
                found = items[1] in selectedDevices
            else:
                found = True
            if found:
                out.write(line[:24]+'\t'+items[1]+'\t'+items[3]+'\n')
    inp.close()

#   *** Main code ***
# Set current working directory to this python file folder
currentPath = pathlib.Path(__file__).parent.resolve()
os.chdir(currentPath)
# Get command file name (without suffix)
currentFileName = pathlib.Path(__file__).stem
# Get command file name (with suffix)
currentFileNameExt = pathlib.Path(__file__).name

# Use command line if given
if sys.argv[1:]:
    command = sys.argv[1:]
else:
    command = '--trace --input=domoticz.log*'
    command = command.split()

# Read arguments
helpMsg = 'Usage: ' + currentFileNameExt + ' [options]' + """
    [--device=<name to include>: (partial) device (or variable) name to include (can be repeated)
        Select only lines containing this text (enclose it in quote if multiple words)
    [--trace]: enable trace
    [--input=<input file(s)>]: input file name (can be repeated, default to domoticz.log*)
    [--output=<output file>]: output file name (default to <this python file name>.log)
    [--help]: print this help message

   Extract device/variable changes from Domoticz log file

"""

inputFiles = []
selectedDevices = []
outputFile = currentFileName+'.log'
traceFlag = False

try:
    opts, args = getopt.getopt(command, "h",["help", "trace", "device=", "input=", "output="])
except getopt.GetoptError as excp:
    print(excp.msg)
    print('in >'+str(command)+'<')
    print(helpMsg)
    sys.exit(2)

for opt, arg in opts:
    if opt in ("-h", "--help"):
        print(helpMsg)
        sys.exit()
    elif opt == "--device":
        # Remove single quote at begining and end of device specs
        if arg[0] == "'" and arg[-1] == "'":
            arg = arg[1:-1]
        # Remove double quote at begining and end of device specs
        if arg[0] == '"' and arg[-1] == '"':
            arg = arg[1:-1]
        selectedDevices.append(arg)
    elif opt == "--trace":
        traceFlag = True
    elif opt == '--input':
        inputFiles.append(arg)
    elif opt == '--output':
        outputFile = arg
    else:
        print('Unknown option >'+opt+'<')
        print('in >'+str(command)+'<')
        print(helpMsg)
        sys.exit(2)

if traceFlag:
    print('Input file(s)->'+str(inputFiles))
    if outputFile:
        print('Output file ->'+str(outputFile))
    if selectedDevices:
        print('Selected devices->'+str(selectedDevices))

# Open LOG file
out = open(outputFile, 'wt', encoding = 'UTF-8')
out.write('Date\tDevice\tValue\n')

# Convert all logs
for specs in inputFiles:
    logFiles = glob.glob(specs)
    for f in logFiles:
        extractChanges(f)

out.close()

[waltervl]

If you want to do that you better use something like influxdb and Domoticz push to influxdb function. But if it works for you off course it is fine but will give large log files (and have them stored on OS). Also you have to be sure you keep the old log files when Domoticz restarts. So it will need some extra configuration than just running these scripts (warning for the community that would like to implement this too)

[Huib]

Re: Export data from DB to Excel
Unread post by waltervl » Monday 06 February 2023 11:47

Huib wrote: ↑Monday 06 February 2023 10:00
Hi all, I would like to get the P1 information only per month. How can I change the script to get the data per month instead of per day and without the history of all way back? Thanks for the help!


waltervl answer: If you need it only once a month you can better use the export to Excel on the monthly report graph. https://www.domoticz.com/wiki/Managing_ ... graph_data

Thank you for the response and I am aware of this solution. However, I want to automatically upload the monthly data in another file and do not want to download it manually from Domoticz. So that's why I thought it would be useful to only add last month's data only to this script

[peterbos]

Hi,

Correct me if I'm wrong but these scripts make a new file every day, in some cases they overwrite the old one and in some cases they have a name with the date in it. I was wondering if it is possible to append the data to the existing file instead of writing a new file everyday?

Peter

[BartSr]

Recently I had same kind of question.
I was pointed to:
https://www.tutorialspoint.com/python/p ... les_io.htm
There you find the priciple of opening a file overwriting the old one or appending data.
Kr
Bart

[peterbos]

Hi Bart,

Can you share your code?

Peter

[BartSr]

Hi Peter,
I am part of scouting-group.
The building is in use by others from mon- to friday.
I want to know their gas-usage.
Therefore I have written next script:

Code: Select all

return {
	on = {
	
	
	devices = {
		    913, --switch to force the script to run for testpurpose
		},
	    
		timer = {

			'at 07:00', 
			'at 19:00',
			} 
	},
	logging = {
		level = domoticz.LOG_INFO,
		marker = 'BSOtest',
	},
	execute = function(dz, timer)
	    local gasstand = dz.devices(762).counter -- 762 is IDX gasmeter
	    local datetimestamp = tostring(os.date('%Y-%m-%d %H:%M:%S'))

	    file = io.open("/home/pi/gas.txt", "a+")
	    file:write(datetimestamp.." gasstand "..gasstand.."\n")
        file:close()
    end
}

I still need to update in way that saterday and sunday there,s no measuring.
Hopes this give you a direction for your solution.

[peterbos]

Thanks Bart,

This will help me. You are aware you only have to change 'at 07:00' to 'at 07:00 on mon,tue,wed,thu,fri' if you don't want to measure on saterdays and sundays?

Peter

[BartSr]

Thanks to you too Peter. No need to dig into the many time-triggers then.