insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

Highlighted
Six Stars

insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

Hi,

 

In this job I want to insert all datas/rows from excel file to another table when first table before doesn't contain excel filename.

4.PNG 

 

in my job above, i successfully create job iterating insert filename from each excel file to table. If table contain excel filename then error message will show up like at picture below :

 

2.PNG

 

I have selected inner join reject condition in output flow to gather all the records which do not have a file name present, look at the picture below.

 

- The row1 is column from excel.

- out2 is first table that will filled with excel file name.

- otoid is second table that will filled with all rows from excel if fisrt table doesn't contains current excel filename.

 

3.PNG

 

As you can see, my job doesn't work as I want.

Can you help me how to design it?

Thanks.


Accepted Solutions
Employee

Re: insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

Hi,

 

   For your use case, you will need to first extract the distinct file names and store them in a a tHashOutput (you can store in a file or temporary table also)

image.png

 

In the second subjob, you will have to call this distinct file names as lookup and do inner join as shown below. All the matched records will go to out flow and all the unmatched records will go to reject flow.

 

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 


All Replies
Employee

Re: insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

Hi,

 

   For your use case, you will need to first extract the distinct file names and store them in a a tHashOutput (you can store in a file or temporary table also)

image.png

 

In the second subjob, you will have to call this distinct file names as lookup and do inner join as shown below. All the matched records will go to out flow and all the unmatched records will go to reject flow.

 

image.png

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

Six Stars

Re: insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

Ok thanks for your help. I'll try it.
Six Stars

Re: insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

@nikhilthampi wrote:

 

In the second subjob, you will have to call this distinct file names as lookup and do inner join as shown below. All the matched records will go to out flow and all the unmatched records will go to reject flow.

 

image.png

Hi @nikhilthampi,

 

I think i can't do inner join between row2 and row3 because I got excel filename from tFileList not from excel file itself. So I can't get matched or rejected output.

Any suggestion?

Thanks.

Employee

Re: insert all datas/rows from excel file to another table when first table before doesn't contain excel filename

Hi,

 

    I believe you did not understand the full solution. You have a column called Nana_File in target database, which is rejecting the data if there is a duplicate value. So you should be able to take the distinct values of file names from DB itself right?

 

    Once the distinct column names from table is extracted, you should be able to use it as lookup in next subjob. If you are still facing issue, could you please share the job flow screenshots you have created and the component screen shots. It will give us more idea about what you are trying to achieve.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Six Stars

Re: insert all datas/rows from excel file to another table when first table before doesn't contain excel filename


@nikhilthampi wrote:

Hi,

 

    I believe you did not understand the full solution. You have a column called Nana_File in target database, which is rejecting the data if there is a duplicate value. So you should be able to take the distinct values of file names from DB itself right?

 

    Once the distinct column names from table is extracted, you should be able to use it as lookup in next subjob. If you are still facing issue, could you please share the job flow screenshots you have created and the component screen shots. It will give us more idea about what you are trying to achieve.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)


Ok, I understand now what for first subjob.

Thanks for your help.

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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog