What Does This Article Cover?
- Manually parse CSV text to JSON format
- Example
- Considerations
- Other related material
Decoding text byte code
The CSV connector expects UTF-8 encoded, character delimited text files. There may be a scenario where CSV text data can't be ingested through the CSV connector. For example, the file is not encoded in UTF-8, or the data is sourced from a connection that doesn't natively convert CSV data. The following covers an alternative example of converting CSV text data into a JSON format.
Example
Shown here, the source data is a CSV formatted string. *Note the .value is used since metadata is enabled on the input file data and .utf8String references the text data * 
Then converted to JSON format: 
The following expression can be pasted into a Custom Condition where the Source is a File connector Input for CSV formatted text data.
var CSVToJSON = (data, delimiter = ',', eol = '\n', customHeaders = null, skipLines = 0) => {
// Extract titles (header) from the CSV data or use custom headers if provided
var titles = customHeaders || data.slice(0, data.indexOf(eol)).split(delimiter);
// Return the data, split by end-of-line delimiter,
// then map each line to a JSON object using the titles
//return data.slice(1)
return data
.split(eol)
.slice(customHeaders ? 0 : 1) // Skip the first row if using custom headers
.slice(skipLines ? skipLines : 0) // Skip the first row if using custom headers
.map(v => {
// Split the current line by the delimiter to get the values
var values = v.split(delimiter);
// Reduce the titles and values into a single JSON object
return titles.reduce(
(obj, title, index) => ((obj[title] = values[index]), obj),
{}
);
});
};
// Initialize the variable with text from the source data
var payload = .value.utf8String;
// Define custom headers if needed
//var customHeaders = ['Column1', 'Column2', 'Column3', 'Column4','Column5'];
//var skipLines = 0;
// Call the function with data, tab delimiter, end-of-line characters, replacement headers, and option to skip lines
//CSVToJSON(payload, ',', '\r\n', customHeaders, skipLines);
CSVToJSON(payload, ',', '\r\n');
Considerations
In the example here, a tab - '\t' is passed in as the 2nd argument into the CSVToJSON() function call to override the default comma, and the Windows style CRLF "\r\n" is passed in as the 3rd argument to override the default '\n' end of line character.
If, in your case, these are different as well, they can be specified when calling the function as shown in the example above.
-
CSV - Delimiter - The default delimiter set by the function is a comma. CSV stands for comma-separated values, and traditionally may have only used a comma as the delimiter. As shown in the example provided, other characters like a tab may be used instead.
-
End of line character - The default end-of-line character is '\n' and the character-delimiter is a ','. Files out of a Windows system typically use Carriage Return (CR) - '\r' and Line Feed (LF) - '\n' for end of line characters. Unix systems typically have just a newline - '\r' character.
- Custom header row - An example of an array with custom header rows is included. This can be passed as an argument into the function to inject a header row into the data. This is useful if your data doesn't have a header row, or you want to replace existing headers.
- Skip lines - Optional argument to skip the first X number of rows. Note to skip 1 line if using custom header row to prevent reading the existing header row as data.
-
Validate the outcome - Make sure to understand what the function is doing to transform the CSV text and to review that the outcome is what you expect. For example, if you expect the end of line characters within the value content, then the provided function must be modified.
Feel free to reach out to us if you have any questions.