topic Re: Create Multiple Excel Output Files on json attribute basis in Talend Studio

Create Multiple Excel Output Files on json attribute basis

Anonymous — Mon, 01 Apr 2019 12:15:24 GMT

Hi all,

I am totally new for this data Integration process.

I need to read API and convert that into multiple excel files depending upon the values of one particular attribute.
for example :

{
id:1,
job:student,
Name:ABC,
Date:01,
year:2018
},
{id:1,
job:staff,
name:pr,
Date:02,
year:2019
},
{id:1,
job:student,
name:mys,
Date:01,
year:2019
}

ideally it should create two excel files one named for student and other named for staff and this needs to be generic.
Number of excels should be generated on the basis of number of job types and that is not fixed.

For now what I am doing is
rest-> extract fields-> map-> excel output

my excel looks like :
id name job period
1 ABC student JAN-2018
2 pr staff JAN-2019
3 mys student JAN-2019

expected is :
first excel
id student period
1 ABC JAN-2018
3 mys JAN-2019

second excel
id staff period
1 pr JAN-2018

another way in single excel :
id staff student period
1 - ABC JAN-2018
3 - mys JAN-2019
2 pr - JAN-2018

I maybe doing something wrong.
please let me know if I can do anything to work it as expected.
In simple words one column needs to header and other to be value of it. and condition on basis of which it can create new row for new job profile.

Thanks in advance.

Re: Create Multiple Excel Output Files on json attribute basis

akumar2301 — Mon, 01 Apr 2019 12:26:33 GMT

Output in one excel would be easier as you have to just change the tmap to populate output like you want

Define output scgema as
Id staff student period

Staff = if inputjobname is “staff” then inputname else -
Student = if inputjobname is “student” then inputname else -

Re: Create Multiple Excel Output Files on json attribute basis

Anonymous — Mon, 01 Apr 2019 12:31:29 GMT

job profile is dynamic it can be student staff worker anything which comes dynamically in API

Re: Create Multiple Excel Output Files on json attribute basis

akumar2301 — Mon, 01 Apr 2019 13:52:02 GMT

Split the input based on column value and you could reformat after tfixedflowinput

http://talendhowto.com/2017/10/27/how-to-split-file-depending-on-column-values-2/

Re: Create Multiple Excel Output Files on json attribute basis

Anonymous — Mon, 01 Apr 2019 15:04:24 GMT

Hello ani11,

You can achieve this by generating files with a different name. In this case add the "job" in the fileName.

In attachment a printscreen of the data integration job.

First save your api response in your cache by using a tHashOutput.

Then loop over every record and save it to the correct file. (The iterator will decrease your performance.)

Kr,

Jelle

Screenshot_1.png