Lua - Retrieve all second from last values from an inconsistent CSV file-CodePudding

I have a csv file (DailyEnergyCost.txt) that has many different variations of line entries, but it’s only the first and second from last values I want to retrieve. So I can plot them on a graph

2022-03-14 23:59:00, 23.51, 6.21, 0.264,
2022-03-15 23:59:00, 21.74, 5.74, 0.264,
2022-03-16 23:59:00, 18.87, 4.98, 0.264,
2022-03-23 09:00:37, 145.79, 38.49, 0.264,
2022-03-23 09:06:44, 3210.2370, 3210.5250, 0.29, 0.08, 0.264, 
2022-03-23 23:59:00, 3210.5250, 3224.2470, 13.72, 3.62, 0.264, 
2022-03-29 23:59:00, 1648508340, 1648594740, 3322.4630, 3343.3360, 20.87, 5.51, 0.264, 
2022-03-30 23:59:00, 1648594740, 1648681140, 3343.3360, 3365.2770, 21.94, 5.79, 0.264, 
2022-03-31 23:59:00, 1648681140, 1648767540, 3365.2770, 3395.7930, 30.52, 8.06, 0.264,

Now I’ve tried to do work out the logic of getting the last but one entry, using one line from the csv above in an array.. see below.. but that only works to a point.

local arr = {3210.5250, 3224.2470, 13.72, 3.62, 0.264}
for i,v in pairs(arr) do
    last = #arr - 1
end
print(last)

I also know there is the lines option too, to allow me to work my way through the file line by line ..

local file = io.open("www/DailyEnergyCost.txt")
local lines = file:lines()
for line in lines do
    print("\t" .. line)
end

But, please could someone share how you work line by line through the csv file that does not have consistent entries to extract the last but one entry?

Using the data source example as a guide above , the resulting Lua script should return the following. (First item and seconded from last).

2022-03-14 23:59:00, 6.21
2022-03-15 23:59:00, 5.74
2022-03-16 23:59:00, 4.98
2022-03-23 09:00:37, 38.49
2022-03-23 09:06:44, 0.08
2022-03-23 23:59:00, 3.62
2022-03-29 23:59:00, 5.51
2022-03-30 23:59:00, 5.79
2022-03-31 23:59:00, 8.06

CodePudding user response：

read the entire file and use the pattern to take values:

local fp = io.open("www/DailyEnergyCost.txt","rb")
local text = fp:read('*a')
print(text)
local time, value
for line in text:gmatch("[^%c] ") do
  time = line:match("^(.-),")
  value = line:match(". ,%s (.-),.-,") or ""
  print(time .. ", " .. value)
end

CodePudding user response：

here is the code I used in the end, huge thanks to @Mike V for his help on this..

local fh,err = io.open("www/DailyEnergyCost.txt")
    if err then print("file not found"); return; end

-- Open a file for write
local fho,err = io.open("/mnt/nas/DailyEnergyCostExtract.txt","wb")

local time, value

while true do
    line = fh:read()
        if line == nil then break end
        if line:gmatch("[^%c] ") then
            time = line:match("^(.-),")
            value = line:match(". ,%s (.-),.-,") or ""
            -- print(time .. ", " .. value)
            fho:write(time .. ", " .. value)
            fho:write("\n")
        end
    end

fh:close()
fho:close()

-- Let’s just check the file.

local file = io.open("/mnt/nas/DailyEnergyCostExtract.txt")
local lines = file:lines()
for line in lines do
    print("\t" .. line)
end