I'm receiving a CSV file via webhook containing event registration data. The goal is to transform each row into a distinct collection within a JSON object, which will then be sent to an API for conversion and reformatting.

chrome_6noxdq4sB11912×363 44.3 KB

In my setup, I'm fetching the CSV file using HTTP and then proceeding to rename, upload, and convert it to Google Sheets (these are the initial three modules).

This process enables me to utilize Google Sheets modules for handling the file.

Subsequently, I employ the Search Rows function within Google Sheets, referencing the file ID and sheet name obtained from the Google Drive upload.

This step yields each row as an individual collection.

Following that, I aggregate these collections into a JSON format, conforming to your required custom data structure.

Looking ahead, if you introduce additional columns to the CSV, the only modification needed will be within the custom data structure of the Aggregate to JSON module.

In the actual data, there are 23 columns, so I'm a bit concerned about the maintainability of a series of nested join/split operations if more columns are added in the future.

However, you've just shown me that I can copy and paste JSON to and from the scenario editor. This has allowed me to rapidly auto-generate some definitions within a macro environment.

I've managed to set this up now:

image1332×321 41.1 KB

It's still a bit complex, as there are a couple of nested replace operations to handle newline characters and quotation marks within a Text constructor:

image433×1064 48.3 KB

This is then assembled into some text as valid JSON:

image1008×278 39.2 KB

And then parsed against a JSON description:

The JSON used to create the schema was:

{
"values": [

]
}

Then, the JSON can be created:

image440×664 28.6 KB

And finally, calling the API with that:

image434×675 26.2 KB

It's a bit more involved, but perhaps more manageable than the join/slice method?

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

1. Copy the JSON code below by clicking the copy button when you mouseover the top-right of the code block
   

2. Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV (paste keyboard shortcut for Windows) to paste directly in the canvas.

3. Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 33,
                    "module": "util:ComposeTransformer",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "value": "Orderno,Orderdate,CompanynBSOM1002,2022-12-15,Shop 1nBSOM1002,2022-12-15,Shop 2"
                    },
                    "metadata": {
                        "designer": {
                            "x": 14,
                            "y": -733,
                            "name": "CSV"
                        },
                        "restore": {},
                        "expect": 
                    }
                },
                {
                    "id": 34,
                    "module": "util:ComposeTransformer",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "value": "[n  ,n  ," + newline + "n]"
                    },
                    "metadata": {
                        "designer": {
                            "x": 258,
                            "y": -731,
                            "name": "JSON",
                            "messages": 
                        },
                        "restore": {},
                        "expect": 
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}

samliew – request private consultation

Join the Make unofficial Discord server!

Screenshot_2024-05-02_210527846×351 31.5 KB

Output

Give it a try and let us know if you encounter any difficulties!

Join the callin.io unofficial Discord server!

How can I convert a CSV file into a JSON array of arrays within callin.io?

Everything else seems simple enough, but when I structure any JSON object, it appears I can only connect an Array Aggregator to the more structured parts of the data.

Given this CSV:

Orderno,Orderdate,Company
BSOM1002,2022-12-15,Shop 1
BSOM1002,2022-12-15,Shop 2

How can I achieve this result?

[
  ,
 ,
 
]

Here's some synthetic test data I've run through the provided blueprint, which illustrates the problem.

Thanks,

Could you also provide an example CSV that would translate into the example JSON you provided?