CSV Data Load (code snippet)

Description

This function allows the easy loading of a CSV File into a table of tables. It can be used in two ways, if the file has a header line this can be used to return a table with lines indexed on the header field names, in which case two tables are returned one with the data and another with the field names. Alternately all the lines in the file can be returned as a simply indexed table.

Requires: None

Code

LoadCSV.fh_lua
--[[table.loadcsv
@name          table.loadcsv
@description   Loads a CSV file into a table of tables, the first line should contain descriptions
@usage         provide valid filename, use ipairs loop to use resulting table
@param         filename      filename to load
@param         bHasHeader    true if the file contains a header line, false if not, defaults to false
                             If true  
@return        table of tables, if a header is used, indexed by the fieldnames from the header
@return        table containing the header field names (if used) 
]]
 
function table.loadcsv(filename,bHasHeader)
    bHasHeader = bHasHeader or false
    local bHeader = false
    local function fromCSV (s)
        s = s .. ',' -- ending comma
        local t = {} -- table to collect fields
        local fieldstart = 1
        repeat
        -- next field is quoted? (start with `"'?)
        if string.find(s, '^"', fieldstart) then
            local a, c
            local i = fieldstart
            repeat
            -- find closing quote
            a, i, c = string.find(s, '"("?)', i+1)
            until c ~= '"' -- quote not followed by quote?
            if not i then
                error('unmatched "')
            end
            local f = string.sub(s, fieldstart+1, i-1)
            table.insert(t, (string.gsub(f, '""', '"')))
            fieldstart = string.find(s, ',', i) + 1
        else -- unquoted; find next comma
            local nexti = string.find(s, ',', fieldstart)
            table.insert(t, string.sub(s, fieldstart, nexti-1))
            fieldstart = nexti + 1
        end
        until fieldstart > string.len(s)
        return t
    end
    local contents = {}
    if bHasHeader then
        local header = {}
    end
    for line in io.lines(filename) do
        local fields = fromCSV(line)
        local data = {}
 
        if bHasHeader then
            if bheader then
                -- Build Data Table with the Header Descriptions
                for i,field in ipairs(fields) do
                    if field ~= '' then
                        data[header[i]] = field
                    end
                end
                table.insert(contents,data)
            else
                -- Grab Column Names
                header = fields
                bheader = true
            end
        else
            table.insert(contents,fields)
        end
    end
    return contents,header
end

Usage

-- Load CSV with file with header
    local lines,titles = table.loadcsv('my.csv',true)
-- Load CSV with file with no header
    local lines = table.loadcsv('my.csv')