how to extract the json data which are in single string column and convert to separate columns?

Highlighted
Six Stars

how to extract the json data which are in single string column and convert to separate columns?

Hi all,

 

I have urgent requirement. I have json file which are in single string line and having multiple columns . I need to separate the columns and their value in csv file. Attached the sample file please let me know how can i convert to json to csv file which are in single string value.

 

file is in txt format first convert the txt format to json format and try to convert the json data to csv file.


Accepted Solutions
Eleven Stars

Re: how to extract the json data which are in single string column and convert to separate columns?

input component

 

TalendImage.JPG

Regards
Abhishek KUMAR

All Replies
Eleven Stars

Re: how to extract the json data which are in single string column and convert to separate columns?

you can use textractJsonfield

 

TalendImage.JPG

you need to complete above schema.

 

Sample result

 

d66c6f9c-572f-e911-b8b3-0004ff9d738f|0f79fecc-e7c0-e811-a979-000d3af49c6c|2019-02-13T06:21:43Z|indskr_positiongroups
241e2b1f-572f-e911-b8b3-0004ff9d738f|0f79fecc-e7c0-e811-a979-000d3af49c6c|2019-02-13T06:18:05Z|indskr_positiongroups
d5200cd9-562f-e911-b8b3-0004ff9d738f|0f79fecc-e7c0-e811-a979-000d3af49c6c|2019-02-13T06:16:07Z|indskr_positiongroups
0e9e8390-562f-e911-b8b3-0004ff9d738f|0f79fecc-e7c0-e811-a979-000d3af49c6c|2019-02-13T06:14:05Z|indskr_positiongroups
b2d7fbaa-af2e-e911-b8b3-0004ff9d738f|0f79fecc-e7c0-e811-a979-000d3af49c6c|2019-02-12T10:19:33Z|indskr_positiongroups
d9b9056a-af2e-e911-b8b3-0004ff9d738f|0f79fecc-e7c0-e811-a979-000d3af49c6c|2019-02-12T10:17:35Z|indskr_positiongroups

Regards
Abhishek KUMAR
Six Stars

Re: how to extract the json data which are in single string column and convert to separate columns?

please can you let me know which is that input component u have used. M using talend open studio. can u please attach the job.

 

M facing the below error.

 

net.minidev.json.parser.ParseException: Unexpected End Of File position 0: null
Expected to find an object with property ['AuditDataSet'] in path $ but found 'java.lang.String'. This is not a json object according to the JsonProvider: 'com.jayway.jsonpath.spi.json.JsonSmartJsonProvider'.
Expected to find an object with property ['AuditDataSet'] in path $ but found 'java.lang.String'. This is not a json object according to the JsonProvider: 'com.jayway.jsonpath.spi.json.JsonSmartJsonProvider'.
net.minidev.json.parser.ParseException: Unexpected character (}) at position 0.

Eleven Stars

Re: how to extract the json data which are in single string column and convert to separate columns?

input component

 

TalendImage.JPG

Regards
Abhishek KUMAR
Six Stars

Re: how to extract the json data which are in single string column and convert to separate columns?

thank you so much m able to extract the columns data.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Modern Data Engineering in the Cloud

Learn about modern data engineering in the Cloud

Watch Now

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud reg...

Learn how to deploy Talend Jobs as Docker images to Amazon, Azure and Google Cloud registries

Blog