Insert into Web APi with JSON after data transformation

Hello,
I have a transformation that I have been pushing directly into SQL Server tables for years.  I now have a need to also push the converted data to an Endpoint using JSON.

Are there any examples of how to push to an endpoint using JSON?  I reviewed the XML to JSON but it will not do exactly what I want.  And it was converting specifically XML to JSON.

The source file is a flat file (CSV).  One record per row.  

I want to output multiple JSON files, 1 JSON per row to a folder.  Then I want to log into the site, and loop through the JSON files.  If we get back a success response from the API, then we can move the JSON file to a \Compelted folder.  If they fail, I want to move the file to a \Failed folder and send an email to a user about the failure.  

There will be thousands of these per day in the single CSV file.  

Thank you very much!

This was a super cool question !!

My first video of 2026, thanks to you

www.loom.com/share/1819754b3cb2489f85ef47b3609b959f

Changes
1. I am not saving the JSON to a file but instead sending it directly to api
2. my api don't need any authentication , yours might
3. There is a resumable loop option available too.


Admins
1. Having an action CSV to JSON might be helpful
2. Using calculated variable & python, we could do the entire thing in 1 go in http itself , correct?

Hi everyone,

It makes me happy to see our customers helping each other.

The video example is very good, and I hope you’ll find it useful, Tim.

JSON is a very flexible format, but it can also become quite complex.

If you can provide examples of your CSV and JSON files, we’ll be happy to help you with that.

What I need to do is:

1. Read a multi line csv file
2. Perform record transformations such as standardizing phone numbers, doing some lookups and some conversions.
3. I currently write these to a SQL server database and map the fields.
4. I want to add a step that also allows me to write each record to a JSON endpoint.

The CSV file will have around 1000 rows.
It will have 65 or so columns.
VendorName
CustomerName
CustomerAddress
CustomerCity
CustomerState
CustomerZip
CustomerPhone
CustomerEmail
Lookup a Manager based on State and Assign a User ID

I want to loop through the records and insert them into the endpoint validating the status to ensure proper insert or performing error handling if there is a problem.

I will need to map fields and do transformations just as I would doing the SQL server inserts.

I hope that helps. I can provide the specific samples to support if needed but they are customer specific and cannot be publicly posted.

I would also like to understand how the JSON option in the "Writer" works.  

I had originally thought that I could use the Writer, Paste in my full JSON on the JSON tab and those target fields would be available to map to in the Transformer.  

But this would do one giant insert of all rows at once to an endpoint?  But there is nowhere to define the endpoint, so I am very confused.   But this would not work because it would open so many concurrent sessions.

This is why I thought I would write individual JSON's to disk and then loop through those.  

Just trying to think this through as the video posted was very helpful, but looks like it will be very time consuming to build out as I will need a JSON variable node for each value. 

Thanks again and sorry for all the information, just trying to better understand the process. 

 

I would also like to understand how the JSON option in the "Writer" works. 

It will create a giant JSON file, since you want multiple files it will not work for you.

Anyway we will create a basic example for you which you could follow.