What's the most convenient way to visually create a lua table?

[pirx]

Hi,

My game needs a static, read only table of game data.

Think a simple two-dimensional table with rows containing e.g. characters and columns containing their various features. The table is going to be rather large so inputting the data directly in code would be massively unwieldy.

I’d much rather make it in Excel or something and then convert to lua. The problem is, there doesn’t appear to be a straightforward way to do this.

I tried some chinese xls-to-lua converters on github but they are buggy or clunky to work with. Also I would prefer to avoid parsing json or csv if I can help it by just having the data straight up in LUA.

So the basic question is:

What’s the most convenient way to visually create a lua table?

[nick_sherman]

I would look into sql lite. It’s very easy to read a .csv into a table using an sql lite editor, then read each row of the table into a lua table.

Otherwise you could just use an excel formula to take data from the columns and organise it into lua table format. However very large sets of data may take a long time to load in your app.

[Alan_PlantPot]

I’ve never come across a good method for exporting directly from excel or a google doc, so I’ve always relied on json.  

Is there any reason you don’t want to use json, as it’s only a few lines of code to convert from json to a lua table?

[pirx]

@nick_sherman

I feel sql is a bit of an overkill for just a static table. I might be wrong though. If it has a negligible footprint on general efficiency I could look into that.

@Alan PlantPot

The thing is I then need to go Excel -> CSV -> JSON -> LUA. The table is going to be updated and tested frequently during the next weeks so I was hoping to eliminate as many chore-clicks (so to speak) as possible.

However, it seems then that there is no direct route. Would you care to point me to a simple json to lua function? I remember writing something like that some time ago but it was rather extensive (that’s why I was reluctant; it had a slightly different purpose though).

[nick_sherman]

As I said you can just go Excel -> Lua using a formula for each row of data, then just copy and paste this column into a Lua module.

Excel formula: 

=“data[#data+1] = { stringcolumn= '”&A1&"’, intcolumn = “&B1&”}"  – just add as many columns as you need to formula

Lua module:

[lua]

***data.lua**

local data = {}

– paste excel data here

return data

***app.lua***

local data = require(“data”)

[/lua]

Only thing you need to watch out for is if your strings have apostrophes. In that case I find/replace them all in the source data with an un-used character such as ‘@’. Then I paste as values the formatted data into the next column, find/replace single quotes with double quotes and then find/replace ‘@’ as single quotes.

[Alan_PlantPot]

Converting from json to lua is as simple as calling json.decode(theJsonString), but there is a little extra needed to load the json from a file in the first place:

function loadFromJson( filename, base ) -- set default base dir if none specified if not base then base = system.DocumentsDirectory; end -- create a file path for corona i/o local path = system.pathForFile( filename, base ) -- will hold contents of file local file, contents -- io.open opens a file at path. returns nil if no file found if path then file = io.open( path, "r" ) end if file then -- read all contents of file into a string contents = file:read( "\*a" ) io.close( file ) -- close the file after using it else --if not found then return a string representing an empty table contents = "[]" end --convert the json string to a table local returnTable = json.decode(contents) --return the table return returnTable end local myNewTable = loadFromJson("myJsonFile.json", system.ResourceDirectory)

I never use Excel so I’m not sure if/how you would export Excel to json, but I do use Google Sheets quite often and there is an extension for that to export directly to json:

https://chrome.google.com/webstore/detail/export-sheet-data/bfdcopkbamihhchdnjghdknibmcnfplk?hl=en

That would take the sequence down to: googledoc > json > lua. If the json to lua function is added to the start of your app setup you wouldn’t need to do anything manually, other than dropping the json file into your project.

[pirx]

@Alan PlantPot

Great, thank you! I like this solution, because the function reads directly from a text file – so there’s no point simplifying it any further.

@nick_sherman

That’s a really interesting bit of excel magic there. I will go with JSON in the end in this case but I can already think of a couple of uses for this formula as well.

[roaminggamer]

Update: I know you said you wanted to avoid CSV, but it is pretty simple and easy to read so…

If you don’t use commas in your data, you could just:

1. Use Excel or another editor to edit and store data.

2. Export to CSV.

3. Write a small importer to read csv and convert to table.

Using SSK 2 step 3 would look like this:

local function getTable( filename, baseFolder, firstRowAsFieldNames ) local csvData = io.readFileTable( filename, baseFolder ) -- for i = 1, #csvData do csvData[i] = string.trim(csvData[i]) end -- local t = {} -- -- First row of data is 'field names' for subsequent row's data if( firstRowAsFieldNames ) then local fieldNames = string.split(csvData[1],",") for i = 2, #csvData do local tmp = string.split(csvData[i],",") local tmp2 = {} for j = 1, #fieldNames do tmp2[fieldNames[j]] = tmp[j] end t[#t+1] = tmp2 end else for i = 1, #csvData do t[#t+1] = string.split(csvData[i],",") end end return t end local t = getTable( "testData.csv", system.ResourceDirectory, true ) table.print\_r(t) local t = getTable( "testData.csv", system.ResourceDirectory, false ) table.print\_r(t)

For the attached csv file:

name,age,health Bill,20,100 Bob,50,80 Sue,27,95

The output of the code would look like this…

first print_r

10:20:05.848 table: 05AD5210 { 10:20:05.848 [1] =\> table: 05AD5210 10:20:05.848 { 10:20:05.848 [health] =\> "100" 10:20:05.848 [age] =\> "20" 10:20:05.848 [name] =\> "Bill" 10:20:05.848 } 10:20:05.848 [2] =\> table: 05AD5210 10:20:05.848 { 10:20:05.848 [health] =\> "80" 10:20:05.848 [age] =\> "50" 10:20:05.848 [name] =\> "Bob" 10:20:05.848 } 10:20:05.848 [3] =\> table: 05AD5210 10:20:05.848 { 10:20:05.848 [health] =\> "95" 10:20:05.848 [age] =\> "27" 10:20:05.848 [name] =\> "Sue" 10:20:05.848 } 10:20:05.848 }

second print_r

10:20:05.848 table: 05AD5800 { 10:20:05.848 [1] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "name" 10:20:05.848 [2] =\> "age" 10:20:05.848 [3] =\> "health" 10:20:05.848 } 10:20:05.848 [2] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "Bill" 10:20:05.848 [2] =\> "20" 10:20:05.848 [3] =\> "100" 10:20:05.848 } 10:20:05.848 [3] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "Bob" 10:20:05.848 [2] =\> "50" 10:20:05.848 [3] =\> "80" 10:20:05.848 } 10:20:05.848 [4] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "Sue" 10:20:05.848 [2] =\> "27" 10:20:05.848 [3] =\> "95" 10:20:05.848 } 10:20:05.848 }

[pirx]

@roaminggamer

Great! I will try that. It does exactly what I wanted.

[roaminggamer]

Tip:   You may notice that all values are treated as strings. 

For the ‘first row as field names’ part of the solution, you can modify the code so it tries to force numbers to be numbers as follows:

replace this:

tmp2[fieldNames[j]] = tmp[j]

with this:

tmp2[fieldNames[j]] = tonumber(tmp[j]) or tmp[j]

[nick_sherman]

I would look into sql lite. It’s very easy to read a .csv into a table using an sql lite editor, then read each row of the table into a lua table.

Otherwise you could just use an excel formula to take data from the columns and organise it into lua table format. However very large sets of data may take a long time to load in your app.

[Alan_PlantPot]

I’ve never come across a good method for exporting directly from excel or a google doc, so I’ve always relied on json.  

Is there any reason you don’t want to use json, as it’s only a few lines of code to convert from json to a lua table?

[pirx]

@nick_sherman

I feel sql is a bit of an overkill for just a static table. I might be wrong though. If it has a negligible footprint on general efficiency I could look into that.

@Alan PlantPot

The thing is I then need to go Excel -> CSV -> JSON -> LUA. The table is going to be updated and tested frequently during the next weeks so I was hoping to eliminate as many chore-clicks (so to speak) as possible.

However, it seems then that there is no direct route. Would you care to point me to a simple json to lua function? I remember writing something like that some time ago but it was rather extensive (that’s why I was reluctant; it had a slightly different purpose though).

[nick_sherman]

As I said you can just go Excel -> Lua using a formula for each row of data, then just copy and paste this column into a Lua module.

Excel formula: 

=“data[#data+1] = { stringcolumn= '”&A1&"’, intcolumn = “&B1&”}"  – just add as many columns as you need to formula

Lua module:

[lua]

***data.lua**

local data = {}

– paste excel data here

return data

***app.lua***

local data = require(“data”)

[/lua]

Only thing you need to watch out for is if your strings have apostrophes. In that case I find/replace them all in the source data with an un-used character such as ‘@’. Then I paste as values the formatted data into the next column, find/replace single quotes with double quotes and then find/replace ‘@’ as single quotes.

[Alan_PlantPot]

Converting from json to lua is as simple as calling json.decode(theJsonString), but there is a little extra needed to load the json from a file in the first place:

function loadFromJson( filename, base ) -- set default base dir if none specified if not base then base = system.DocumentsDirectory; end -- create a file path for corona i/o local path = system.pathForFile( filename, base ) -- will hold contents of file local file, contents -- io.open opens a file at path. returns nil if no file found if path then file = io.open( path, "r" ) end if file then -- read all contents of file into a string contents = file:read( "\*a" ) io.close( file ) -- close the file after using it else --if not found then return a string representing an empty table contents = "[]" end --convert the json string to a table local returnTable = json.decode(contents) --return the table return returnTable end local myNewTable = loadFromJson("myJsonFile.json", system.ResourceDirectory)

I never use Excel so I’m not sure if/how you would export Excel to json, but I do use Google Sheets quite often and there is an extension for that to export directly to json:

https://chrome.google.com/webstore/detail/export-sheet-data/bfdcopkbamihhchdnjghdknibmcnfplk?hl=en

That would take the sequence down to: googledoc > json > lua. If the json to lua function is added to the start of your app setup you wouldn’t need to do anything manually, other than dropping the json file into your project.

[pirx]

@Alan PlantPot

Great, thank you! I like this solution, because the function reads directly from a text file – so there’s no point simplifying it any further.

@nick_sherman

That’s a really interesting bit of excel magic there. I will go with JSON in the end in this case but I can already think of a couple of uses for this formula as well.

[roaminggamer]

Update: I know you said you wanted to avoid CSV, but it is pretty simple and easy to read so…

If you don’t use commas in your data, you could just:

1. Use Excel or another editor to edit and store data.

2. Export to CSV.

3. Write a small importer to read csv and convert to table.

Using SSK 2 step 3 would look like this:

local function getTable( filename, baseFolder, firstRowAsFieldNames ) local csvData = io.readFileTable( filename, baseFolder ) -- for i = 1, #csvData do csvData[i] = string.trim(csvData[i]) end -- local t = {} -- -- First row of data is 'field names' for subsequent row's data if( firstRowAsFieldNames ) then local fieldNames = string.split(csvData[1],",") for i = 2, #csvData do local tmp = string.split(csvData[i],",") local tmp2 = {} for j = 1, #fieldNames do tmp2[fieldNames[j]] = tmp[j] end t[#t+1] = tmp2 end else for i = 1, #csvData do t[#t+1] = string.split(csvData[i],",") end end return t end local t = getTable( "testData.csv", system.ResourceDirectory, true ) table.print\_r(t) local t = getTable( "testData.csv", system.ResourceDirectory, false ) table.print\_r(t)

For the attached csv file:

name,age,health Bill,20,100 Bob,50,80 Sue,27,95

The output of the code would look like this…

first print_r

10:20:05.848 table: 05AD5210 { 10:20:05.848 [1] =\> table: 05AD5210 10:20:05.848 { 10:20:05.848 [health] =\> "100" 10:20:05.848 [age] =\> "20" 10:20:05.848 [name] =\> "Bill" 10:20:05.848 } 10:20:05.848 [2] =\> table: 05AD5210 10:20:05.848 { 10:20:05.848 [health] =\> "80" 10:20:05.848 [age] =\> "50" 10:20:05.848 [name] =\> "Bob" 10:20:05.848 } 10:20:05.848 [3] =\> table: 05AD5210 10:20:05.848 { 10:20:05.848 [health] =\> "95" 10:20:05.848 [age] =\> "27" 10:20:05.848 [name] =\> "Sue" 10:20:05.848 } 10:20:05.848 }

second print_r

10:20:05.848 table: 05AD5800 { 10:20:05.848 [1] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "name" 10:20:05.848 [2] =\> "age" 10:20:05.848 [3] =\> "health" 10:20:05.848 } 10:20:05.848 [2] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "Bill" 10:20:05.848 [2] =\> "20" 10:20:05.848 [3] =\> "100" 10:20:05.848 } 10:20:05.848 [3] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "Bob" 10:20:05.848 [2] =\> "50" 10:20:05.848 [3] =\> "80" 10:20:05.848 } 10:20:05.848 [4] =\> table: 05AD5800 10:20:05.848 { 10:20:05.848 [1] =\> "Sue" 10:20:05.848 [2] =\> "27" 10:20:05.848 [3] =\> "95" 10:20:05.848 } 10:20:05.848 }

[pirx]

@roaminggamer

Great! I will try that. It does exactly what I wanted.

[roaminggamer]

Tip:   You may notice that all values are treated as strings. 

For the ‘first row as field names’ part of the solution, you can modify the code so it tries to force numbers to be numbers as follows:

replace this:

tmp2[fieldNames[j]] = tmp[j]

with this:

tmp2[fieldNames[j]] = tonumber(tmp[j]) or tmp[j]