Lua - read a csv file, update some values (calculations) and write new file

Question

I’ve been writing values to a text/csv file, but as certain information has now changed, I need to update the file to reflect the new values/calculations.

The text/csv file is a report on energy used, sample below, and it’s the KwH Unit Cost and Cost (which is calculated from the KWH Used * KwH Unit Cost) that both need updating.

Date Time Run,  Start Epoc,  End Epoc, KwH Start, KwH End, KwH Used, KwH Unit Cost, Cost Spent
2022-12-11 13:12:53, 1670763866, 1670764373, 7841.1690, 7841.2920, 0.12, 0.3506, 0.04, 
2022-12-11 23:59:00, 1670764373, 1670803140, 7841.2920, 7853.5480, 12.26, 0.3506, 4.3, 

I’m able to parse the csv/text file to a table, but I’m unable to work out how to extract update the unit cost from 0.3506 to 0.3877 and recalculate the Cost based on that new rate.

My Lua code is below, which shows the cost values. That’s as far as I’m able to go ..

local CSV = [[Date Time Run,  Start Epoc,  End Epoc, KwH Start, KwH End, KwH Used, KwH Unit Cost, Cost Spent
2022-12-11 13:12:53, 1670763866, 1670764373, 7841.1690, 7841.2920, 0.12, 0.3506, 0.04, 
2022-12-11 23:59:00, 1670764373, 1670803140, 7841.2920, 7853.5480, 12.26, 0.3506, 4.3, 
2022-12-12 23:59:00, 1670803140, 1670889540, 7853.5480, 7887.0740, 33.53, 0.3506, 11.76, 
2022-12-13 23:59:00, 1670889540, 1670975940, 7887.0740, 7917.4370, 30.36, 0.3506, 10.64, ]]

local function tprint (tbl, indent)
  if not indent then indent = 0 end
  for k, v in pairs(tbl) do
    formatting = string.rep("  ", indent) .. k .. ": "
    if type(v) == "table" then
      print(formatting)
      tprint(v, indent+1)
    elseif type(v) == 'boolean' then
      print(formatting .. tostring(v))      
    else
      print(formatting .. v)
    end
  end
end

local linePattern = "[^\r\n]+"
local csWordPattern = "[^,]+"

local function parseCsv(csv)
    local rows = {}
    for line in string.gmatch(csv, linePattern) do
        local row = {}
        for word in string.gmatch(line,csWordPattern) do
            table.insert(row, word)
        end
        table.insert(rows, row)
    end

    return rows
end

local CSV_Table = parseCsv(CSV)
print(tprint(CSV_Table))

for k,v in pairs(CSV_Table) do
    --print (k,v)
    for k1,v1 in pairs(v) do 
        --print (k1,v1)
        if k1 == 8 then 
            print(v1)
        end
    end
end

Any/all help greatly appreciated..

Answer 1

I managed to get my code to work, here’s what I used in the end..

local CSV = [[Date Time Run,  Start Epoc,  End Epoc, KwH Start, KwH End, KwH Used, KwH Unit Cost, Cost Spent
2022-12-11 13:12:53, 1670763866, 1670764373, 7841.1690, 7841.2920, 0.12, 0.3506, 0.04, 
2022-12-11 23:59:00, 1670764373, 1670803140, 7841.2920, 7853.5480, 12.26, 0.3506, 4.3, 
2022-12-12 23:59:00, 1670803140, 1670889540, 7853.5480, 7887.0740, 33.53, 0.3506, 11.76, 
2022-12-13 23:59:00, 1670889540, 1670975940, 7887.0740, 7917.4370, 30.36, 0.3506, 10.64, ]]

local linePattern = "[^\r\n]+"
local csWordPattern = "[^,]+"

local function parseCsv(csv)
    local rows = {}
    for line in string.gmatch(csv, linePattern) do
        local row = {}
        for word in string.gmatch(line,csWordPattern) do
            table.insert(row, word)
        end
        table.insert(rows, row)
    end
    return rows
end

local data = parseCsv(CSV)
local newarr = {}
for i,v in pairs(data) do
    local rate = 0.386
    local usage = tonumber(v[6])
    if type(usage ) == "number" then
        local cost = (usage * rate)
        table.insert(newarr, {v[1], v[2], v[3], v[4], v[5], v[5], v[6], rate, cost})
    else
        table.insert(newarr, {v[1], v[2], v[3], v[4], v[5], v[5], v[6], v[7], v[8]})
    end
end

for k, v in pairs(newarr) do
    print(table.concat(v,", ")) 
end