Loading data into excel file

[shalinim1]

Hi,

I have a source file where data got added for every time the job runs. I wanted to load that source to excel file. My job follows,

tfileinputdelimited--> tmap--> tfileoutputexcel

I have added a date column in tmap and getting it with the help of talend date generator.

I want the new data to be loaded in outputfile by remaining the existing data from source and generated date in tmap.

for example,

source data is,

1,abc

2,fgr

using tmap I have included the date and time and loaded to output table,

1,abc,2020-11-02 5:21:20

2,fgr,

2020-11-02 5:21:20

If the data is added to source as,

1,abc

2,fgr

3,ytr

by the time

2020-11-02 5:51:40

the output file should be,

1,abc,2020-11-02 5:21:20

2,fgr,

2020-11-02 5:21:20

3,ytr,

2020-11-02 5:51:40

But now its loading in output file as,

1,abc,2020-11-02

5:51:40

2,fgr,

2020-11-02 5:51:40

3,ytr,

2020-11-02 5:51:40

[vikramk]

Hi @Shalini M​ ,

I don't see any difference in desired and obtained output of the file. Could elaborate more.

[shalinim1]

@Vikram Kumar​ ​ Whenever i'm running the job, date and time column was same for every data (Existing in excel and newly loaded data) because it is loading from the Source and I'm generating date and time using tmap.

I want the date to be generated as current date and time for newly loaded ones. and the old generated date for existing ones.

[vikramk]

Hi @Shalini M​ ,

Could you share the ss, how you are generating date and time in tmap

[shalinim1]

@Vikram Kumar​ PFB

 

[vikramk]

Hi @Shalini M​ ,

I don't see any problem with tmap date generation. Can you check the output component settings whether you recreating the excel file or just appending the existing one. Can you share the output ss as well.

[shalinim1]

@Vikram Kumar​ yeah, the generation of date is working. But when I ran a job it is loading as the above picture.

When I run the job again, the last 6 rows alone added but the date and time was same for the previous data's too.

[vikramk]

Hi @Shalini M​ ,

Could you share the entire job ss for each component settings, I believe you are recreating the new excel file instead of appending the existing one. Please check the append existing file option for tfileoutputexcel in basic settings.

[shalinim1]

@Vikram Kumar​ When I tried append existing file. For example, the target table contains 56 rows. and in the next run 6 rows got added to source table.

It is appending the whole 61 rows to the already existing 56 rows. And my target has 117 rows(56 rows from already existing target table and 61 from source table).

Is there any way to only load the non existing data in target?

[vikramk · Accepted solution]

Hi @Shalini M​ ,

You could achieve your requirement like this:

Please ignore the errors in the above ss, as it is just sample.

First Loop: Connect source to map as main connection, do inner join in tmap with your old output excel file and enable Catch lookup inner join reject to true. And catch this reject data in thashoutput component . Please go through below url to explore on "Catch lookup inner join reject"

https://help.talend.com/reader/mjoDghHoMPI0yuyZ83a13Q/7VFow3bHpC71W~iGA3oYnA

For join condition you can use any common columns and unique columns.

Second Loop: you will retrieve the reject data using thashinput component and implement date generation, connect to excel output with append.

Please mark it as solution if it helps.