Using BoxLang to process CSV files and push data to an API endpoint

The other day I had a situation where a third-party vendor was attempting to push a large volume of data. Due to system constraints, this endpoint is configured to handle much smaller payloads. I asked the vendor if they could send the payload they were trying to push to us, but instead of the JSON payload they sent the data as an Excel spreadsheet so I needed something to bridge the gap from Excel to get that data into the system.

Being able to execute BoxLang scripts from the command line is arguably one of my favorite features of the language. It was trivial to write a quick script to read in the CSV file, parse the header row, and build a JSON payload for each row of data, breaking the JSON payloads out into chunks of 100 to prevent overloading the endpoint. Some of the columns were currency values, and there needed to be some special handling for those because of Excel formatting.

Below is the script to read in the CSV file and output the payload chunk in a JSON file.

// processData.bxs
function parseCsvLine(line) {
    fields = [];
    currentField = "";
    inQuotes = false;

    for (i = 1; i <= line.len(); i++) {
        ch = line.mid(i, 1);
        if (ch == '"') {
            inQuotes = !inQuotes;
        } else if (ch == ',' && !inQuotes) {
            fields.append(currentField);
            currentField = "";
        } else {
            currentField &= ch;
        }
    }
    fields.append(currentField);
    return fields;
}

csvContent = fileRead("./bulk_data.csv");
lines = csvContent.replace(char(13), "").listToArray(char(10));

headers = parseCsvLine(lines[1]);

chunkSize = 100;
chunkIndex = 1;
batch = { "data": [] };

lines.each((line, index) => {
    if (index == 1 || line.trim() == "") return;

    values = parseCsvLine(line);

    if (values.len() < headers.len()) return;

    row = {};
    for (i = 1; i <= headers.len(); i++) {
        rawValue = values[i];
        strippedValue = rawValue.replace(",", "");
        row[headers[i]] = (isNumeric(strippedValue) && strippedValue.len() <= 15) ? strippedValue.val() : rawValue;
    }
    batch.data.append(row);

    if (batch.data.len() == chunkSize) {
        fileWrite("./payload-#chunkIndex#.json", jsonSerialize(batch));
        chunkIndex++;
        batch = { "data": [] };
    }
});

if (batch.data.len() > 0) {
    fileWrite("./payload-#chunkIndex#.json", jsonSerialize(batch));
}

The processing script written, the next step was the process the CSV file and output the payloads.

boxlang processData.bxs

Once the payload JSON files were written, I needed a quick script to push all those payloads to the endpoint.

// send.bxs
files = directoryList("./", false, "name", "payload-*.json");

files.each((fileName) => {
  fileData = fileRead("./#fileName#");

  bx:http url = "<api endpoint>" method="post" result="postResult" {
    bx:httpparam type="header" name="Content-Type" value="application/json";
    bx:httpparam type="header" name="hash-code" value=lcase(hash(fileData, "SHA-1"));
    bx:httpparam type="body" value=fileData;
  }

  dump("#fileName#: #postResult.fileContent#");
});

Finally I needed to run the script to push the data to the endpoint

boxlang send.bxs

It took about 15 minutes from start to finish to write the code and process the data. I love the old adage "when your favorite tool is a hammer, everything looks like a nail" but I would say BoxLang is a very versatile hammer.