Help parsing csv data into JSON with eval blocks in flow

[selch2169 (Matt Seclchow)]

Hello all, I’m building a flow that reads a csv file but I’m struggling with parsing the data that’s returned.

My csv file has three columns and will have 1 or more rows of data: Date, Order_Number, Total. I’d like to JSONify this so I can query another system with this information.

Raw text returned with two rows:
“DATE,ORDER_NUMBER,TOTAL\r\nWed May 01 2024,55425,136.1\r\nWed May 01 2024,55426,56.42\r\n”

Pretty version:
DATE,ORDER_NUMBER,TOTAL
Wed May 01 2024,55425,136.1
Wed May 01 2024,55426,56.42

Desired outcome:
{“date”:DATE,
“order_number”:ORDER_NUMBER},
“total”:TOTAL}

{“date”:Wed May 01 2024,
“order_number”:55425},
“total”:136.1}

I don’t need the column headings row as I know what they’ll always be. I can strip it down to just the two rows of data but I can’t figure out how to map the values to JSON.

This process needs to be automated so I can’t manually load the csv into the collection and run it - but as you know, that works wonderfully - just not in flows yet.

Or is my approach wrong and this should be done in a post test script and then passed to my flow?

I’d appreciate any feedback. Thank you!

[flows-daniel (Daniel Kimmelmann)]

Hi @selch2169

You can use TypeScript in the Evaluate Block to achieve this (select the FQL icon in the top right corner and open the drop down to select TypeScript):

Code:

function csvToJson(csvText: string): any[] {
    const lines = csvText.split('\n');
    const headers = lines[0].split(',');
    const jsonData = [];

    for (let i = 1; i < lines.length; i++) {
        const data = lines[i].split(',');
        if (data.length === headers.length) {
            const entry: any = {};
            for (let j = 0; j < headers.length; j++) {
                entry[headers[j].trim()] = data[j].trim();
            }
            jsonData.push(entry);
        }
    }

    return jsonData;
}
csvToJson(input);

You may need to change the split from ‘\n’ to ‘\r\n’

[selch2169 (Matt Seclchow)]

So awesome, I wouldn’t have figured that out. You sir, are a gentleman and a scholar. Thank you.

I’m surprised I didn’t find a similar topic on this already, unless my searching stinks. I think this will help many others too.