Logic Machine Forum
export csv - Printable Version

+- Logic Machine Forum (https://forum.logicmachine.net)
+-- Forum: LogicMachine eco-system (https://forum.logicmachine.net/forumdisplay.php?fid=1)
+--- Forum: Scripting (https://forum.logicmachine.net/forumdisplay.php?fid=8)
+--- Thread: export csv (/showthread.php?tid=723)

export csv - akn - 07.04.2017

I have the following group addresses:

1/1 / ... - the actual value of meter 
1/2 / ... - the value of meter  at the beginning of the month
1/3 / ... - the value of consumption for the current month
1/4 / ... - the value of consumption for the previous month

I need a script that once a month, generated a csv file on a local storage. Another script on the scheduled or by event sent this file by email.

thank you in advance

RE: export csv - buuuudzik - 07.04.2017

(07.04.2017, 09:11)akn Wrote: I have the following group addresses:

1/1 / ... - the actual value of meter 
1/2 / ... - the value of meter  at the beginning of the month
1/3 / ... - the value of consumption for the current month
1/4 / ... - the value of consumption for the previous month

I need a script that once a month, generated a csv file on a local storage. Another script on the scheduled or by event sent this file by email.

thank you in advance

This can be for start:

Once a month:
Code:
actual_value = grp.getvalue('1/1/1')
start_month = grp.getvalue('1/1/1')
current_month = grp.getvalue('1/1/1')
previous_month = grp.getvalue('1/1/1')
labels = "actual_value, start_month, current_month, previous_month/r/n"
meter = labels .. actual_value .. "," .. start_month .. "," .. current_month .. "," .. previous_month .. "/r/n"

storage.set('meter', meter)


Scheduled:
Code:
-- read csv report file
data = storage.get('meter')

-- send file as report.csv with text/csv mime type
res, err = mailattach('someone@example.com', 'Report for current month', 'CSV file attached', 'report.csv', data, 'text/csv')


RE: export csv - admin - 07.04.2017

Improved example Wink

Code:
rows = {}

-- from 1/1/1 to 1/1/30
addrstart = 1
addrend = 30

rows[ #rows + 1 ] = 'name,actual_value,start_month,current_month,previous_month'

for i = addrstart, addrend do
  actual_value_obj = grp.find('1/1/' .. i)
  name = actual_value_obj.name
  actual_value = actual_value_obj.value
  start_month = grp.getvalue('1/2/' .. i)
  current_month = grp.getvalue('1/3/' .. i)
  previous_month = grp.getvalue('1/4/' .. i)
  
  rows[ #rows + 1 ] = name .. ',' .. actual_value .. ',' .. start_month .. ',' .. current_month .. ',' .. previous_month
end

csv = table.concat(rows, '\r\n')


RE: export csv - buuuudzik - 07.04.2017

Perfect way, the best solution, table.concat() in right place can do miracle. I didn't know. From one hour ago I had a big problem with export to .csv tables(on Load Balancer) with 100k rows but with 2 table.concat() functions it is not a thingWink

So in this subject also it is better Smile

Thanks admin

RE: export csv - akn - 10.04.2017

data = storage.get('meter')
res, err = mailattach('someone@example.com', 'Report for current month', 'CSV file attached', 'report.csv', data, 'text/csv')

I have error,

send mail 10.04.2017 18:47:11
* arg: 1
* nil
* arg: 2
* string: unknown type in record hdr

RE: export csv - akn - 10.04.2017

How can I optimize this script and extend it to all objects?

actual_value = grp.getvalue('3/0/..')
start_month = grp.getvalue('3/1/..')
current_month = grp.getvalue('3/2/..')

current_month = actual_value - start_month
grp.write('3/2/..', current_month)

RE: export csv - Erwin van der Zwart - 10.04.2017

Hi,

Try this:

for i = 0, 255, 1 do
   actual_value = grp.getvalue('3/0/' .. i)
   start_month = grp.getvalue('3/1/' .. i)
   current_month = actual_value - start_month
   grp.update('3/2/' .. i, current_month)
   os.sleep(0.2)
end

BR,

Erwin

RE: export csv - akn - 11.04.2017

Thanks guys, but how about this problem?

data = storage.get('meter')
res, err = mailattach('someone@example.com', 'Report for current month', 'CSV file attached', 'report.csv', data, 'text/csv')

I have error,

send mail 10.04.2017 18:47:11
* arg: 1
* nil
* arg: 2
* string: unknown type in record hdr

RE: export csv - admin - 11.04.2017

Have you tried installing the updated package from here?
http://forum.logicmachine.net/showthread.php?tid=402

RE: export csv - akn - 11.04.2017

1. Admin does not work. I also tried to change the security settings ("sslv23", "tlsv1", "tlsv11" or "tlsv12")
Using 25 port without security settings, everything works!

2. Why does this script send two emails?

-- send an e-mail with attachment
function mailattach(to, subject, message, filename, filedata, mimetype)
-- make sure these settings are correct
local settings = {
-- "from" field, only e-mail must be specified here
from = 'user@domen.com',
-- smtp server
server = 'mail.apollo.lv',
-- smtp server port
port = 25,


}

  local smtp = require('socket.smtp')

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

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

  -- escape double quotes in file name
  filename = filename:gsub('"', '\\"')

  -- message headers and body
  settings.source = smtp.message({
    headers = {
      to = table.concat(to, ', '),
      subject = subject,
    },
    body = {
      {
        headers = {
          ['Content-Type'] = 'text/html; charset=utf-8',
        },
        body = mime.eol(0, message)
      },
      {
        headers = {
          ['Content-Type'] = mimetype or 'text/plain',
          ['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
          ['Content-Transfer-Encoding'] = 'BASE64',
        },
        body = ltn12.source.chain(
          ltn12.source.string(filedata),
          ltn12.filter.chain(mime.encode('base64'), mime.wrap())
        )
      }
    }
  })

  -- fixup from field
  settings.from = '<' .. tostring(settings.from) .. '>'
  settings.rcpt = to

  return smtp.send(settings)
end

RE: export csv - admin - 19.04.2017

Are you sending to a different e-mail or to the same one? Just tested it with different e-mails and only one message is received.

RE: export csv - akn - 22.04.2017

I adjusted the script so that users can change the settings themselves. I want to add possibility to send a copy to another email address.

--------- Event-based----------------

-- read csv report file
data = storage.get('csv')
userdatato = grp.getvalue('1/6/0')

-- send file as report.csv with text/csv mime type
res, err = mailattach(userdatato, '!!!!', 'CSV file attached', 'report.csv', data, 'text/csv')

--------- Common functions ----------------

-- send an e-mail with attachment
function mailattach(to, subject, message, filename, filedata, mimetype)
-- make sure these settings are correct

local settings = {

-- "from" field, 255 byte data type
from = grp.getvalue('1/6/1'),
-- smtp server, 255 byte data type
server = grp.getvalue('1/6/2'),
-- smtp server port, 2 byte unsigned data type
port = grp.getvalue('1/6/3'),


}

local smtp = require('socket.smtp')

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

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

-- escape double quotes in file name
filename = filename:gsub('"', '\\"')

-- message headers and body
settings.source = smtp.message({
headers = {
to = table.concat(to, ', '),
subject = subject,
},
body = {
{
headers = {
['Content-Type'] = 'text/html; charset=utf-8',
},
body = mime.eol(0, message)
},
{
headers = {
['Content-Type'] = mimetype or 'text/plain',
['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
['Content-Transfer-Encoding'] = 'BASE64',
},
body = ltn12.source.chain(
ltn12.source.string(filedata),
ltn12.filter.chain(mime.encode('base64'), mime.wrap())
)
}
}
})

-- fixup from field
settings.from = '<' .. tostring(settings.from) .. '>'
settings.rcpt = to

return smtp.send(settings)
end

RE: export csv - Erwin van der Zwart - 22.04.2017

Hi,

To send to multiple addresses you can simply use a table.

to = {'mail1@domain.com', 'mail2@domain.com', 'mail3@domain.com'}

BR,

Erwin

RE: export csv - akn - 24.04.2017

Thank you, but I want то sо that users can change the settings themselves.

For example:

-- "to" field, 255 byte data type
to1 = grp.getvalue('1/6/3')
-- "to" field, 255 byte data type
to2 = grp.getvalue('1/6/4')
-- "to" field, 255 byte data type
to3 = grp.getvalue('1/6/5')

to = {to1, to2, to3}

But it does not work!
Thank you, but I want sо that users can change the settings themselves.

For example:

-- "to" field, 255 byte data type
to1 = grp.getvalue('1/6/3')
-- "to" field, 255 byte data type
to2 = grp.getvalue('1/6/4')
-- "to" field, 255 byte data type
to3 = grp.getvalue('1/6/5')

to = {to1, to2, to3}

But it does not work!

RE: export csv - Erwin van der Zwart - 24.04.2017

Hi,

Just tested your sample and it works for me, did you try to log the table 'to' ?

BR,

Erwin

RE: export csv - akn - 26.04.2017

Now it works!

--------- Event-based----------------

-- read csv report file
data = storage.get('csv')

-- "to" field, 255 byte data type
to1 = grp.getvalue('1/6/3')
-- "to" field, 255 byte data type
to2 = grp.getvalue('1/6/4')
-- "to" field, 255 byte data type
to3 = grp.getvalue('1/6/5')

res, err = mailattach('subject', 'CSV file attached', 'report.csv', data, 'text/csv')

--log(res, err)



--------- Common functions ----------------


function mailattach (subject, message, filename, filedata, mimetype)

local settings = {

-- "from" field, 255 byte data type
from = grp.getvalue('1/6/1'),
-- smtp server, 255 byte data type
server = grp.getvalue('1/6/2'),
-- smtp server port, 2 byte unsigned data type
port = grp.getvalue('1/6/3'),

}


local smtp = require('socket.smtp')

to = {to2, to1, to3}

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

-- escape double quotes in file name
filename = filename:gsub('"', '\\"')

-- message headers and body
settings.source = smtp.message({
headers = {
to = table.concat(to, ', '),
subject = subject,
},
body = {
{
headers = {
['Content-Type'] = 'text/html; charset=utf-8',
},
body = mime.eol(0, message)
},
{
headers = {
['Content-Type'] = mimetype or 'text/plain',
['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
['Content-Transfer-Encoding'] = 'BASE64',
},
body = ltn12.source.chain(
ltn12.source.string(filedata),
ltn12.filter.chain(mime.encode('base64'), mime.wrap())
)
}
}
})

-- fixup from field
settings.from = '<' .. tostring(settings.from) .. '>'
settings.rcpt = to

return smtp.send(settings)
end

RE: export csv - akn - 22.08.2017

I have a problem sending to gmail.com, I receive back error.
I d'not have problem with another email address!

----
This is the mail system at host mail.name.com.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.

The mail system

<name@gmail.com>: host gmail-smtp-in.l.google.com[74.125.131.26] said:
550-5.7.1 [82.193.64.9 11] Our system has detected that this message
is not 550-5.7.1 RFC 5322 compliant: 550-5.7.1 'From' header is missing.
550-5.7.1 To reduce the amount of spam sent to Gmail, this message has been
550-5.7.1 blocked. Please visit 550-5.7.1
https://support.google.com/mail/?p=RfcMessageNonCompliant 550 5.7.1 and
review RFC 5322 specifications for more information. 16si5852631lfb.292 -
gsmtp (in reply to end of DATA command)

Final-Recipient: rfc822; name@gmail.com
Original-Recipient: rfc822;name@gmail.com
Action: failed
Status: 5.7.1
Remote-MTA: dns; gmail-smtp-in.l.google.com
Diagnostic-Code: smtp; 550-5.7.1 [82.193.64.9 11] Our system has detected
that this message is not 550-5.7.1 RFC 5322 compliant: 550-5.7.1 'From'
header is missing. 550-5.7.1 To reduce the amount of spam sent to Gmail,
this message has been 550-5.7.1 blocked. Please visit 550-5.7.1
https://support.google.com/mail/?p=RfcMessageNonCompliant 550 5.7.1 and
review RFC 5322 specifications for more information. 16si5852631lfb.292 -
gsmtp
---

RE: export csv - admin - 24.08.2017

Add From header after first Content-Type like this:
Code:
['Content-Type'] = 'text/html; charset=utf-8',
['From'] = '<' .. tostring(settings.from) .. '>',


RE: export csv - akn - 25.08.2017

(24.08.2017, 10:05)admin Wrote: Add From header after first Content-Type like this:
Code:
['Content-Type'] = 'text/html; charset=utf-8',
['From'] = '<' .. tostring(settings.from) .. '>',



Does not help! 
I correctly added line in the script?



-- user function library


function mailattach (subject, message, filename, filedata, mimetype)

  local settings = {
 
    -- "from" field, 255 byte data type
    from = grp.getvalue('7/7/3'),
    -- smtp server, 255 byte data type
    server = grp.getvalue('7/7/4'),
    -- smtp server port, 2 byte unsigned data type
    port = grp.getvalue('7/7/5'),
      
  }
  
 
    local smtp = require('socket.smtp')

--if type(to) then
  to = {to1, to2}
--end

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

  -- escape double quotes in file name
  filename = filename:gsub('"', '\\"')

  -- message headers and body
  settings.source = smtp.message({
    headers = {
     to = table.concat(to, ', '),   
      subject = subject,
    },
    body = {
      {
        headers = {
          ['Content-Type'] = 'text/html; charset=utf-8',
          ['From'] = '<' .. tostring(settings.from) .. '>',  
        },
        body = mime.eol(0, message)
      },
      {
        headers = {
          ['Content-Type'] = mimetype or 'text/plain',
          ['Content-Disposition'] = 'attachment; filename="' .. filename .. '"',
          ['Content-Transfer-Encoding'] = 'BASE64',
        },
        body = ltn12.source.chain(
          ltn12.source.string(filedata),
          ltn12.filter.chain(mime.encode('base64'), mime.wrap())
        )
      }
    }
  })

  -- fixup from field
  settings.from = '<' .. tostring(settings.from) .. '>'
  settings.rcpt = to

  return smtp.send(settings)
end