User Tools

Site Tools


Converting "pts" point files to Calc or Excel

When CaBSP finds a leak or when you record the player path in Cafu, a pts point file is written that describes the path through the level.

All resulting pts file can be loaded into CaWE in order to visualize the path graphically, but some users may wish to load the point files into a spreadsheet application as well.

This document explains how pts point files are converted into csv file format so that they can be opened in Microsoft Excel, OpenOffice.org Calc, and many other applications.

The pts2csv() conversion function

The pts point files that are generated by CaBSP and Cafu are in fact small but complete Lua programs. As the Cafu in-game console happens to be a full-fledged Lua interpreter, it is self-suggesting that we use a console function in order to convert pts files to csv.

Function pts2csv() in file config.lua is ready for use, but you may customize it if you wish:

function pts2csv(fileName)
    if fileName:sub(-4, -1):lower()==".pts" then
        -- Strip the .pts suffix, if present.
        fileName=fileName:sub(1, -5);
    end
 
    -- Load and run the point file in order to obtain the Points table.
    dofile(fileName .. ".pts");
 
    -- Write all points into a new csv file.
    local csvFile=assert(io.open(fileName .. ".csv", "wt"));
 
    csvFile:write('"time","x","y","z","heading","info"\n');
    for i=1, #Points do
        csvFile:write("\"", table.concat(Points[i], "\",\""), "\"\n");
    end
 
    csvFile:close();
end

In this implementation, we have chosen to have a leading line with the column names, to delimit the fields by double-quotes, and to separate the individual fields by commas. As a result, a typical generated csv file looks like this:

"time","x","y","z","heading","info"
"0","-179","-451","-216.63","9981",""
"3.83891","-179","-451","-251.887","9830",""
"5.04289","-164.953","-454.399","-251.936","18860","This is a test."
"5.24353","-118.536","-464.517","-251.936","16850",""
"5.44413","-70.7624","-468.833","-251.936","14960",""

You can edit the code in order to customize the output format as desired. For example, omitting the line with the column names and using semicolons as field separators yields

    -- The next line is commented out in order to omit the line with column names.
    -- csvFile:write('"time","x","y","z","heading","info"\n');
    for i=1, #Points do
        -- The , has been replaced by ; as the field separator.
        csvFile:write("\"", table.concat(Points[i], "\";\""), "\"\n");
    end

Usage

Using the pts2csv() conversion function is easy: In the Cafu in-game console, type

    pts2csv("myfile")

in order to convert file myfile.pts to myfile.csv. Note that this even works in the Cafu main menu – no need to run a map beforehand.

You can now quit Cafu and import the newly generated file in Microsoft Excel, OpenOffice.org Calc, or any other application that can read csv files.

Tips and tricks

Microsoft Excel

With MS Excel, it can help to use File → Import rather than File → Open or double-clicking the file in Windows Explorer.

OpenOffice.org Calc

With OOo Calc, if the program expects decimal numbers to be formatted in a locale-dependent fashion (e.g. with commas instead of points as in 3,1415926 instead of 3.1415926), make sure to adjust the column type in the preview accordingly.

Implementing shortcuts

If you frequently record player paths and usually convert them to csv thereafter, you can save some typing by adding this code to your config.lua file:

function startRec(fileName)
    g_recName=fileName or "playerpath.pts";
    recordPath(g_recName);
end
 
function stopRec()
    recordPath();
    pts2csv(g_recName);
end

With this code, startRec() starts recording the path to file playerpath.pts or to the file with the specified name. stopRec() creates the associated csv file with the same name.

See also

appendix/pts2csv.txt · Last modified: 2017-02-15 11:43 by Carsten