CSV Export details
#1
Dear all,
before writing, I read all post about this thread but no post clearify what I would like to do.

I would like to export and send by mail all my csv files log. After that I would like to delate the csv file in order to keep the memory clean and light.

What do you suggest? Furthermore I do not konw how I can clean memory of trend manually? 

Thank you for your help.

Regards.
Reply
#2
In some cases you don't need to store the file at all, just generate it in your script and send via e-mail as attachment.

Trends cannot be "cleaned". Database is allocated to support all data at once, so even trend without any data still has the maximum size depending on selected data range.
Reply
#3
Ok, so what' your suggestion about it?
What's a script the generate the file?
Thanks.
Reply
#4
Hi,

Try this, the attachment is never saved:

.lua   Mail 1 year of trenddata with hourly data points version 1_2.lua (Size: 6.18 KB / Downloads: 10)

BR,

Erwin
Reply
#5
(14.02.2018, 00:04)Erwin van der Zwart Wrote: Hi,

Try this, the attachment is never saved:


BR,

Erwin

Hi Erwing,
I studied the script and I cannot understand the interval. This is the mail of one year of trend?

I would like to set the interval as I want just for test. How can do that? Example today from 5 pm to 6 pm.

Thanks.

Ok, I create the script as scheduled.

As start date and end date I use 19.02.2018 (today) from 17.25 to 18.00.

I received the error enclosed.


What do you suggest?

Thanks.


Attached Files Image(s)
   
Reply
#6
Hi,

See this article on how to set start - end range on trend fetching by using UNIX format timestamps:

http://www.openrb.com/docs/trends-new.htm

I don't think you can fetch smaller data set then 1 day, you have to get the values from the result yourself, when the trend resolution is 5 minutes you get a set of 288 points, then each 12 points are equal to 1 hour, so the points from 15:00 to 16:00 will be data[180] to data[191], when the trend resolution is set to 1 hour the point will be data[15]

BR,

Erwin
Reply
#7
(19.02.2018, 19:01)Erwin van der Zwart Wrote: Hi,

See this article on how to set start - end range on trend fetching by using UNIX format timestamps:

http://www.openrb.com/docs/trends-new.htm

I don't think you can fetch smaller data set then 1 day, you have to get the values from the result yourself, when the trend resolution is 5 minutes you get a set of 288 points, then each 12 points are equal to 1 hour, so the points from 15:00 to 16:00 will be data[180] to data[191], when the trend resolution is set to 1 hour the point will be data[15]

BR,

Erwin

Hi Erwin,
I modified start and end date. Now error is the one shown in attchment at line 133. What does it mean?
Thanks.


Attached Files Image(s)
   
Reply
#8
Hi,

Not sure but i think there is no data in your fetched result. You probably have set the scope to narrow. Have you logged the raw result from the fetch command?

BR,

Erwin
Reply
#9
Consider that this error is shown when I launch the script but the file should be sent in the next 12 hours as per my setting. Or this error stop the script?
Reply
#10
Please check this post:
https://forum.logicmachine.net/showthread.php?tid=1216

You can find there some additional info about current trend API.
Reply
#11
Thank you buuuudzik!

I use your scritp and it works. But I would like to do is save data in a CSV file and sending it by mail. I cannot mix your script with the one suggest by Erwin.

Thanks.
Reply
#12
(20.02.2018, 11:37)Domoticatorino Wrote: Thank you buuuudzik!

I use your scritp and it works. But I would like to do is save data in a CSV file and sending it by mail. I cannot mix your script with the one suggest by Erwin.

Thanks.

Yes, I understand but maybe you can use same start and end in your script. You can also log every single step to find what is the reason of nil in the result.
Reply
#13
Code:
--**************************************************************************--
--** Mail year hour data as CSV attachment created by Erwin van der Zwart **--
--***************** For W4K from FW 2.0 and SL from FW 2.0 *****************--
--********* Running this script takes a while, pleace be patience **********--
--**************************************************************************--
--*************************** Start of parameters **************************--
-- Version 1.2 *************************************************************--

--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 = {
 'Temperatura - Giorno'
}

-- Set Gmail address
gmail_address = 'xxx'

-- Set Gmail password
gmail_password = 'xxxx'

-- Set recipients
to = {'claudio@domoticatorino'}

-- Set subject
subject = 'Trend reports from Schneider Electric'

-- Set Message
message = 'These are the Trend reports from project: Schneider Electric'

-- Set logging
logging = true

--**************************************************************************--
--**************************** End of parameters ***************************--
--**************************************************************************--
--****************** DON'T CHANGE ANYTHING UNDER THIS LINE *****************--
--**************************************************************************--

require('trends')

-- Get all trend names from DB
trends_table = db:getall('Temperatura - Giorno')

-- 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, '""')

-- Get current timestamp
timestamp = os.time()
startpoint = os.date('*t', timestamp - 1) -- reduce data with 1 day
endpoint = os.date('*t', timestamp) -- reduce data with 1 day

-- Set startpoint time from 00:00:00
startpoint.sec = 0
startpoint.min = 0
startpoint.hour = 0

-- Set endpoint time to 23:59:59
endpoint.sec = 59
endpoint.min = 59
endpoint.hour = 23

-- Create data selection range
dates = {}
dates['start'] = {year = 2018, month = 2, day = 1}
dates['end'] = {year = 2018, month = 2, day = 18}

-- Set resolution to dayly data
--resolution = 86400

-- Set resolution to hourly data
--resolution = 3600

-- Set resolution to 5 min data (288)
resolution = 288

-- Creat 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, '""')

-- 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 data from trend
 trenddata = trends.fetch(trend_name, dates, resolution)
 
 -- Create CSV data from table content
 for _, row in ipairs(trenddata) do
   
   -- format csv row
   csv = string.format('datapoint ' .. _  .. ',' .. row)
   
   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 result only when there's data in buffer
if #buffer > 1 then
 result = table.concat(buffer, '\r\n')
end
log(result)

-- make sure these settings are correct
local settings = {
 -- "from" field, only e-mail must be specified here
 from = 'claudio@domoticatorino.com',
 -- smtp username
 user = 'claudio@domoticatorino.com',
 -- smtp password
 password = 'cla09domo',
 -- smtp server
 server = 'smtp.gmail.com',
 -- smtp server port
 port = 465,
 -- enable ssl, required for gmail smtp
 secure = 'sslv23',
}

--Load required modules to send email with attachment
local smtp = require("socket.smtp")
local mime = require("mime")
local ltn12 = require("ltn12")

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

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

mime = require("mime")
fileString = mime.b64(result)

-- message headers and body
settings.source = smtp.message({
 headers = {
   to = table.concat(to, ', '),
   subject = subject,
 },
 --Load attachment inside body    
 body = {
   preamble = "",
   [1] = {  
       headers = {
         ["content-type"] = 'text/csv',
       ["content-disposition"] = 'attachment; filename="trends.csv"',
       ["content-description"] = 'trends',
       ["content-transfer-encoding"] = "BASE64",
     },
        body = fileString
   },
   [2] = {  
       body = '\n' .. message
   },
   epilogue = ""
 }
})

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

--Send the email
r, e = smtp.send(settings)

if logging == true then
 --Create alert when sending gives an error with error message
 if (e) then
   log("Could not send email: "  .. e)
 else
   log("Email is send")
 end
end

script.disable(_SCRIPTNAME)


Hi Erwing,
this is what I insert in the script. Now everything is good without mistake. But when I launch the script, log inform me that "No trends available, Could not export trends".

But trends 'Temperatura - Giorno' is present. What could be?

Thanks.
Reply
#14
Hi, 

Like the script says:


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

But you changed this line: 

trends_table = db:getall('Temperatura - Giorno') -- This is not a valid query

Set it back to original:

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

BR,

Erwin
Reply
#15
(20.02.2018, 22:32)Erwin van der Zwart Wrote: Hi, 

Like the script says:


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

But you changed this line: 

trends_table = db:getall('Temperatura - Giorno') -- This is not a valid query

Set it back to original:

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

BR,

Erwin

Hi Erwin,
I did it, but now I have the error shown.

What do you think?

Thanks.


Attached Files Image(s)
   
Reply
#16
You can find a solution in below post:
https://forum.logicmachine.net/showthrea...trends+api
Reply


Forum Jump: