Convert Json data to csv format

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[manodwhb]

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

[Anonymous]

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?

[manodwhb]

Yes

[manodwhb]

is your issue fixed?

[Anonymous]

@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

[Anonymous]

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

[Anonymous]

@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