# Transforming a CSV file in transit between two systems

Sometimes the gap between two systems that need to exchange data is entirely a formatting problem: wrong column order, 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 light transformations, you can express the transformation as a few lines of JavaScript and let AFT! handle both the transfer and the rewriting in a single job. This article walks through a pattern that 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 then imports that file to the destination. A `try/finally` block ensures both temporary files are always cleaned up, even if an error occurs mid-run.

```javascript
// 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  = Params("src_vfs")  || "source-system";
var srcPath     = Params("src_path") || "/exports/data.csv";
var dstVfsName  = Params("dst_vfs")  || "target-system";
var dstDir      = Params("dst_dir")  || "/imports/";

// Build a timestamped output filename so 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 = 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 for comma default

    // Transform
    // Replace the body of this loop with your own logic.
    // This example uppercases column 0 (a name field) and skips the original 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 to skip the source header
        var row = rows[i];
        if (row.length < 3) continue;              // skip blank or malformed lines
        output.push([
            row[0],                                // ID: pass through unchanged
            row[1].toUpperCase(),                  // NAME: transform applied here
            row[2]                                 // VALUE: pass through unchanged
        ]);
    }

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

    // Upload
    var dst = 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);
}
```

`ParseCSV(raw, ",")`: returns a two-dimensional array where each element is a row and each element of a row is a field. 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`. Takes a two-dimensional array and joins it back into a string using 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 columns is as simple as not including them in the pushed array. Adding a computed column means appending a new element. Sorting requires pulling the data rows (indices 1 to n) out into a separate array, sorting it, and merging it back with the header.

The `try/finally` cleanup block is not optional. If the upload fails or the transformation throws an exception, reaching `DelFile` on both temporaries is essential. Leaving temporary files accumulating on the AFT! host is 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 `FormatCSV` to handle them without any other changes.

#### 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; just address the indexes you want.

#### Adding a computed column

Append a new element to each pushed row:

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

Remember to add a corresponding header in the `header` array at the top so the receiving system knows what it is getting.