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

[Anonymous]

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.

 

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 :

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.

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

Can you help me how to design it?

Thanks.

[Anonymous · Accepted solution]

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)

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.

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 🙂

[Anonymous]

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

[Anonymous]

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

Hi @nthampi,

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.

[Anonymous]

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 🙂

[Anonymous]

---

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