CSV format

[jobb@nordstrandsel.se]

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,,,,,,,,,,,,,,,,,

[admin]

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.

[jobb@nordstrandsel.se]

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

[admin]

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)

[jobb@nordstrandsel.se]

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

[admin]

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 0

3. Add these new values to string.format. Each %.Xf outputs a number, where X is the number of decimal places.

[jobb@nordstrandsel.se]

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

[admin]

Replace csv = {} with this:

Code:

csv = { 'field1,field2,...' }

[jobb@nordstrandsel.se]

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