How to Transform CSV Data to JSON for API Uploads

Convert CSV files to JSON format for uploading to APIs.

What This Integration Does

Plenty of business systems still produce CSV exports, but most modern APIs only accept JSON. Doing the conversion by hand is fine once; doing it weekly with three different files in three different shapes is a chore that's perfect for automation. This workflow picks the CSV up wherever it lands, parses it, optionally reshapes the rows to match the target API's contract, and POSTs the resulting JSON to the endpoint.

The pipeline is built to handle the messy parts: encoding detection, header quirks, type conversion (a string "123" in CSV becomes a number in JSON when the API expects one), and chunked uploads when the file is too big to send in one request.

Prerequisites

A source for the CSV: FTP/SFTP, inbound email, or a Webhook upload.
The target API endpoint URL, expected JSON shape, and authentication (API key, bearer token, basic auth, or OAuth).
A clear mapping from CSV column names to API field names. Get this from the API docs before building the workflow.
Knowledge of the target API's max payload size and whether it supports batched arrays or only one record per request.

Step 1: Trigger - Pick Up the File

Drop a Trigger node. For polled FTP, use Schedule and follow it with a Connector node calling the ftp list-directory and download-file tools. For email-attached files use the Email trigger. For partner uploads use a Webhook trigger - the body is the CSV.

Step 2: Parse the CSV

Add a Connector node for the csv connector and call to-json. This turns the file into an array of objects keyed by the header row. Configure:

Has header: true
Delimiter: comma (or whatever the source uses)
Trim values: true
Encoding: utf-8 by default; switch to utf-16 or windows-1252 if the source is an Excel export

If you want to sanity-check the file first, call info from the same connector for row count, columns, and detected types.

Step 3: Transform to Match the API Shape

CSV columns rarely line up 1:1 with API fields. Add a Transform node to:

Rename keys ("Email Address" becomes email).
Convert types: price: Number(row.price), active: row.active === 'true'.
Build nested objects: address: { line1: row.address_1, city: row.city }.
Drop unused columns.

For complex transformations - especially nested arrays - use the code connector with execute-javascript instead. It scales better than a visual transform for 20+ field mappings.

Step 4: Validate Before Upload

Add a Connector node for the validation connector and call email, phone, or url on the fields the API requires. Pair with a Condition node that splits the array into "valid" and "invalid" lists. Invalid rows route to a slack alert (or a quarantine file) rather than getting POSTed and rejected by the API.

Step 5: Chunk and Upload

Most APIs cap payload size or array length. Use the array connector's chunk tool to split the validated array into batches (e.g. 100 records each), then wrap a Loop around the upload step. Inside the loop, add an http http-post call:

POST https://api.example.com/v1/customers
Authorization: Bearer {{ secrets.API_TOKEN }}
Content-Type: application/json

[
  { "email": "alice@example.com", "name": "Alice" },
  { "email": "bob@example.com", "name": "Bob" }
]

If the API only accepts one record per call, just loop over the array directly with http-post per row. Spojit's per-node concurrency keeps the requests respectful of rate limits.

Step 6: Handle Responses and Archive

Capture each response status. Add a Condition on the response: 2xx adds the row IDs to a success list, anything else (4xx/5xx) adds the row plus the error body to a failure list. After the loop, write a summary to slack with the counts ("Uploaded 480 records, 2 failed"). Move the source CSV to an archive folder with the ftp rename tool so it isn't reprocessed.

Tips

Sample first - in early dev, slice the file to 5 rows with the csv slice tool and POST against a staging endpoint until the shape is right.
Compress when allowed - if the API accepts Content-Encoding: gzip, gzipped payloads are much faster on large files.
Idempotency keys - send a deterministic key per record (file:row_index) in a header if the API supports it, so retries don't create duplicates.

Common Pitfalls

Numbers as strings - CSV is text. "0001" becomes 1 after Number() and you lose the leading zero. If leading zeros matter (zip codes, product codes), keep them as strings.
Hidden BOM - the first cell's key turns into id instead of id. The CSV connector strips it when you set the encoding explicitly.
Embedded commas and quotes - well-formed CSV escapes them with double-quotes; poorly-formed CSV doesn't. The parse tool's info output tells you if rows have inconsistent column counts.
API rate limits - looping one POST per row against a 60/min API on a 10,000-row file is a half-day job. Either batch the array or set concurrency low to space the calls.

Testing

Drop a 5-row test file. Inspect each stage's output: the parsed JSON, the transformed JSON, the validation result, the API response. Confirm one record made it end-to-end and the response body looks right. Then test with a file that has one deliberately-bad row and confirm the quarantine branch catches it. Only after that should you point the workflow at a production-size file.