Read CSV file from LM/SL FTP device folder and put data to groups

[AlexLV]

Hello,

Some system reading info from M-Bus different counters and send data daily to different DB in .CSV format.

If we save .csv fo local FTP folder, can we use LM/SL to read .csv and show data from file?

Idea - read counter's data from .csv file, separate it and than show every counter's data at visualization. When new file is received (i think to local FTP folder), data should be extracted and updated at visualization. Old file (files) should be deleted in FTP folder.

I am waiting real .csv file with counter's data, but need way where to look to start investigate will this solution possible to build or not.

So, how we can read data from .csv fie and put them to groups?

BR,

Alexander

[admin]

This can be used a starting point:

Code:

-- FTP directory absolute path
dir = '/home/ftp/'
-- get file list
files = io.ls(dir)

-- go through all files
for _, file in ipairs(files) do
  -- full file path
  path = dir .. '/' .. file
  -- read file data
  data = io.readfile(path)
  -- split into lines
  lines = data:split('\n')
  -- remove CSV header
  table.remove(lines, 1)

  -- go through all lines
  for _, line in ipairs(lines) do
    -- split each line into separate values
    values = line:trim():split(',')
    log(values)
  end

  -- remove file
  os.remove(path)
end

[AlexLV]

admin,

Super, thank you! It works, in log I see my data from my .csv file.

Some new questions appears:

1. My file in named: report-2021-05.csv Is possible to read data just from last received file named "report" , but not from all files? If any other files are located in this directory, script reads data from all of them.
Also is possible to start reading script just when new file appeared in FTP folder name started "report"?
2. Is possible somehow read date and time of file appeared in FTP folder?
3. How put data from received table to groups? In log data shown such way (just 2 rows shown from whole table):

FTP check 08.05.2021 17:25:19
* table:
[1]
* string: 0
[2]
* string: 1
[3]
* string: 322
[4]
* string: 2015.330
[5]
* string: 2202.530
FTP check 08.05.2021 17:25:19
* table:
[1]
* string: 0
[2]
* string: 1
[3]
* string: 322
[4]
* string: 6.900
[5]
* string: 6.900

4. os.remove(path) removes all files in my directory. I think I should add file name for path = dir .. '/' .. file. How can I do it? This way? : file = 'report*.csv' Can I use file mask here? (All files name started from report should be read and than deleted in this case?

5. If many files are in this directory, in which order files will be read by script above? Alphabetically?

BR,

Alexander

[admin]

1. If the script is working properly then you should have only one file before it runs. Since you have meter values that cannot become smaller you can check if the current object value is smaller than the new value before writing:

Code:

value = tonumber(...)
addr = ...
if value and value > grp.getvalue(addr) then
  grp.write(addr, value)
end

2. size, mtime = io.stat(path) where mtime is file modification timestamp, it can be converted to any date/time format using os.date(fmt, mtime)

3. You need to add grp.write() calls yourself after values = line:trim():split(','), values[1] is the first CSV row entry, values[2] is the second and so on

4. Each os.remove(path) removes a single file, path variable already contains full path (dir .. '/' .. file)

5. There's no defined order, you can add table.sort(files) after files = io.ls(dir) to sort the files table before going through all files

[AlexLV]

admin,

thank you, now is clear the way what to do. Some more questions regarding point Nr. 3.

a. I can manually calculate how many rows with data I have in my table, but can I do somehow group numbering automatically as example starting from 32/1/1 and put there values from table?
b. How possible to check how many lines are in the table before data reading and writing to groups? I need such checking in case if new counter's data are added to .csv file if compare new file with previous received file, and after that add new groups for new data.

c. How periodically check that new file named "report" is added to ftp folder?

Alex