Convert Json data to csv format

Six Stars

Convert Json data to csv format

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

Moderator

Re: Convert Json data to csv format

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Convert Json data to csv format

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.
Thirteen Stars

Re: Convert Json data to csv format

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

Manohar B
Six Stars

Re: Convert Json data to csv format

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?
Thirteen Stars

Re: Convert Json data to csv format

Yes

Manohar B
Thirteen Stars

Re: Convert Json data to csv format

is your issue fixed?

Manohar B
Five Stars

Re: Convert Json data to csv format

@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

Six Stars

Re: Convert Json data to csv format

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

Five Stars

Re: Convert Json data to csv format

@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'.

Six Stars

Re: Convert Json data to csv format

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

 

 

 

 

 

Five Stars

Re: Convert Json data to csv format

@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

Six Stars

Re: Convert Json data to csv format

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:

 

json1.JPG

 

Connect it with a tJavaRow.

tJavaRow should look like that inside:

 

json2.JPG

 

>>>

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:

 

json3.JPG

 

 

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 Smiley Happy

 

 

 

best regards

Five Stars

Re: Convert Json data to csv format

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