Spacelynk: How to export data from trend logs to a .xls file?

[Anonymous user]

I have a energy counter and i would like to save data during a month and then export everything to .xls format and send by e-mail. can anyone help me please

[Benji]

For your convenience, in the future please place your question in the corresponding sub community as more people will see it there. This question had been placed in "Community Help", and only people following this sub Community will get notified. You find the sub communities in the top left part. Click "Communities" and then select the area your question is about.

I've moved this question for you.

[Thomas_Rohde]

Hi Benjamin, thanks a lot for placing this topic into the right section!

[Thomas_Rohde]

Hi Pedro, where are you struggling in detail?

- Are you able to sent an email in general?

     => if the answer is no please check out this application-note first AN011_SL - Email, SMS and FTP in spaceLYnk

     => if the answer is yes than I will work with you on a script which realizes the monthly trend-report.

Please let me know.

Kind regards

Thomas

[Thomas_Rohde]

Hi Pedro,

I found within the community here already an example to store all scripts on regular base to a ftp. Means the script needs to be updated.

With the following link you will find script for all trends:

https://community.se.com/message/79811#79811

With the following link you will find script for a single trend:

https://community.se.com/message/79812#79812

Please let me know if this is helpful for you and you can adapt the script to sending an email by yourself.

Otherwise we will go ahead ;o)

Kind regards

Thomas 

[Thomas_Rohde · Accepted solution]

Hi Pedro, attached you will find an updated version of a script which fits to the new internal SW and has now a clear structure in terms of script area where you need to make your adaptations and the other which doesnt need to be changed.

Kind regards

Thomas Rohde

--**************************************************************************--

--** Email trendlog data as CSV attachment created by Erwin van der Zwart **--

--************ For HL from FW 1.5 and SL from FW 1.2 with NGINX ************--

--**************************************************************************--

--*************************** Start of parameters **************************--

--**************************************************************************--

--Gmail (smtp) username !IMPORTANT!

user = 'YOUR EMAIL ADRESS'

--Gmail (smtp) password !IMPORTANT!

password = 'YOUR PASSWORD'

--Sender for e-mail

from = '<' .. user .. '>'

alias_from = 'YOUR ALIAS'

--Recipient for e-mail

to = '<receiver@domain.com>'

alias_to = 'receiver'

--Subject for e-mail

subjectpart1 = 'Trend export file'

subjectpart2 = 'automaticly send by homeLYnk'

--Message on bottom of email (will only be showed when client don't understand attachment)

epilogue = 'End of message'

--Set export mode (selected trend(s) or all trends)

export_all = false

--Set trend names if not all trends need to be exported (only used when export_all = false)

trendnames = {

  "Total Electric Usage",

  "Total Water Usage",

  "Total Gas Usage",

}

--**************************************************************************--

--**************************** End of parameters ***************************--

--**************************************************************************--

--****************** DON'T CHANGE ANYTHING UNDER THIS LINE *****************--

--**************************************************************************--

require('trends')

-- Get all trend names from DB

trends_table = db:getall('SELECT name FROM trends ORDER BY name DESC')

-- Check if all trends or selection neeed to be exported

if export_all == false then

  -- Loop through trends_table

  i = 1

    for _, trend_names in ipairs(trends_table) do

    delete_from_table = true

    -- Loop through trendnames

    for _, trendname in ipairs(trendnames) do

      if trendname == trend_names.name then

            delete_from_table = false

      end

    end

    if delete_from_table == true then

      table.remove(trends_table, i)

      end

      i = i + 1

  end

end

-- Check if the is at least 1 trend to be exported

if #trends_table < 1 then

  log("No trends available, Could not export trends")

  return

end

-- csv buffer

buffer = {}

-- Add to buffer

table.insert(buffer, '"This file contains the export data of ' .. #trends_table .. ' trend(s) and is automaticly created on ' .. os.date("%A",os.time()) .. ' ' .. os.date("%d-%m-%y at %H:%M") .. '"')

-- Add empty line

table.insert(buffer, '""')

-- months

local months = { "Januari", "Februari", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December" }

-- Loop through trends_table

for _, trend_names in ipairs(trends_table) do

  -- Grab trend name from DB table

  trend_name = trend_names.name

  -- Add to buffer

  table.insert(buffer, '"##### START OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')

  -- Add empty line

  table.insert(buffer, '""')

  -- Get current timestamp

  timestamp = os.time()

  now = os.date('*t', timestamp)

  -- get data for the past year

    dates = {}

  dates['start'] = now

  if dates['start'].month == 1 then

      dates['start'].month = 12

    dates['start'].year = dates['start'].year - 1

  else

    dates['start'].month = dates['start'].month - 1

  end

  dates['end'] = os.date('*t')

  -- Set resolution to dayly data

  resolution = 86400

  -- Get last month for data by each days in this month

  trenddatamonth = trends.fetch(trend_name, dates, resolution)

  -- Get last month total avarage data

  trenddatamonthavg = trends.fetchone(trend_name, dates, resolution)

  -- Add to buffer

  table.insert(buffer, '"Export of the average usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')

  -- Add empty line

  table.insert(buffer, '""')

  -- Add header

  table.insert(buffer, '"Start date","End Date","Average month value"')

  -- Add to buffer

  table.insert(buffer, '"01-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. #trenddatamonth .. '-' .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year .. '","' .. trenddatamonthavg .. '"')

  -- Add empty line

  table.insert(buffer, '""')

  -- Add to buffer

  table.insert(buffer, '"Detailed export of the daily usage of the month ' .. months[dates['start'].month] .. ' from trend ' .. trend_name .. '"')

  -- Add empty line

  table.insert(buffer, '""')

  -- Add header

  table.insert(buffer, '"Weekday","Date","Average day value"')

  for _, row in ipairs(trenddatamonth) do

    stamp = dates['start'].year .. '-' .. dates['start'].month .. '-' .. string.format("%02d", _)

        local y, m, d = stamp:match("(%d+)%-(%d+)%-(%d+)")

        local t = { year = y, month = m, day = d} 

    -- format csv row

    csv = string.format('%q,%q,%q', os.date("%A",os.time(t)), "" .. string.format("%02d", _) .. "-" .. string.format("%02d", dates['start'].month) .. "-" .. dates['start'].year, row)

    -- add to buffer

    table.insert(buffer, csv)

  end

  -- Add empty line

  table.insert(buffer, '""')

  -- Add header

  table.insert(buffer, '"##### END OF TREND ' .. _ .. ': ' .. trend_name .. ' #####"')

  -- Add empty line

  table.insert(buffer, '""')

end

--Create table to include mail settings

local settings = {

    from = from,

    rcpt = to,

    user = user,

    password = password,

    server = 'smtp.gmail.com',

    port = 465,

    secure = 'sslv23',

}

--Create attachment inside FTP server

src = 'Trend Export '.. os.date('%Y-%m-%d %H#%M#%S') .. '.csv'

dst = '/home/ftp/' .. src

io.writefile(dst, buffer)

--Create subject

subject = subjectpart1 .. ": " .. src .. " " .. subjectpart2

--Load required modules to send email with attachment

local smtp = require("socket.smtp")

local mime = require("mime")

local ltn12 = require("ltn12")

--Create e-mail header

settings.source = smtp.message{

headers = {

          from = '' .. alias_from .. ' ' .. from .. '',

          to = '' .. alias_to .. ' ' .. to .. '',

          subject = subject

},

--Load attachment inside body    

body = {

preamble = "",

[1] = { 

        headers = {

           ["content-type"] = 'text/plain',

           ["content-disposition"] = 'attachment; filename="'..src..'"',

           ["content-description"] = '.. src ..',

           ["content-transfer-encoding"] = "BASE64",

        },

        body = ltn12.source.chain(

          ltn12.source.file(io.open(dst, "rb")),

          ltn12.filter.chain(

          mime.encode("base64"),

          mime.wrap()

        )

      )

    },

      epilogue = epilogue

  }

}

--Send the email

r, e = smtp.send(settings)

--Create alert when sending gives an error with error message

if (e) then

  log (e)

  log (r)

  alert("Could not send email: ", e, "\n")

end

--Delete created backup file from ftp folder inside HL

os.remove(dst)

[Thomas_Rohde]

Hi Pedro,

is this topic solved or do you need still support?

In case this topic is answered please mark it accordingly

so that we can get a better view on which kind of topics are really still open.

Thanks a lot for your understanding and support.

Kind regards

Thomas