topic Convert Json data to csv format in Talend Studio

Convert Json data to csv format

Anonymous — Sat, 16 Nov 2024 08:47:58 GMT

Hi All,

I have my input as json file and I need to convert the same as csv format. Could you please help me on the same?

Can we use tExtractJSONFields ?

Thanks in advance

Re: Convert Json data to csv format

Anonymous — Mon, 29 Jan 2018 09:41:38 GMT

Hello,

The tExtractJSONFields component is used to extract the desired data from JSON fields based on the JSONPath or XPath query and tFileInputJson component is used to extract JSON data from a file and transfer the data to a file, a database table, etc.

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

Re: Convert Json data to csv format

Anonymous — Mon, 29 Jan 2018 09:43:51 GMT

I will be receiving a Json file which contains details of 40 odd fields. I need to accept the JSON file and convert it to a csv file. The csv file must be uploaded to a particular site.

Re: Convert Json data to csv format

manodwhb — Mon, 29 Jan 2018 10:32:30 GMT

tFileInputJson component is used to extract JSON data from a Json file.

Re: Convert Json data to csv format

Anonymous — Mon, 29 Jan 2018 10:39:45 GMT

So i should use the tFileInputJson to extract the desired fields from the Json file and in the components section of the same, I must mention JsonPath as Read By and the csv Filename and path should be mentioned in the Filename section right?

Re: Convert Json data to csv format

manodwhb — Mon, 29 Jan 2018 11:53:30 GMT

Yes

Re: Convert Json data to csv format

manodwhb — Thu, 01 Feb 2018 07:19:46 GMT

is your issue fixed?

Re: Convert Json data to csv format

Anonymous — Thu, 06 Sep 2018 20:00:18 GMT

@manodwhb @xdshi I am also trying to do the same, I've created a job but not able to extract rows from the JSON file, My job runs successfully but only extracts a single row, My file is like below:

{"Request_Time":"12345",
"Hostname":"https://www.abc.com",
"PID":"12345",
"Visitor_Id":"12345",
"Page":"CartPage",
"Location":"www.abc.com/gallery.asp?",
"Session_ID":"",
"User_Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36",
"Geo_Info":"",
"Data_Layer":[
{"account":"1305",
"customerid":"12345",
"event":"productDataReady",
"isCriteo":"True",
"pagetype":"CartPage",
"siteType":"d",
"NonComplianceDescription":"",
"SaleAmtWithOutTaxAndShipping":null,
"SaleAmtWithTaxAndShipping":null,
"billingTo":null,
"conversionValue":null,
"deduplication":null,
"exchangeRate":null,
"localCurrencyCode":null,
"localItemTotal":null,
"localItemTotalWithTaxShipping":null,
"new_customer":null,
"orderID":null,
"page":{"pageInfo":
{"pageName":"CartPage",
"destinationURL":"www.abc.com/gallery.asp?",
"breadCrumbs":"",
"pageType":"CartPage",
"siteType":"d",
"isCustomer":"False",
"loginMethod":"1",
"PID":"12345",
"customerServiceAgent":"",
"userID":"12345",
"ABTest01":"","ABTest02":"","ABTest03":"","ABTest04":"","ABTest05":"","ABTest06":"","ABTest07":"",
"gaAccount":"UA-15",
"sessionid":"12345",
"cartkey":"12345"}},
"user":{"segment":"",
"profile":{"profileInfo":
{"profileID":"1234",
"userName":"",
"email":"",
"emailHash":""}}}

I want to extract "Request_Time","Hostname","PID","Visitor_Id" and few fields from "Data_Layer", I am attaching screenshot of Job I built.

Please help.

Thanks,

Alok

JSON_EXtract.JPG
JSON_Input.JPG

Re: Convert Json data to csv format

Anonymous — Fri, 07 Sep 2018 06:21:27 GMT

it seems something is wrong with the structure you´ve postet.

the data layer array is openened

"Data_Layer":[

...

but never closed. I don´t find a corresponding "]" bracket.

Please check first whether you´ve really posted the correct structure and if not post the correct one.

regards

Re: Convert Json data to csv format

Anonymous — Sat, 08 Sep 2018 01:35:12 GMT

@odisys Thanks for reply, I am attaching two files one with single JSON document in it and other with multiple JSON documents in a single file, When I am trying to read the file with single JSON document, I am able to parse the values I want , but running into problems when trying to parse a multiline JSON file.

The error I am getting is 'Cannot determine next state'.

File_with_multiple_record.txt
File_with_Single_record.txt

Re: Convert Json data to csv format

Anonymous — Sat, 08 Sep 2018 07:52:09 GMT

Hi AlokGarg,

there is still problem with your multiple records file.

It misses the comma after the first (or before the second) "object" (object = the structure that starts with Request_Time).

Also you need array brackets around your whole structure because you have multiple objects on the same hierarchy level.

I have added a file with the corrected version.

Because this is more a JSON than a Talend question, i would recommend you to check your file/files first whether they are correct. I am using an online tool where you can post your json, execute JSON-Path queries and see the results.

http://www.jsonquerytool.com/

there are othe tools/webpages out, just google for it, if you need another.

Post your structure and enter some queries like:

$.*

$[0].Location

$[0].Data_Layer[0].products[2]
$[0].Data_Layer[0].products[*].name

I did not look at your Talend Job, first the JSOn has to be corrected.

Best regards, odisys

corrected_file_with_multiple_record.txt

Re: Convert Json data to csv format

Anonymous — Wed, 12 Sep 2018 01:26:27 GMT

@odisys Thank you so much for your reply, I corrected the file manually and I could run the job successfully, but the input file I am getting is coming in the format I have posted, without ' [ ' in starting and no ' , ' separating the multiple JSON objects in the file, is there a way I can handle it in my JSON components, or is there a way to modify that raw file using any talend component and loading it to another JSON file with the proper format?

Thanks,

Alok

Re: Convert Json data to csv format

Anonymous — Wed, 12 Sep 2018 11:26:33 GMT

Hello @AlokGarg

A little bit of quick and dirty java code in a tJavaRow e.g. should do it.

Read your original JSON file by tFileInputRaw component:

Connect it with a tJavaRow.

tJavaRow should look like that inside:

>>>

String newJSON = input_row.content.toString();

// add "," to root repeating objects
newJSON = newJSON.replace("{\"Request_Time\":", ",{\"Request_Time\":");
// remove "," from first root repeating object
newJSON = newJSON.replaceFirst(",", "");
// add array brackets
newJSON = "[" + newJSON + "]";

output_row.content = newJSON;

// System.out.println(newJSON);

>>>

connect tJavaRow to fileOutputRaw component:

fileOutputRaw like that:

Parse your new JSON file instead of the original one.

Tell the guys from the source-system which produce the JSON to deliver correct data to you

in the future

best regards

Re: Convert Json data to csv format

Anonymous — Thu, 13 Sep 2018 00:38:17 GMT

@odisys Thank you so much for this, this is really helpful