+- 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: CSV format (/showthread.php?tid=4964)
CSV format - jobb@nordstrandsel.se - 13.09.2023
Hi,
I use this script for sending log data to csv file.
The script make one new row for each value, but i nedd to have them in one row. how can i make this?
ids = {
['1'] = 'GENOMSNITTLIG VINDHASTIGHET',
['2'] = 'RELATIV LUFTFUKTIGHET',
}
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 = 'email@dot.com '
settings = {
-- "from" field, only e-mail must be specified here
from = 'email@dot.com',
-- smtp username
user = ' email@dot.com ',
-- smtp password
password = '****',
-- smtp server
server = 'smtp.gmail.com',
-- smtp server port
port = 465,
-- enable ssl, required for gmail smtp
secure = 'sslv23',
}
subject = 'CSV väder'
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)
This is the way WOW want the file for weather data shall aranged.
Id,Site Id,Site Authentication Key,Report Date / Time,Concrete Temp.,Day of Gales,Soil Temp. (at 10cm),Wet Bulb,Soil Temp. (at 30cm),Max. Temp. (last 24hr),Total Cloud Cover,Wind Gust,Day of Hail,Wind Gust Direction,Present Weather,Ground State,Soil Temp. (at 100cm),Grass Temp.,Sunshine,Day of Snow,Mean Sea-Level Pressure,Pressure (At Station),Relative Humidity ,Weather Diary,Rainfall Accumulation,Visibility,Min. Temp. (last 24hr),Wind Direction,Wind Speed,Air Temperature,Snow Depth,Soil Moisture,Dew Point,Day of Thunder,Rainfall Rate,Rainfall,Travel Disruption,Hazards causing Travel Disruption,Property or Infrastructure Damage,Hazards causing Property or Infrastructure Damage,Personal Health and Safety,Hazards causing Personal Health and Safety,Utility Disruption,Hazards causing Utility Disruption,Service or Business Disruption,Hazards causing Service or Business Disruption,Agriculture Habitat Damage,Hazards causing Agriculture Habitat Damage,Disruption to Camping Events Leisure Activities,Hazards causing Disruption to Camping Events Leisure Activities
Can be like this...
,,,15/06/2012 15:00,,,,,,,,,,202,100 - Cloudless sky (day),,,,1,,1017,,53,,0,7,,202,6.9,24.5,0,,14.2,,,,,,,,,,,,,,,,,
RE: CSV format - admin - 14.09.2023
You can set the third argument of trends.fetch to 86400 and this will produce a single daily average value instead of multiple values. But if you also need to calculate min/max then you need to perform this calculation on the data array.
RE: CSV format - jobb@nordstrandsel.se - 14.09.2023
(14.09.2023, 07:21)admin Wrote: You can set the third argument of trends.fetch to 86400 and this will produce a single daily average value instead of multiple values. But if you also need to calculate min/max then you need to perform this calculation on the data array.
Thank you for quick reply!
But my problem is the format of the csv-file.
Now it comes like below...
2023-09-13 14:51,1,18.4
2023-09-13 14:51,3,90
2023-09-13 14:46,1,18.4
2023-09-13 14:46,3,90.08
It has to be something like this...
,,, 2023-09-13 14:46,1,18.4,,,,90,,
,,, 2023-09-13 14:51,1,18.4,,,,90,,
/Fredrik
Here comes a link to WOW, explaining how to send data...
https://wow.metoffice.gov.uk/support/dataformats
/Fredrik
RE: CSV format - admin - 14.09.2023
Then you probably need something like this. For it to work correctly all trends included in the export must have the same resolution.
Code:
require('trends')
dates = {}
dates['start'] = os.date('*t')
dates['start'].day = dates['start'].day - 1
dates['end'] = os.date('*t')
now = os.time()
function fetch(tname)
local res = {}
local values = trends.fetch(tname, dates)
local count = #values
local step = 86400 / count
for i, value in ipairs(values) do
table.insert(res, {
now - (count - i) * step,
value
})
end
return res
end
buf = {
fetch('Trend name 1'),
fetch('Trend name 2')
}
csv = {}
for i, row1 in ipairs(buf[ 1 ]) do
date = os.date('%Y-%m-%d %H:%M', row1[ 1 ])
value1 = row1[ 2 ]
row2 = buf[ 2 ][ i ] or {}
value2 = row2[ 2 ] or 0
csv[ #csv + 1 ] = string.format(',,,%s,%.2f,,,,%.0f,,',
date, value1, value2)
end
csv = table.concat(csv, '\n')
log(csv)RE: CSV format - jobb@nordstrandsel.se - 14.09.2023
Thank you!
Now it works. But i need to add more values in the file. How can i add value3, value4 and so on?
/Fredrik
RE: CSV format - admin - 14.09.2023
1. Add more trend fetch calls to the buf table.
2. Add more rowX/valueX variables into the for loop:
Code:
row3 = buf[ 3 ][ i ] or {}
value3 = row3[ 3 ] or 03. Add these new values to string.format. Each %.Xf outputs a number, where X is the number of decimal places.
RE: CSV format - jobb@nordstrandsel.se - 14.09.2023
Thank you very much!
Now i can manage it as i want! :-)
/Fredrik
There is one more thing...
If i want to describe in first row the meaning of the values.
Eg Id,Site Id,Site Authentication Key,Report Date / Time,Concrete Temp.,Day of Gales,Soil Temp. (at 10cm),Wet Bulb,Soil Temp. (at 30cm),Max. Temp. (last 24hr) and so on...
/Fredrik
RE: CSV format - admin - 14.09.2023
Replace csv = {} with this:
Code:
csv = { 'field1,field2,...' }RE: CSV format - jobb@nordstrandsel.se - 14.09.2023
Many thanks!
Now there is an other posibility to send weather data via API as following.
Is it posible to do it like this?
Using the API directly
If you're a developer and would like to use the API directly, details are provided below.
Upload URL:
WOW expects an HTTP request, in the form of either GET or POST, to the following URL. When received, WOW will interpret and validate the information supplied and respond as below.
The URL to send your request to is: http://wow.metoffice.gov.uk/automaticreading? followed by a set of key/value pairs indicating pieces of data.
Response and Errors
All requests will return a status code. A success is indicated by 200. Anything else is a failure. A human readable error message will accompany all errors in JSON format.
Mandatory Information:
All uploads must contain 4 pieces of mandatory information plus at least 1 piece of weather data.
Site ID - siteid:
The unique numeric id of the site
Authentication Key - siteAuthenticationKey:
A pin number, chosen by the user to authenticate with WOW.
Date - dateutc:
Each observation must have a date, in the date encoding specified below.
Software Type - softwaretype
The name of the software, to identify which piece of software and which version is uploading data