Domoticz

Open source Home Automation System
https://forum.domoticz.com/

Export data from DB to Excel

https://forum.domoticz.com/viewtopic.php?t=30725

Page 1 of 3

Export data from DB to Excel

Posted: Monday 30 December 2019 16:02
by JuanUil
Hi There,

I have made an Excel sheet with all the date from outside temp, kWh usage and solar production.
At the moment I download the database and copy the data from there to my excel sheet.
Is the a way to do this automatically?

Curious if anyone has ever done this.

greetz
Jan

Re: Export data from DB to Excel

Posted: Monday 30 December 2019 17:43
by waaren
JuanUil wrote: ↑Monday 30 December 2019 16:02 I have made an Excel sheet with all the date from outside temp, kWh usage and solar production.
At the moment I download the database and copy the date from there to my excel sheet.
Is the a way to do this automatically?
If a CSV file works for you then a bash- or dzVents script can do this for you. Just let me know the ID's or names, the field names and the frequency and I will have a look.

Re: Export data from DB to Excel

Posted: Monday 30 December 2019 22:14
by JuanUil
Hi Waaren,

I Think I can find a way to import the csv to my workbook.
So The ID's
44 for the kWh used
48 for the solarproduction
72 for the outside temp

happy oud en nieuw

Jan

Re: Export data from DB to Excel

Posted: Tuesday 31 December 2019 1:55
by waaren
JuanUil wrote: ↑Monday 30 December 2019 22:14 44 for the kWh used
48 for the solarproduction
72 for the outside temp
can you try this ? it collects the date from the daily history tables in the domoticz database.

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

return 
{
    on =
    {  
        timer =
        {
            "at 01:07",
        },
    },
    
    logging =   
    {  
        level = domoticz.LOG_DEBUG,
        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 -- which sqlite3 
        local allTemperatures = "72" -- comma separated list of temperature devices to export
        local allUsages = "44" -- comma separated list 
        local allSolar = "48" -- comma separated list 
        local csvFile = "/home/pi/domoticz2excel.csv"
        
        -- =======================  No modification needed below this line ==================
        
        local copy = "safeCopy.db"  
  
        local baseCommand = "sudo " .. sqlite .. " -header -csv " .. copy 
        local closure     = " >> "  .. csvFile
        local collects = {} 

        collects.meters = 'select a.id as meter, a.name as naam, b.Value as value, b.counter as counter from devicestatus a , meter_calendar b where a.id = b.devicerowid and a.id in (' .. allUsages .. ',' .. allSolar ..');'
        collects.temperatures = 'select a.id as temperature, a.name as naam, b.temp_min as min, b.temp_max as max, b.temp_avg as avg, b.date as date from devicestatus a , temperature_calendar b where a.id = b.devicerowid and a.id in (' .. allTemperatures .. ') order by a.id;'

        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 type, 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
                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
}

Re: Export data from DB to Excel

Posted: Tuesday 31 December 2019 9:59
by JuanUil
Hi Waaren,

Thank you for your effort!!!
I am not at home today so I will try it tomorrow and let you know.

Happy oud en nieuw :D

Re: Export data from DB to Excel

Posted: Thursday 02 January 2020 14:05
by JuanUil
Hi Waaren,

First of all: Best wishes for 2020!!!
I have tried your program and it makes a domoticz2csv file in home/pi.
The problem is that I can't open it.
In Filezilla it gives:

Code: Select all

Fout:	/home/pi/Documents/test.csv: open for read: permission denied
Fout:	Bestandsoverdracht mislukt
So I cannot check.
I have tried to change the rights of the file but was not succesful

Jan

Re: Export data from DB to Excel

Posted: Thursday 02 January 2020 15:49
by waaren
JuanUil wrote: ↑Thursday 02 January 2020 14:05 The problem is that I can't open it.
Hi Jan,

also a happy new year for you.

What is the owner of the file ? And what are the permissions ?
For now I add a chmod statement to the 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
]]--

return 
{
    on =
    {  
        timer =
        {
            "at 01:07",
        },
    },
    
    logging =   
    {  
        level = domoticz.LOG_DEBUG,
        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 -- which sqlite3 
        local allTemperatures = "72" -- comma separated list of temperature devices to export
        local allUsages = "44" -- comma separated list 
        local allSolar = "48" -- comma separated list 
        local csvFile = "/home/pi/domoticz2excel.csv"
        
        -- =======================  No modification needed below this line ==================
        
        local copy = "safeCopy.db"  
  
        local baseCommand = "sudo " .. sqlite .. " -header -csv " .. copy 
        local closure     = " >> "  .. csvFile
        local collects = {} 

        collects.meters = 'select a.id as meter, a.name as naam, b.Value as value, b.counter as counter from devicestatus a , meter_calendar b where a.id = b.devicerowid and a.id in (' .. allUsages .. ',' .. allSolar ..');'
        collects.temperatures = 'select a.id as temperature, a.name as naam, b.temp_min as min, b.temp_max as max, b.temp_avg as avg, b.date as date from devicestatus a , temperature_calendar b where a.id = b.devicerowid and a.id in (' .. allTemperatures .. ') order by a.id;'

        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 type, 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
}

Re: Export data from DB to Excel

Posted: Thursday 02 January 2020 17:12
by JuanUil
The owner is root and normaly I am pi, but it is working now!!
Fabulous, thank you verry much!!

I am going to make a macro to import the csv to my workbook now.
If you are interested I will show you the result.

Greetings
Jan

Re: Export data from DB to Excel

Posted: Thursday 02 January 2020 17:38
by Egregius
Isn't it a better idea to put the data in a MySQL database instead of Excel?
How is Excel gonna handle the data after several years?

Re: Export data from DB to Excel

Posted: Thursday 02 January 2020 17:49
by JuanUil
I have data from my Youless from 2015 till now.
The csv file made from the program of Waaren is gathering them perfectly.
The advandtage from excel is that you can make nice graphs and predictions. :D

Re: Export data from DB to Excel

Posted: Thursday 02 January 2020 17:56
by waaren
JuanUil wrote: ↑Thursday 02 January 2020 17:12 If you are interested I will show you the result.
That would be nice. Thx in advance !

Re: Export data from DB to Excel

Posted: Friday 23 October 2020 14:29
by fzoet
Very interested in this script. I tried it and it runs but the csv file remains empty. The data i am trying to export is my P1 power meter (idx 17) and p1 gas meter (idx 21).

That way I can use excel to calculate the actual power usage when combined with the solar inverter download (until i can get this automated in Domoticz as well, see https://domoticz.com/forum/viewtopic.php?f=14&t=34272)

I just changed the idx numbers for allusages to 17, and allsolar to 21 to test. No other changes. So what else needs to be amended to get the export going?

Re: Export data from DB to Excel

Posted: Friday 23 October 2020 15:47
by waaren
fzoet wrote: ↑Friday 23 October 2020 14:29 Very interested in this script. I tried it and it runs but the csv file remains empty. The data i am trying to export is my P1 power meter (idx 17) and p1 gas meter (idx 21).
The P1 history is in another table ( MultiMeter_Calendar ) and was not implemented yet in this script. I updated the script to include it (see below)

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

return
{
    on =
    {
        timer =
        {
            'at 01:07',
            'at 14:47',
        },
        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 = '/opt/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 = '/usr/local/domotica/data/domoticz2excel.csv'

        -- set to '' for the types you don't need
        local allTemperatures = '152,148' -- comma separated list of Temperature sensors to export
        local allMeters = '36,1168,2872' -- comma separated list of Solar +  Meters + Usage sensors
        local allMultiMeters = '35' -- comma separated list P1 energy sensor

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

        local copy = 'safeCopy.db'

        local baseCommand = 'sudo ' .. sqlite .. ' -header -csv ' .. copy
        local closure     = ' >> '  .. csvFile
        local collects = {}

        collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Counter Counter, b.Date Date FROM DeviceStatus a , Meter_Calendar b ' ..
                           ' WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ');'

        collects.Temperatures = 'SELECT a.ID Temperature, a.Name Name, b.Temp_Min min, b.Temp_Max max, b.Temp_Avg avg, b.Date Date ' ..
                                ' FROM DeviceStatus a , Temperature_Calendar b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allTemperatures .. ') ORDER BY a.ID;'
    
        collects.MultiMeters = 'SELECT a.Name, b.* FROM DeviceStatus a , MultiMeter_Calendar b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMultiMeters .. ') ORDER BY b.DeviceRowID;'

        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
}

Re: Export data from DB to Excel

Posted: Monday 26 October 2020 12:33
by fzoet
@waaren thanks. With a few small modifications (paths and smart meter idx) this works and i get a CSV dump of the P1 meter per day.

How can I change the code so that I get the values for each day (so every measurement)? These are needed to calculate the actual usage per counter (high / normal), and the percentage of used solar energy - and with that the return on investment of the solar panels.

For others that are interested: I used the paths below for the raspberry pi Domoticz install. Note that the change is the reference to the home directory.

Code: Select all

        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 = "/home/pi/domoticz2excel.csv"

Re: Export data from DB to Excel

Posted: Monday 26 October 2020 12:55
by waaren
fzoet wrote: ↑Monday 26 October 2020 12:33 How can I change the code so that I get the values for each day (so every measurement)? These are needed to calculate the actual usage per counter (high / normal), and the percentage of used solar energy - and with that the return on investment of the solar panels.
Change the query strings to

Code: Select all

        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 .. ')'

        collects.Temperatures = 'SELECT a.ID ID, a.Name Name, b.Temperature Temperature, b.Date Date ' ..
                                ' FROM DeviceStatus a , Temperature b WHERE a.ID = b.DeviceRowID AND ' ..
                                ' a.ID IN ( '.. allTemperatures .. ') ORDER BY a.ID '
                                
        collects.MultiMeters =  'SELECT a.Name, b.* FROM DeviceStatus a , MultiMeter b WHERE a.ID = b.DeviceRowID ' ..
                                ' AND a.ID IN (' .. allMultiMeters .. ') ORDER BY b.DeviceRowID'
To get "every" measurement (in fact it is one sample per 5 minutes)

Re: Export data from DB to Excel

Posted: Monday 26 October 2020 17:22
by fzoet
Thanks @waaren.

As the file will be containing the past 24 hours and overwrite the previous file, I changed the filename to contain yesterday's date and run the timer at 00:04.

Code: Select all

        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 = "/home/pi/domoticz2excel"
        local csvFile = csvFile .. "_" .. os.date("%Y-%m-%d",os.time()-24*60*60) .. ".csv"
Question:
is there a way to dump this daily data from all the past months at once? - to get the backlog

Re: Export data from DB to Excel

Posted: Monday 26 October 2020 21:56
by waaren
fzoet wrote: ↑Monday 26 October 2020 17:22 Question:
is there a way to dump this daily data from all the past months at once? - to get the backlog
Only if you have daily backups that go back that far. The short log sensors (288 rows a day / sensor) are collated and averaged into the long term storage (1 row / day) once a day just after midnight.
The retention period for short log sensors is max. 7 days

Re: Export data from DB to Excel

Posted: Tuesday 27 October 2020 13:31
by fzoet
@waaren. Thanks, yes I feared that from what I have already read about domoticz. Thanks for the confirmation.

Re: Export data from DB to Excel  [SOLVED]

Posted: Tuesday 27 October 2020 13:41
by fzoet
Here is the last full code, as it works in my setup. Thanks @waaren for all your contributions - the credits fully go to you!

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:16',
        },
        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 = "/home/pi/domoticz2excel" --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 allTemperatures = '152,148' -- comma separated list of Temperature sensors to export
        local allMeters = '36,1168,2872' -- comma separated list of Solar +  Meters + Usage sensors
        local allMultiMeters = '17' -- comma separated list P1 energy sensor

        -- =======================  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 daily data==
        --[[
        collects.meters = 'SELECT a.ID Meter, a.Name Name, b.Value Value, b.Counter Counter, b.Date Date FROM DeviceStatus a , Meter_Calendar b ' ..
                           ' WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMeters .. ');'

        collects.Temperatures = 'SELECT a.ID Temperature, a.Name Name, b.Temp_Min min, b.Temp_Max max, b.Temp_Avg avg, b.Date Date ' ..
                                ' FROM DeviceStatus a , Temperature_Calendar b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allTemperatures .. ') ORDER BY a.ID;'
    
        collects.MultiMeters = 'SELECT a.Name, b.* FROM DeviceStatus a , MultiMeter_Calendar b WHERE a.ID = b.DeviceRowID AND a.ID IN (' .. allMultiMeters .. ') ORDER BY b.DeviceRowID;'
        ]]--
        --==end query daily data

        --==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 .. ')'

        collects.Temperatures = 'SELECT a.ID ID, a.Name Name, b.Temperature Temperature, b.Date Date ' ..
                                ' FROM DeviceStatus a , Temperature b WHERE a.ID = b.DeviceRowID AND ' ..
                                ' a.ID IN ( '.. allTemperatures .. ') ORDER BY a.ID '
                                
        collects.MultiMeters =  'SELECT a.Name, b.* FROM DeviceStatus a , MultiMeter b WHERE a.ID = b.DeviceRowID ' ..
                                ' AND a.ID IN (' .. allMultiMeters .. ') ORDER BY b.DeviceRowID'
        --==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
}

Re: Export data from DB to Excel

Posted: Monday 10 May 2021 19:20
by tukiplus
Hai @waaren,
thanx for the code, is work like what i expected. this is the sample output from my modbus energy meter.
Spoiler: show
idx|name|stand |date

113|kwh1|73,408 |30/04/2021
113|kwh1|74,193 |01/05/2021
113|kwh1|74,978 |02/05/2021
113|kwh1|75,763 |03/05/2021
113|kwh1|76,548 |04/05/2021
113|kwh1|77,334 |05/05/2021
113|kwh1|78,119 |06/05/2021
113|kwh1|78,904 |07/05/2021
113|kwh1|79,689 |08/05/2021
113|kwh1|80,474 |09/05/2021
but is there a way to change the table to form like this?
Spoiler: show
idx|name|30/04/2021|01/05/2021|02/05/2021|03/05/2021|04/05/2021|05/05/2021|06/05/2021|07/05/2021|08/05/2021|09/05/2021

113|kwh1|73,408 |74,193 |74,978 |75,763 |76,548 |77,334 |78,119 |78,904 |79,689 |80,474
thanx for your help, and sorry for my bed english :) :) :)
All times are UTC+01:00
Page 1 of 3

Powered by phpBB® Forum Software © phpBB Limited