-
Goal: I have a table with three columns of data: first name, last name, email address. I want to upload this data to Mailjet (an email service) using their REST API (docs here). The first step in their process is for me to POST to an endpoint with the data in the body of the post in CSV format, but binary encoded.
-
Steps:
My first attempt was to create a Transformer that uses a js function to take the data from the table, convert it to a CSV formatted string, then binary encode it. Then I used the value of that Transformer in the API request. But every time I try this, Mailjet has errors importing the data. So maybe my js is wrong? Or maybe I'm making wrong assumptions about character encoding or something?
Here is what I have now:
function jsonToCsvBinaryString(jsonArray) {
if (!Array.isArray(jsonArray) || jsonArray.length === 0) {
return '';
}
const keys = Object.keys(jsonArray[0]);
const csvRows = [];
// Add header row
csvRows.push(keys.join(','));
// Add data rows
for (const obj of jsonArray) {
const values = keys.map(key => {
const escapedValue = ('' + obj[key]).replace(/"/g, '""'); // Escape double quotes
return `"${escapedValue}"`;
});
csvRows.push(values.join(','));
}
// Convert CSV array to string
const csvString = csvRows.join('\n');
// Convert CSV string to binary string
let binaryString = "";
for (let i = 0; i < csvString.length; i++) {
const binaryChar = csvString.charCodeAt(i).toString(2).padStart(8, '0');
binaryString += binaryChar;
}
return binaryString;
}
const binaryString = jsonToCsvBinaryString({{adminTable.data}})
return binaryString
But more broadly, I'm wondering if there's a better way to do this. Can I somehow write the data from the table to a csv file "object" stored in memory somewhere in retool and then use that same object in the API request, treating it more like a file? And not having to use js to encode it into a binary string?
Or is there a better, more native way to generate the binary?