Csv file as attachment

[PassivPluss]

Hi

We got an customer that wants to get kWh values once an hour to another system.
The file is to be sent as csv file attached to a mail.
The script underneath is run once an hour to save the value and i want to send the mail every hour with the values last 24 hour and delete the oldest entry each time. 

a better solution than this might be to trend the object and get values from the trend if possible?

Under i have made the data table correct and run this once an hour.
But i have some issues and questions.

1: Then i log data underneath the colums is not sorted right. I tried to sort by time but that doesnt help?
2: I dont understand how to make the csv from this data and send it by email?
3: the old csv file can then be deleted.

Can you help with this?

data = storage.get('myobjectlogs', {})
-- data = {}
table.insert(data, {
  ['time'] = os.date('%Y-%m-%d %X'),
  ['id'] = 'byggmax', 
  ['verdi'] = grp.getvalue('2/1/0'),
})
table.sort(data, function(a, b)
  return a.time < b.time
end)



storage.set('myobjectlogs', data)
--log(storage.get('myobjectlogs'))
log(data)


ftpfile = string.format('/home/ftp/%s.csv', os.date('%Y-%m-%d_%H-%M'))
--[[ get past hour data (3600 seconds)
logtime = os.time() - 60 * 60

-- list of objects by id
objects = {}

-- objects with logging enabled
query = 'SELECT address, datatype, name FROM objects WHERE disablelog=0'
for _, object in ipairs(db:getall(query)) do
  objects[ tonumber(object.address) ] = {
    datatype = tonumber(object.datatype),
    name = tostring(object.name or ''),
  }
end

-- csv buffer
buffer = { '"date","address","name","value"' }

-- get object logs
query = 'SELECT src, address, datahex, logtime, eventtype FROM objectlog WHERE logtime >= ? ORDER BY id DESC'
for _, row in ipairs(db:getall(query, logtime)) do
  object = objects[ tonumber(row.address) ]

  -- found matching object and event type is group write
  if object and row.eventtype == 'write' then
    datatype = object.datatype

    -- check that object datatype is set
    if datatype then
      -- decode data
      data = knxdatatype.decode(row.datahex, datatype)

      -- remove null chars from char/string datatype
      if datatype == dt.char or datatype == dt.string then
        data = data:gsub('%z+', '')
      -- date to DD.MM.YYYY
      elseif datatype == dt.date then
        data = string.format('%.2d.%.2d.%.2d', data.day, data.month, data.year)
      -- time to HH:MM:SS
      elseif datatype == dt.time then
        data = string.format('%.2d:%.2d:%.2d', data.hour, data.minute, data.second)
      end
    else
      data = ''
    end

    -- format csv row
    logdate = os.date('%Y.%m.%d %H:%M:%S', row.logtime)
    csv = string.format('%q,%q,%q,%q', logdate, knxlib.decodega(row.address), object.name, tostring(data))

    -- add to buffer
    table.insert(buffer, csv)
  end
end]]
  data2 = table.concat(data)

  io.writefile(ftpfile, data2)

[admin]

Provide CSV example that you want to get. Single scheduled script for collecting data and sending it should be used.

[PassivPluss]

Here is example.

Best would be to go with trend and collect data for last 24 hours on script run and send as email attachment

(CSV format) :
Kolonne Innhold Beskrivelse
1 timestamp one of the following
• YYYY-MM-DD TT
• YYYY-MM-DD TT:mm
• YYYY-MM-DD TT:mms
• DD-MM-YYYY TT
• DD-MM-YYYY TT:mm
• DD-MM-YYYY TT:mms
2 ID
3 read kwh


04-07-2012 00:00:00,707057500075005871,0.00
03-07-2012 23:00:00,707057500075005871,0.00
03-07-2012 22:00:00,707057500075005871,0.00
03-07-2012 21:00:00,707057500075005871,0.00
03-07-2012 20:00:00,707057500075005871,0.00
03-07-2012 19:00:00,707057500075005871,0.00
03-07-2012 18:00:00,707057500075005871,0.00
03-07-2012 17:00:00,707057500075005871,0.00

[admin]

Create a scheduled script that runs every hour. Modify ids table as needed: key is id in the CSV, value is the trend log name. Also fill in correct mail settings and to variable.

Code:

ids = {
  ['707057500075005871'] = 'Temperature',
  ['213123123123123123'] = 'Humidity',
}

require('trends')

dates = {}
dates['start'] = os.date('*t')
dates['start'].day = dates['start'].day - 1
dates['end'] = os.date('*t')

buf = {}
now = os.time()

for id, tname in pairs(ids) do
  values = trends.fetch(tname, dates)
  count = #values
  step = 86400 / count

  for i, value in ipairs(values) do
    buf[ #buf + 1 ] = {
      now - (count - i) * step,
      id,
      value
    }
  end
end

table.sort(buf, function(a, b)
  return a[ 1 ] > b[ 1 ]
end)

for i, row in ipairs(buf) do
  row[ 1 ] = os.date('%Y-%m-%d %H:%M', row[ 1 ])
  buf[ i ] = table.concat(row, ',')
end

csv = table.concat(buf, '\n')

to = 'to@example.com'

settings = {
  -- "from" field, only e-mail must be specified here
  from = 'sender@example.com',
  -- smtp username
  user = 'sender@example.com',
  -- smtp password
  password = '12345678',
  -- smtp server
  server = 'smtp.gmail.com',
  -- smtp server port
  port = 465,
  -- enable ssl, required for gmail smtp
  secure = 'tlsv1_2',
}

subject = 'CSV'

smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')

function escape(v)
  return '<' .. tostring(v) .. '>'
end

to = escape(to)

msgt = {
  headers = {
    to = to,
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="logs.csv"',
    ['content-transfer-encoding'] = 'BASE64',
    subject = subject,
  },
  body = ltn12.source.chain(
    ltn12.source.string(csv),
    ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64'))
  )
}

settings.source = smtp.message(msgt)
settings.from = escape(settings.from)
settings.rcpt = { to }

res, err = smtp.send(settings)
log(res, err)

[PassivPluss]

Perfect as always
Thanks for the help

One more question, the export now uses resolution from trend. If I would like to use hourly export values but want higher resolution on the LM trend window how can that be adjusted?

Wishes you a Good Christmas and a Great New Year

[admin]

You can create separate trend logs with hourly resolution for this. Use lowest data retention period so these trends use minimum amount of disk space.

[PassivPluss]

Another question. The customer mail import program does not recieve the emails.
In their guide it is said that it should use 7bit csv file. Is that ok?
When sending the mail with csv to my email and then forward to their system everything comes in and works.
I can se on the mail info that the LM mail is Base64 but when i forward outlook makes it different encoding.
Can the base 64 be marked as spam and discarded?

Found this online, can the reciever have problems to recieve if the <> is not used or does that not matter?

Note: SMTP servers can be very picky with the format of e-mail addresses. To be safe, use only addresses of the form "<fulano@example.com>" in the from and rcpt arguments to the send function

The importsystem is Energinet

[PassivPluss]

We dont get this to work. Sending file to my email works but sending to energinet doesnt work. See my questions in last post regarding base64. How can we make this 7bit?

[admin]

Try changing the msgt variable to this:

Code:

msgt = {
  headers = {
    to = to,
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="logs.csv"',
    ['content-transfer-encoding'] = '7BIT',
    subject = subject,
  },
  body = ltn12.source.string(csv)
}

to is already escaped using <> in the script

[PassivPluss]

Try changing the msgt variable to this:

Code:

msgt = {
  headers = {
    to = to,
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="logs.csv"',
    ['content-transfer-encoding'] = '7BIT',
    subject = subject,
  },
  body = ltn12.source.string(csv)
}

to is already escaped using <> in the script

Tried this but the csv looks like this in outlook?
MjAyMy0wMi0wMyAwODowMCw3MDcwNTc1MDAwNzY1NjEyMzksMjE4NzguOAoyMDIzLTAyLTAzIDA3
OjAwLDcwNzA1NzUwMDA3NjU2MTIzOSwyMTg3Ni41CjIwMjMtMDItMDMgMDY6MDAsNzA3MDU3NTAw
MDc2NTYxMjM5LDIxODcxLjkKMjAyMy0wMi0wMyAwNTowMCw3MDcwNTc1MDAwNzY1NjEyMzksMjE4
NjcuNgoyMDIzLTAyLTAzIDA0OjAwLDcwNzA1NzUwMDA3NjU2MTIzOSwyMTg2My4xCjIwMjMtMDIt
MDMgMDM6MDAsNzA3MDU3NTAwMDc2NTYxMjM5LDIxODU2LjMKMjAyMy0wMi0wMyAwMjowMCw3MDcw
NTc1MDAwNzY1NjEyMzksMjE4NTMuNgoyMDIzLTAyLTAzIDAxOjAwLDcwNzA1NzUwMDA3NjU2MTIz
OSwyMTg1MwoyMDIzLTAyLTAzIDAwOjAwLDcwNzA1NzUwMDA3NjU2MTIzOSwyMTg1Mi4zCjIwMjMt
MDItMDIgMjM6MDAsNzA3MDU3NTAwMDc2NTYxMjM5LDIxODQ3LjMKMjAyMy0wMi0wMiAyMjowMCw3
MDcwNTc1MDAwNzY1NjEyMzksMjE4NDMuNgoyMDIzLTAyLTAyIDIxOjAwLDcwNzA1NzUwMDA3NjU2
MTIzOSwyMTgzOC45



this is the mail script. We tried to make use of table as the integrator wanted to have a cc \ to their own email to see file.
But afterward they said that we couldn't have more than one in  to field so we only send to one adress
what about this field underneath?    ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64'))



smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')

    if type(to) ~= 'table' then
    to = { to }
  end

  for index, email in ipairs(to) do
    to[ index ] = '' .. tostring(email) .. ''
      end
        -- fixup from field
  local from = '' .. tostring(settings.from) .. ''
 
        if type(cc) ~= 'table' then
 
    cc = { cc }
  end

  for index, email in ipairs(cc) do
    cc[ index ] = '' .. tostring(email) .. ''
      end



msgt = {
  headers = {
  to = table.concat(to, ', '),
        cc = table.concat(cc, ', '),
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="file.csv"',
    ['content-transfer-encoding'] = '7BIT',
    subject = subject,
  },
  body = ltn12.source.chain(
    ltn12.source.string(csv),
    ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64'))
  )
}

settings.source = smtp.message(msgt)
settings.from = from -- escape(settings.from)
settings.rcpt = to
res, err = smtp.send(settings)

[admin]

Use this. Put multiple recipients into the to table. The first email is the main recipient, all others will receive a copy. Separate CC header is not needed.

Code:

to = { 'first@example.com', 'second@example.com', 'third@example.com' }

subject = 'CSV'

smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')

if type(to) ~= 'table' then
  to = { to }
end

for index, email in ipairs(to) do
  to[ index ] = '<' .. tostring(email) .. '>'
end

from = '<' .. tostring(settings.from) .. '>'

msgt = {
  headers = {
    ['To'] = to[ 1 ],
    ['From'] = from,
    ['Subject'] = subject,
    ['Content-Type'] = 'text/csv',
    ['Content-Disposition'] = 'attachment; filename="file.csv"',
    ['Content-Transfer-Encoding'] = '7BIT',
  },
  body = ltn12.source.string(csv)
}

settings.source = smtp.message(msgt)
settings.from = from
settings.rcpt = to

res, err = smtp.send(settings)
log(res, err)

[PassivPluss]

works perfect.
I Guess the     ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64')) made some problems with the recieving.

is that the mailformat? while the other input was the csv file format?

[admin]

This is the encoding format (base64 in your case). For 7-bit you need to send the attachment as is without any additional encoding.

[mreds5]

Create a scheduled script that runs every hour. Modify ids table as needed: key is id in the CSV, value is the trend log name. Also fill in correct mail settings and to variable.

Code:

ids = {
  ['707057500075005871'] = 'Temperature',
  ['213123123123123123'] = 'Humidity',
}

require('trends')

dates = {}
dates['start'] = os.date('*t')
dates['start'].day = dates['start'].day - 1
dates['end'] = os.date('*t')

buf = {}
now = os.time()

for id, tname in pairs(ids) do
  values = trends.fetch(tname, dates)
  count = #values
  step = 86400 / count

  for i, value in ipairs(values) do
    buf[ #buf + 1 ] = {
      now - (count - i) * step,
      id,
      value
    }
  end
end

table.sort(buf, function(a, b)
  return a[ 1 ] > b[ 1 ]
end)

for i, row in ipairs(buf) do
  row[ 1 ] = os.date('%Y-%m-%d %H:%M', row[ 1 ])
  buf[ i ] = table.concat(row, ',')
end

csv = table.concat(buf, '\n')

to = 'to@example.com'

settings = {
  -- "from" field, only e-mail must be specified here
  from = 'sender@example.com',
  -- smtp username
  user = 'sender@example.com',
  -- smtp password
  password = '12345678',
  -- smtp server
  server = 'smtp.gmail.com',
  -- smtp server port
  port = 465,
  -- enable ssl, required for gmail smtp
  secure = 'tlsv1_2',
}

subject = 'CSV'

smtp = require('socket.smtp')
mime = require('mime')
ltn12 = require('ltn12')

function escape(v)
  return '<' .. tostring(v) .. '>'
end

to = escape(to)

msgt = {
  headers = {
    to = to,
    ['content-type'] = 'text/csv',
    ['content-disposition'] = 'attachment; filename="logs.csv"',
    ['content-transfer-encoding'] = 'BASE64',
    subject = subject,
  },
  body = ltn12.source.chain(
    ltn12.source.string(csv),
    ltn12.filter.chain(mime.encode('base64'), mime.wrap('base64'))
  )
}

settings.source = smtp.message(msgt)
settings.from = escape(settings.from)
settings.rcpt = { to }

res, err = smtp.send(settings)
log(res, err)

Hi,

I've adapted this code for use in a couple of my logic machines, and what I've found is that when I run this, the datetime column doesn't match up with the values when I compare them to the data in the trend logs. The data which is shown is from about 10hrs45 before that time. In the image below, the left part is data that I got after running the script at 10:30am BST. On the right is the data from the logic machine trend logs export that actually matches up with this data - so 21/08/2023 20:15 on the trend logs corresponds to 22/08/2023 07:00 in the emailed data.

   

I've done some digging and it looks like the issue arises in the code  
values = trends.fetch(tname, dates)
What could be the reason for this?