topic Re: [resolved] API JSON output to Excel in Talend Studio

[resolved] API JSON output to Excel

Anonymous — Sat, 16 Nov 2024 10:02:21 GMT

Hi All,
I have created a job (screenshot below) using REST Client & ExtractJSON Fields to insert data into excel or into Salesforce object. I need output data from extract json fields in multiple rows but I am getting data in single column with all the row values (Example below). Can you please provide suggestions to achive this.
Example:
Title Status

Re: [resolved] API JSON output to Excel

vapukov — Mon, 27 Feb 2017 08:47:18 GMT

I think You need attach some pictures of Your Job, and provide little more information about API
If Your API return to You element as array and You extract JSON by JSONPath - You must use (where xxx - number in array)
some time more easy trick - remove by replaceAll [] and than use tNormalize
the benefits - no loops, do not need know size of array

Re: [resolved] API JSON output to Excel

Anonymous — Mon, 27 Feb 2017 23:34:40 GMT

Hi Vapukov,
I have uploaded the job image but not sure why its not getting displayed in the post.
I have attached sample output JSON data file from API. I tried to use tnormalizwe in same path due which i was getting multile rows.
API_Ouput.txt.txt
I am not able to add the screenshots or files . Can you please help.

Re: [resolved] API JSON output to Excel

vapukov — Tue, 28 Feb 2017 00:01:06 GMT

It very easy - edit You post unless You will see pictures
In other cases - no way to help You

Re: [resolved] API JSON output to Excel

Anonymous — Tue, 28 Feb 2017 02:05:44 GMT

Hi Vapukov,
Not sure what the issue i am not able to upload image or file.
Please share you email. I can forward the attachmants.
API JSON Output:
{
"count": 1,
"workspaces": {
"10380637": {
"title": "Dylan's Internal Projects",
"status": {
"color": "green",
"message": "Active",
"key": 300
},
"account_features": {
"time_trackable": false,
"has_time_entry_role_picker": true
},
"permissions": {
"can_upload_files": true,
"can_private_message": true,
"can_join": false,
"is_participant": true,
"access_level": "admin",
"team_lead": true,
"user_is_client": false,
"can_change_price": true,
"can_change_story_billable": true
},
"id": "10380637"
}
"10380638": {
"title": "Tom's Internal Projects",
"status": {
"color": "Red",
"message": "Active",
"key": 300
},
"account_features": {
"time_trackable": false,
"has_time_entry_role_picker": true
},
"permissions": {
"can_upload_files": true,
"can_private_message": true,
"can_join": false,
"is_participant": true,
"access_level": "admin",
"team_lead": true,
"user_is_client": false,
"can_change_price": true,
"can_change_story_billable": true
},
"id": "10380637"
}
"10380639": {
"title": "Luisa's Internal Projects",
"status": {
"color": "Yellow",
"message": "Active",
"key": 300
},
"account_features": {
"time_trackable": false,
"has_time_entry_role_picker": true
},
"permissions": {
"can_upload_files": true,
"can_private_message": true,
"can_join": false,
"is_participant": true,
"access_level": "admin",
"team_lead": true,
"user_is_client": false,
"can_change_price": true,
"can_change_story_billable": true
},
"id": "10380637"
}
},
"results":
}

Current OutPut:
Title Status Id

I need optuput in multile rows.
Title Status Id
Dylan's Internal Projects Green 10380637
Tom's Internal Project Red 10380638
Luisa's Internal Project Yellow 10380637

Re: [resolved] API JSON output to Excel

vapukov — Tue, 28 Feb 2017 02:28:24 GMT

it more than enough, I will answer little later today
by the way - what source of data (which database? or provider)

Re: [resolved] API JSON output to Excel

Anonymous — Tue, 28 Feb 2017 02:44:42 GMT

I have getting data from Mavenlink API.
Thanks,
Vikas.

Re: [resolved] API JSON output to Excel

vapukov — Tue, 28 Feb 2017 04:02:05 GMT

ok, let start from the end
The similar problem present with many API systems, and each have different solution (later), but finally, for extract proper information, You must use proper JSON Path:

where (String)globalMap.get("v_key") it is Your ID

as You can see - we make a loop for all possible ID, than use each value as query parameter on next steps
and final result - as expected
This is was The end of story ... How to fetch this list of ID? it always different
Some systems like Google FireBase where I meet this problem first time have special query parameter "shallow" which return on list of Keys
I not familiar with Mavelink, so You need read how to request only ID from workspace
Or You need write custom routine to do this from file.

Re: [resolved] API JSON output to Excel

vapukov — Tue, 28 Feb 2017 04:18:35 GMT

as additional post:
all described above, because in JOSN not included proper id - no any chances to extract Key Name from Talend
all other columns You can extract more easy:

but because id - it is not correct, some other system include _id in JSON body with unique name and all work fine
as example - they own docs, they have id in JSON body, so may be it just Yours error when copy, if all id different, result will be correct

and my first post related to normalise, was about array constructions similar to:

"results":
}

and again if You proper copy JSON, You can extract id list from Results:
it is from documentation web page, but not from Your example

Re: [resolved] API JSON output to Excel

Anonymous — Tue, 28 Feb 2017 12:49:25 GMT

Thank you so much. I am able to get the desired output in tabular format.
Warm Regards,
Vikas.