Data ingestion from JSON format to CSV

BLAPOINTE1610678245 — Sat, 16 Nov 2024 00:49:18 GMT

Hello,

One of my customer is struggling ingesting data to AWS S3 buckets from JSON format data returned from an Web Application API call.

He is is using

Talend API generated csv files

to complete the process.

Precisely, he is struggling escaping the line breaks and the double quotes returned in JSON by the API to push the to the CSV.

Do somebody knows any tools in Talend to complete this task.

Here is a sample of the JSON data

returned by the API

{

"@odata.context": "xxx",

"@odata.type": ""xxx",

"@odata.id": "xxx",

"@odata.editLink": "xxx",

"Id": "38c7fb88-d8f6-4444-bca4-a230654eb4ce",

"Deleted": false,

"DateModified": "2020-11-15T19:54:56.907Z",

"DateCreated": "2020-11-15T19:46:07.093Z",

"ALSITEINACTIVAT": null,

"ALSITESCADACODE": null,

"SiteCode": "307",

"SiteComment": null,

"SiteID": 1361,

"SiteLatitude": "48.5548747",

"SiteLongitude": "50.854248",

"SiteName": "xxxx",

"StreetAdress": "(Port Cartier) \"Head Office\" 8878 Road 5555, Block 747\r\n+878 5445 23525+\r\454 8784 8787",

"ZipPostalCode": "PO box 86744"

}

Thank you

Re: Data ingestion from JSON format to CSV

Prakhar1 — Fri, 15 Jan 2021 07:26:21 GMT

take the complete output in a string and then use tJavaRow.

in tJavaRow replace all this escape character with null.

String text = input_row.data;

input_row.data = (input_row.data).replaceAll("\\r","');

input_row.data = (input_row.data).replaceAll("\\n","');

output_row.data = input_row.data;

Then read this data into the tFileOutputDelimited.

Hope this will help.