Transforming a CSV file in transit

Sometimes the gap between two systems that need to exchange data is entirely a formatting problem: the wrong column order, a different delimiter, extra header columns the receiving system does not understand, or a computed field that must be added before import. Rather than standing up a dedicated ETL platform for a light transformation, you can express it as a few lines of JavaScript and let Syncplify AFT! handle both the transfer and the rewriting in a single job. This recipe downloads a CSV file from one VFS, transforms it in memory, and delivers the result to a second VFS.

How it works

The script exports the source file to a local temporary location, reads and parses it, applies your transformation, serializes the result back to CSV, writes the output to a second temporary file, and imports that file into the destination. A try and finally pair makes sure both temporary files are cleaned up, even if an error occurs mid run.

// CSV transform pipeline
// Downloads a CSV file from a source VFS, applies a transformation, and uploads
// the result to a destination VFS. Temporary files are always deleted on exit.

var srcVfsName  = Param("src_vfs")  || "source-system";
var srcPath     = Param("src_path") || "/exports/data.csv";
var dstVfsName  = Param("dst_vfs")  || "target-system";
var dstDir      = Param("dst_dir")  || "/imports/";

// Build a timestamped output file name, so that destination files never collide.
var ts          = FormatDateTime("YYYY-MM-DD_HHmmss");
var dstFileName = "data-" + ts + ".csv";

var tmpIn  = GetTempFileName();       // local temporary file for the downloaded source
var tmpOut = GetTempFileName();       // local temporary file for the transformed output

try {
    // Download
    var src = new VirtualFSByName(srcVfsName);
    var dlResult = src.ExportFile(srcPath, tmpIn);
    if (!dlResult.Ok()) {
        Log.Error("download failed: " + dlResult.ErrorMsg());
        Exit(1);
    }

    // Parse
    var raw  = ReadTextFile(tmpIn);
    var rows = ParseCSV(raw, ",");    // second argument is the delimiter; omit it for a comma

    // Transform
    // Replace the body of this loop with your own logic.
    // This example uppercases column 1 (a name field) and skips the source header row.
    var header = ["ID", "NAME_UPPER", "VALUE"];   // write your own output header
    var output = [header];
    for (var i = 1; i < rows.length; i++) {       // i = 1 skips the source header
        var row = rows[i];
        if (row.length < 3) continue;              // skip blank or malformed lines
        output.push([
            row[0],                                // ID: passed through unchanged
            row[1].toUpperCase(),                  // NAME: the transformation
            row[2]                                 // VALUE: passed through unchanged
        ]);
    }

    // Serialize and write
    var csv = FormatCSV(output, ",");
    WriteTextToFile(tmpOut, csv);

    // Upload
    var dst = new VirtualFSByName(dstVfsName);
    var ulResult = dst.ImportFile(tmpOut, dstDir + dstFileName);
    if (!ulResult.Ok()) {
        Log.Error("upload failed: " + ulResult.ErrorMsg());
        Exit(1);
    }

    Log.Info("transformed and delivered " + dstFileName + " (" + (output.length - 1) + " rows)");

} finally {
    DelFile(tmpIn);
    DelFile(tmpOut);
}

Param("src_vfs"): the four settings at the top come from job parameters when the job is triggered with them (from the command line or through the REST API) and fall back to the defaults otherwise, so one script serves several source and destination pairs. Params is an older alias of the same function and still works.

ParseCSV(raw, ","): returns a two dimensional array, one element per row and one element per field in each row. The second argument is the delimiter; omit it and the engine defaults to a comma. Use "\t" for tab separated files.

FormatCSV(output, ","): the inverse of ParseCSV. It takes a two dimensional array and joins it back into a string with the delimiter you supply. Both functions handle quoting and embedded newlines according to RFC 4180.

var output = [header]; ... output.push([...]): the transformation loop is the only part you need to change for a different requirement. Deleting a column is as simple as not including it in the pushed array. Adding a computed column means appending a new element. Sorting means pulling the data rows (indices 1 to n) into a separate array, sorting it, and merging it back with the header.

The finally cleanup block is not optional. If the upload fails or the transformation throws an exception, reaching DelFile on both temporary files is essential. Temporary files accumulating on the AFT! host are both a security concern and a disk space concern over time.

Changing the source delimiter

Some partners deliver semicolon delimited files that are technically not standard CSV. Pass ";" as the second argument to ParseCSV and to FormatCSV to handle them without any other change.

Selecting a subset of columns

To drop columns from the output, simply do not include them in the push call. There is no need to filter the input; address only the indexes you want.

Adding a computed column

Append a new element to each pushed row:

output.push([
    row[0],
    row[1].toUpperCase(),
    row[2],
    (parseFloat(row[2]) * 1.21).toFixed(2)    // computed tax inclusive total
]);

Remember to add a matching entry to the header array at the top, so that the receiving system knows what it is getting.