topic Re: Loading data into excel file in Talend Studio

Loading data into excel file

shalinim1 — Sat, 16 Nov 2024 01:11:25 GMT

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

Re: Loading data into excel file

vikramk — Mon, 02 Nov 2020 13:57:47 GMT

Hi @Shalini M ,

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

Re: Loading data into excel file

shalinim1 — Mon, 02 Nov 2020 14:07:28 GMT

@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.

Re: Loading data into excel file

vikramk — Mon, 02 Nov 2020 14:09:45 GMT

Hi @Shalini M ,

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

Re: Loading data into excel file

shalinim1 — Mon, 02 Nov 2020 14:14:56 GMT

@Vikram Kumar PFB

Re: Loading data into excel file

vikramk — Mon, 02 Nov 2020 14:23:18 GMT

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.

Re: Loading data into excel file

shalinim1 — Mon, 02 Nov 2020 14:56:32 GMT

@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.

Re: Loading data into excel file

vikramk — Mon, 02 Nov 2020 15:12:01 GMT

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.

Re: Loading data into excel file

shalinim1 — Mon, 02 Nov 2020 15:20:11 GMT

@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?

Re: Loading data into excel file

vikramk — Mon, 02 Nov 2020 15:50:37 GMT

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.

Re: Loading data into excel file

shalinim1 — Mon, 02 Nov 2020 15:59:27 GMT

Thank you so much @Vikram Kumar .. Will try this 😊

Re: Loading data into excel file

vikramk — Mon, 02 Nov 2020 16:07:21 GMT

Hi @Shalini M ,

Would you mind marking it as solution once done, if it helps your requirement.