Use a Lua formula to process a CSV file

It’s common in video games to allow players modify some of the functionality of the game via scripting. I recently explored developing such a feature for a web application and started a really tiny fire which I’d like to share.

I was able to develop a program that takes a CSV file, a corresponding Lua script containing a function that computes a result from each row of the CSV, and returns a new CSV file with the results in a new column. While not identical in utility to a video game mod, it presents one of the features of a possible web app mod.

An implementation of the CSV transformer can be found here: CSVerwand

Tech used

The program is written in C#, utilizing .NET 8, and requires two input files: one in CSV and the other in Lua. These ~~constraints~~ choices were motivated by a few reasons:

Lua

Lua has a relatively simple API compared to alternatives like Python and Javascript, which makes scripting more accessible to a wide range of users.

It’s also easier to embed due to its relatively smaller footprint; the tarball for Lua 5.4.7 is 1.3M uncompressed . This should allow its addition to a web application without a huge hit to the host’s performance.

CSV

CSV is easy to read and write and supported by a wide range of applications and programming languages, which makes it ideal for line-of-business apps that would utilize the mod feature.

C#

This is the one personal choice that I can’t back with a more objective reason. I’ve found working in C# to be ‘familiar’; the look of C# code is largely the same to me.

I’ll be happy to update the article with good, ‘objective’, user-submitted reasons for using C#.

Building the CSV transformer

There are three key parts of the application: parsing the Lua script into a C# function, parsing the CSV into a collection and, projecting each item in the collection into a result using the selector from the Lua script.

We’ll be working with the following files:

test.csv

id,firstname,lastname
1,john,doe
2,jane,doe

func.lua

function Calculate(row)
    return row.firstname .. row.lastname
end

Parsing a Lua script into a function

We’ll read in the Lua source code and convert it into a Lua object. For this we’ll be using NLUa to provide the bridge between Lua and .NET.

using System.Text;
using NLua;

namespace ModExplorer;

public static class CSVTransform
{
    public static Func<TSource, TResult> ReadLuaFromFile<TSource, TResult>(string fileName, 
    Encoding encoding)
    {
        ArgumentNullException.ThrowIfNull(fileName);
    
        var scriptSource = File.ReadAllText(fileName, encoding);
        var state = new Lua(); // Lua object provided by NLua
        state.DoString(scriptSource);
    }
}

The ReadLuaFromFile method returns a Func<T, TResult> delegate which would be eventually used as the selector of the CSV collection.

The Lua function can now be retrieved from the state using the hardcoded function name “Calculate”".

var scriptFunc = state["Calculate"] as LuaFunction;

To make the function object usable as a selector, we’ll need to cast the result of the call.

return sourceEntity => (TResult)scriptFunc.Call(sourceEntity).First();

The full method looks like this:

public static Func<TSource, TResult> ReadLuaFromFile<TSource, TResult>(string fileName, Encoding 
encoding)
{
    ArgumentNullException.ThrowIfNull(fileName);
    
    var scriptSource = File.ReadAllText(fileName, encoding);
    var state = new Lua(); // Lua object provided by NLua
    state.DoString(scriptSource);
    
    var scriptFunc = state["Calculate"] as LuaFunction;
    return sourceEntity => (TResult)scriptFunc.Call(sourceEntity).First();
}

I’ve extracted this method into a tiny NuGet library called LuaPredicates which I’m still working on.

Parsing the CSV file into a collection

We’ll parse the CSV file into a usable object using the CSVHelper library.

// other imports omitted for brevity
using CsvHelper;
using CsvHelper.Configuration;

namespace ModExplorer;

public static class CSVTransform
{
    public static Func<TSource, TResult> ReadLuaFromFile<TSource, TResult>(string fileName, 
    Encoding encoding)
    { // omitted for brevity }
    
    public static IEnumerable<dynamic> ReadCSVFromFile(string filePath)
    {
        var config = new CsvConfiguration(CultureInfo.InvariantCulture)
        {
            PrepareHeaderForMatch = args => args.Header.ToLower(),
        };
        
        var csvFileStream = File.OpenRead(filePath);
        using var reader = new StreamReader(fileStream);
        using var csv = new CsvReader(reader, config);
    }
}

The config forces all the headers into lowercase for easier interoperability.

I wanted to make the program as accessible as possible and decided to not have the end-user be required to specify the types for each column. Hence, the ReadCSVFromFile method returns a dynamic enumerable (IEnumerable<dynamic>). This is likely to work against me in the future so I intend to improve the solution in the future.

With the CSV parsed into an object, we need to read the rows into a collection. And because both CsvHelper and our selector function require a typed collection, we need to create one. This will have to be a dynamic type as we do not know what its properties will be until we read the headers.

We use Reflection to create type instances at runtime , and to invoke and access them.

The following utility methods can be used to create a runtime type and add properties to said type.

// other imports omitted for brevity
using System.Reflection;
using System.Reflection.Emit;

namespace ModExplorer;

public static class CSVTransform
{
    private static TypeBuilder BuildDynamicType()
    {
        var parent = typeof(Empty);
        const string name = "CSVRow";
            
        // 1. create assembly name
        var assemblyName = new AssemblyName($"SomeAssemblyName{name}");
        // 2. create the assembly builder
        var assemblyBuilder = AssemblyBuilder.DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run);
        // 3. use assembly builder to create a module builder
        var moduleBuilder = assemblyBuilder.DefineDynamicModule("MainModule");
        // 4. use module builder to create a type builder
        var tb = moduleBuilder.DefineType(name,
            TypeAttributes.Public |
            TypeAttributes.Class
            , parent);
        return tb;
    }
    
    private static void CreateProperty(TypeBuilder tb, string propertyName, Type propertyType)
    {
        var fieldBuilder = tb.DefineField("_" + propertyName, propertyType, FieldAttributes.Private);

        var propertyBuilder = tb.DefineProperty(propertyName, PropertyAttributes.HasDefault, propertyType, null);
        var getPropMthdBldr = tb.DefineMethod("get_" + propertyName, MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig, propertyType, Type.EmptyTypes);
        var getIl = getPropMthdBldr.GetILGenerator();

        getIl.Emit(OpCodes.Ldarg_0);
        getIl.Emit(OpCodes.Ldfld, fieldBuilder);
        getIl.Emit(OpCodes.Ret);

        var setPropMthdBldr =
            tb.DefineMethod("set_" + propertyName,
                MethodAttributes.Public |
                MethodAttributes.SpecialName |
                MethodAttributes.HideBySig,
                null, new[] { propertyType });

        var setIl = setPropMthdBldr.GetILGenerator();
        var modifyProperty = setIl.DefineLabel();
        var exitSet = setIl.DefineLabel();

        setIl.MarkLabel(modifyProperty);
        setIl.Emit(OpCodes.Ldarg_0);
        setIl.Emit(OpCodes.Ldarg_1);
        setIl.Emit(OpCodes.Stfld, fieldBuilder);

        setIl.Emit(OpCodes.Nop);
        setIl.MarkLabel(exitSet);
        setIl.Emit(OpCodes.Ret);

        propertyBuilder.SetGetMethod(getPropMthdBldr);
        propertyBuilder.SetSetMethod(setPropMthdBldr);
    }

    public static Func<TSource, TResult> ReadLuaFromFile<TSource, TResult>(string fileName, 
    Encoding encoding)
    { // omitted for brevity }
    
    public static IEnumerable<dynamic> ReadCSVFromFile(string filePath)
    { // omitted for brevity}
}

The methods can then be utilized in the ReadCSVFromFile method to create a typed collection

var dynamicTypeBuilder = BuildDynamicType();

csv.Read();
csv.ReadHeader();

// add properties to dynamic type based on csv.HeaderRecord
foreach (var prop in csv.HeaderRecord)
{   
    CreateProperty(dynamicTypeBuilder, prop, typeof(string));
}

var dynamicType = dynamicTypeBuilder.CreateType();

var records = new List<dynamic>();
while (csv.Read())
{
    var csvRecord = csv.GetRecord(dynamicType);
    
    records.Add(csvRecord);
}

return records;

At first, we read in just the headers with csv.ReadHeader() to obtain the names for the properties of the dynamic type. After adding the properties, we can then read in the rows, using the dynamic type.

The full ReadCSVFromFile method looks like this:

public static IEnumerable<dynamic> ReadCSVFromFile(string filePath)
{
    var config = new CsvConfiguration(CultureInfo.InvariantCulture)
    {
        PrepareHeaderForMatch = args => args.Header.ToLower(),
    };
    
    var csvFileStream = File.OpenRead(filePath);
    using var reader = new StreamReader(fileStream);
    using var csv = new CsvReader(reader, config);
    
    var dynamicTypeBuilder = BuildDynamicType();

    csv.Read();
    csv.ReadHeader();
    
    // add properties to dynamic type based on csv.HeaderRecord
    foreach (var prop in csv.HeaderRecord)
    {   
        CreateProperty(dynamicTypeBuilder, prop, typeof(string));
    }
    
    var dynamicType = dynamicTypeBuilder.CreateType();
    
    var records = new List<dynamic>();
    while (csv.Read())
    {
        var csvRecord = csv.GetRecord(dynamicType);
        
        records.Add(csvRecord);
    }
    
    return records;
}

Projecting the results into a new CSV file

With both the ReadLuaFromFile and ReadCSVFromFile methods, we can now parse the CSV file and generate a collection of the results by passing each row through the function parsed from the Lua file.

// omitted for brevity

public static class CSVTransform
{
  public static async Task GenerateResultFile(string csvFilePath, string luaFilePath)
  {
    var records = ReadCSVFromFile(csvFilePath);
    // avoid multiple enumeration by converting to a List
    var enumerable = records.ToList();
    var luaFunc = ReadLuaFromFile<object, object>(luaFilePath);
    var results = records.Select(luaFunc).ToList();
  }
}

Since we only want to write to a CSV file and do not intend to use the type elsewhere, we can create a dynamic array of the combination of the original records and the results.

List<dynamic> combined = [];
for (var i = 0; i < enumerable.Count; i++)
{
    dynamic item = new ExpandoObject();
    var dictionary = (IDictionary<string, object>)item;
    foreach (var property in enumerable[i].GetType().GetProperties())
        dictionary.Add(property.Name, property.GetValue(enumerable[i]));
    dictionary.Add("calculated", results[i]);
    combined.Add(dictionary);
}

You’ll need the System.Dynamic builtin in order to use ExpandoObject .

The dynamic collection combined can now be written to an output CSV file.

await using var writer = new StreamWriter("output.csv");
await using var csv = new CsvWriter(writer, CultureInfo.InvariantCulture);
await csv.WriteRecordsAsync(combined);

The CSVWriter class comes from CsvHelper.

The full GenerateResultFile looks like this:

public static async Task GenerateResultFile(string csvFilePath, string luaFilePath)
{
    var records = ReadCSVFromFile(csvFilePath);
    // avoid multiple enumeration by converting to a List
    var enumerable = records.ToList();
    var luaFunc = ReadLuaFromFile<object, object>(luaFilePath);
    var results = records.Select(luaFunc).ToList();
    
    List<dynamic> combined = [];
    for (var i = 0; i < enumerable.Count; i++)
    {
        dynamic item = new ExpandoObject();
        var dictionary = (IDictionary<string, object>)item;
        foreach (var property in enumerable[i].GetType().GetProperties())
            dictionary.Add(property.Name, property.GetValue(enumerable[i]));
        dictionary.Add("calculated", results[i]);
        combined.Add(dictionary);
    }
    
    await using var writer = new StreamWriter("output.csv");
    await using var csv = new CsvWriter(writer, CultureInfo.InvariantCulture);
    await csv.WriteRecordsAsync(combined);
}

Room for improvement

A dull eye would be quick to notice a number of issues with the solution:

There’s next to no error-handling despite the massive allowance. For example, it’s not unlikely for the Lua function to reference a field that doesn’t exist as a header in the CSV.
The program relies heavily on casting object to the needed types which may not be successful.
The CSV is assume to be ‘clean’ i.e the are no incomplete rows
And probably a lot more that I’ve failed to mention.

I’ll be refining the program in the future and would gladly share the improved versions.

Tech used#

Lua#

CSV#

C##

Building the CSV transformer#

Parsing a Lua script into a function#

Parsing the CSV file into a collection#

Projecting the results into a new CSV file#

Room for improvement#