Convert a CSV file into a table with defined keys in Lua

Question

I'm learning Lua to build scripts for a flight simulator.

I have a CSV file that appear like this:

Poti city, Poti,red,-295731.42857144,617222.85714285  
Lanchhuti city, Poti,red,-299217.14285715,647851.42857142  
Ozurgeti city, Poti,red,-317217.14285715,648422.85714285  
Samtredia city, Poti,red,-287502.85714287,672022.85714285  
Abasha city, Poti,red,-284245.71428573,661108.57142857

Each lines contain 5 fields (city, region, coalition, coordinate-x and coordinate-y in simulator coordinates reference system).

I need to read this file as a lua table that should looks like this:

citylist = {
           [1]
           {
              ["city"] = "Poti city",
              ["region"] = "Poti",
              ["coalition"] = "red",
              ["coordinate-x"] = -295731.42857144,
              ["coordinate-y"] = 617222.85714285,
           },
           [2]
           { ....... etcetc

}

I also have to do this for some different csv files, and I tried to look to some CSV function from luawiki, but I honestly do not understood much. Can you please write down an example of code that should work to parse the CSV file as expected?

PS: there are never any ' " ' character in the csv files.

EDIT + ADD

ok the code from answer 1 seems to work but doesn't seems to build a table like above. As a newbie, I'll explain as best as I can.

I tried to parse this text:

123,Poty city,Poti,red,-295731.42857144,617222.85714285
124,Lanchhuti city,Poti,red,-299217.14285715,647851.42857142
125,Ozurgeti city,Poti,red,-317217.14285715,648422.85714285
126,Samtredia city,Poti,red,-287502.85714287,672022.85714285

with this code:

do  
    local OLo = io.open(lfs.writedir() .. "Logs/" .. "Objectivelist.txt", "r")
    local Objectivelist = {}
    for line in io.lines(OLo) do
        local objID, objName, objRegion, objCoalition, objCoordx, objCoordy = line:match("%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-)")
        Objectivelist[#Objectivelist + 1] = { ["objID"] = objID, ["objName"] = objName, ["objRegion"] = objRegion, ["objCoalition"] = objCoalition, ["objCoordx"] = objCoordx, ["objCoordy"] = objCoordy }
    end
end

and then try to rebuild a little differe output for debug with this code:

do
    local fName = "DGWS-DEBUG-objectivelist.doc"
    local f = io.open(lfs.writedir() .. "Logs/" .. fName, "w")
    local debugOBJ = ""

    for ind, objData in pairs(Objectivelist) do
        debugOBJ = debugOBJ.. objData.objID .. "," .. objData.objName .. "," .. objData.objRegion .. "," .. objData.objCoalition .. "," .. objData.objCoordx .. "," .. objData.objCoordy .. "\n"
    end

    f:write(debugOBJ)

end 

well, it gives error in "for ind, objData in pairs(Objectivelist) do" line, saying that expect a table and got nil.

I don't understand where is the error :(, can you help me?.

thanks :)

PS: I don't know match codes, but I'll try to learn how to do stricter check for values as soon as the intended code will work :)

Mackenie

Answer 1

You can split the CSV file yourself. Read the file and use the pattern matching to get each field, then store the fields into a table.

local citylist = {}
for line in io.lines("citys.csv") do
    local city, region, coalition, coordinate_x, coordinate_y = line:match("%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-)")
    citylist[#citylist + 1] = { city = city, region = region, coalition = coalition, coordinate_x = coordinate_x, coordinate_y = coordinate_y }
end

In real code, you may use a stricter pattern. For instance, check the values of coordinates are really numbers.

Rapacious

I’d go for the simpler pattern "%s*(.-),%s*(.-),%s*(.-),%s*(.-),%s*(.-)”. – Sweltering

@Sweltering That's definitely better, I changed the answer to use your pattern. – Rapacious

You can also write citylist[#citylist + 1] = { city = city, region = region, …} and save 5 calls to #. – Sweltering

@Sweltering I thought my previous solution was not that Lua-style, but I failed to find out why, now you taught me. :) – Rapacious

Thank you a lot, I'll try that this evening :) – Mackenie

Added "EDIT+ADD" section to the question, sadly the code seems to do not work as intended and I'm unable to debug it (I'm trying in the last 2 days whitout success) – Mackenie

@Mackenie Try removing the do end outside the first part of the code. It seems to me that Objectivelist is a local variable inside that do end block, but it's out of scope when you iterate it in the second part. – Rapacious

wtf how couldn't I saw that? I think you're right. I'll try this evening, thanks. (what a newbie I am :( ) – Mackenie

ok, block removal has moved the error one line down: now it doesn't find any of the field of the inner table (starting from the last, objCoordy). – Mackenie

I think the last entry of the search pattern should be (.*) instead of (.-), or the last entry on the line won't be picked up – Merat

Answer 2

If you're just prototyping, then you can use my fork of Shan Carter's Mr. Data Converter -- I recently added Lua support.

http://thdoan.github.io/mr-data-converter/

You can input CSV or tab-delimited data and it will output two different types of Lua tables: dictionary table and array table (your example).

Monitor