One Star

How to insert a parent and many child entries from a CSV file to mysql

Hi,
I'm stuck with a job in Talend. I need to insert an entry in parent table and many child entries for its parent from a CSV file. I have inserted record in parent and I dont know how to insert multiple records in the child table and it has a foreign key relation. All Ids, primary key, are auto incremented so i couldn't how to assign the foreign id to child table. I tried it with tJoin but I can only do it for manually assigning primary key. Kindly help and thanks in advance.

Thanks,
Senthil
25 REPLIES
Moderator

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Senthil,
Could you elaborating your case with an example with input and expected output values to us so that we can understand your requirement more clearly and design a proper and precise work flow for you.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

Re: How to insert a parent and many child entries from a CSV file to mysql

a pseudo-code based on the whole information that you have provided:
open connection (transaction is implied)
readFile -> insertInParent -> collect PK just inserted
-> insert into Child
-> collect PK just inserted
-> insert into Child
-> collect PK just inserted
<EOF>
onSubjobOK -> commit tran
onSubjobFail -> rollback tran
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Senthil,
Could you elaborating your case with an example with input and expected output values to us so that we can understand your requirement more clearly and design a proper and precise work flow for you.
Best regards
Sabrina

Hi Sabriba,
Thanks for your reply. I have a CSV file as below.
Name Age Course
-----------------------------------------------------
Mark 23 CS
Mark 23 CE
Mark 23 DB
John 22 ACN
John 22 Maths
Tom 21 Physics

This is a sample CSV. I need to insert Name and Age in my master table and Course in the Child table. The table structure as follows.

Student_DB
Id Name Age
1 Mark 23
2 John 22
3 Tom 21

Course _Student_DB
Id Course Student_Id
1 CS 1
2 CE 1
3 DB 1
4 ACN 2
5 Maths 2
6 Physics 3

Can you kindly help me please.

Thanks,
Senthil
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

a pseudo-code based on the whole information that you have provided:
open connection (transaction is implied)
readFile -> insertInParent -> collect PK just inserted
-> insert into Child
-> collect PK just inserted
-> insert into Child
-> collect PK just inserted
<EOF>
onSubjobOK -> commit tran
onSubjobFail -> rollback tran

Hi,
Thanks for your reply. This will insert all the parent entry first and then the child entries right. Correct if I'm wrong. Is thr any other way to create the parent entry and its child simultaneously.

And from your reply how to join last inserted id and the child table, because i tried it and I'm unable to do it, as it create a loop. I avoided the loop by creating a copy of the input file and again I'm unable to connect the mysqllastinsertid to tmap of the child table. Kindly a suggest a way for this too.

Thanks,
Senthil
Moderator

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
I create an example job to show how to insert a parent and many child entries from a csv file to Mysql, hope it will give you hints and be helpful for all of community users who may have the same request in future. A key point in this job is to do an inner join on tMap_1 to get the entries already exists in the master table, and the ones do not exists. For more details, please see my screenshots. Let me know if you have any questions. Also, if you want to get this example job, please email me!
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
I create an example job to show how to insert a parent and many child entries from a csv file to Mysql, hope it will give you hints and be helpful for all of community users who may have the same request in future. A key point in this job is to do an inner join on tMap_1 to get the entries already exists in the master table, and the ones do not exists. For more details, please see my screenshots. Let me know if you have any questions. Also, if you want to get this example job, please email me!
Best regards
Sabrina

Hi Sabrina,
Thanks a lot for the reply. I have few doubts in the design.
1. The course field is not a field in my master table. Its only available in the child table. So the master table is accepting the column course. While inserting the record an error occurs "Unknown column 'Course' in 'field list'Unknown column 'Course' in 'field list'". If I'm not adding it to the master table, I'm able to insert the record.
2. Due to this I'm unable to get the value of course at 'tMap2'.

I fixed this issue by the following way.
1. I created a duplicate tFileXMLInput file pointing to the same input xml and passed it as input to 'tMap2'. Here I used join condition to match the master table columns i.e. Name from mysqlLastInsertId and the Name column from the input file and created the child entry with Course column.
I need to know whether this is a right way to do it or is there any other configuration to fix it.

I have one more doubt related to this concept.
As the input file is huge, I split the file into small files and I used tFileList to iterate through the directory and pass it to the tFileInputXml(in previous case I pointed only one xml to tFileInputXML). So when Iam doing it, I need to paste another tFileInputList for the input at 'tMap2'. On doing so, the join condition at 'tMap2' is not working fine. I dont know where I'm making the mistake, kindly guide me.

If the first issue is solved by not adding a duplicate copy as input to 'tMap2' then I dont need to bother about the tFileList issue. Or If I need to add a duplicate copy, can you suggest me to do the configuration for the two tFileList.

Thanks in advance.

Thanks,
Senthil
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
I create an example job to show how to insert a parent and many child entries from a csv file to Mysql, hope it will give you hints and be helpful for all of community users who may have the same request in future. A key point in this job is to do an inner join on tMap_1 to get the entries already exists in the master table, and the ones do not exists. For more details, please see my screenshots. Let me know if you have any questions. Also, if you want to get this example job, please email me!
Best regards
Sabrina

Hi Sabrina,
Thanks a lot for the reply. I have few doubts in the design.
1. The course field is not a field in my master table. Its only available in the child table. So the master table is accepting the column course. While inserting the record an error occurs "Unknown column 'Course' in 'field list'Unknown column 'Course' in 'field list'". If I'm not adding it to the master table, I'm able to insert the record.
2. Due to this I'm unable to get the value of course at 'tMap2'.

I fixed this issue by the following way.
1. I created a duplicate tFileXMLInput file pointing to the same input xml and passed it as input to 'tMap2'. Here I used join condition to match the master table columns i.e. Name from mysqlLastInsertId and the Name column from the input file and created the child entry with Course column.
I need to know whether this is a right way to do it or is there any other configuration to fix it.

I have one more doubt related to this concept.
As the input file is huge, I split the file into small files and I used tFileList to iterate through the directory and pass it to the tFileInputXml(in previous case I pointed only one xml to tFileInputXML). So when Iam doing it, I need to paste another tFileInputList for the input at 'tMap2'. On doing so, the join condition at 'tMap2' is not working fine. I dont know where I'm making the mistake, kindly guide me.

If the first issue is solved by not adding a duplicate copy as input to 'tMap2' then I dont need to bother about the tFileList issue. Or If I need to add a duplicate copy, can you suggest me to do the configuration for the two tFileList.

Thanks in advance.

Thanks,
Senthil
Kindly neglect the previous post I have made small mistake in it

Hi Sabrina,
Thanks a lot for the reply. I have few doubts in the design.
1. The course field is not a field in my master table. Its only available in the child table. So the master table is accepting the column course. While inserting the record an error occurs "Unknown column 'Course' in 'field list'Unknown column 'Course' in 'field list'". If I'm not adding it to the master table, I'm able to insert the record.
2. Due to this I'm unable to get the value of course at 'tMap2'.

I fixed this issue by the following way.
1. I created a duplicate tFileXMLInput file pointing to the same input xml and passed it as input to 'tMap2'. Here I used join condition to match the master table columns i.e. Name from mysqlLastInsertId and the Name column from the input file and created the child entry with Course column.
I need to know whether this is a right way to do it or is there any other configuration to fix it.

I have one more doubt related to this concept.
As the input file is huge, I split the file into small files and I used tFileList to iterate through the directory and pass it to the tFileInputXml(in previous case I pointed only one xml to tFileInputXML). So when Iam doing it, I need to paste another tFileInputList for the input at 'tMap2'. On doing so, the join condition at 'tMap2' is not working fine. I dont know where I'm making the mistake, kindly guide me.

If the first issue is solved by not adding a duplicate copy as input to 'tMap2' then I dont need to bother about the tFileList issue. Or If I need to add a duplicate copy, can you suggest me to do the configuration for the two tFileList.

Thanks in advance.

Thanks,
Senthil
Moderator

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
The course field is not a field in my master table. Its only available in the child table. So the master table is accepting the column course. While inserting the record an error occurs "Unknown column 'Course' in 'field list'Unknown column 'Course' in 'field list'". If I'm not adding it to the master table, I'm able to insert the record. You need to deactivate up-datable and insert-able option for course column on the advanced settings tab of tMysqlOutput_1(see my pic)
Could you upload your current work flow into forum so that I can understand your issue more clearly. In addition, I can send the demo job .zip file to you by e-mail. I think it will help you to know the job deeply.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
The course field is not a field in my master table. Its only available in the child table. So the master table is accepting the column course. While inserting the record an error occurs "Unknown column 'Course' in 'field list'Unknown column 'Course' in 'field list'". If I'm not adding it to the master table, I'm able to insert the record. You need to deactivate up-datable and insert-able option for course column on the advanced settings tab of tMysqlOutput_1(see my pic)
Could you upload your current work flow into forum so that I can understand your issue more clearly. In addition, I can send the demo job .zip file to you by e-mail. I think it will help you to know the job deeply.
Best regards
Sabrina

Hi Sabrina,
Thanks for your reply. I will try this and let you know soon. You can forward me the job to sennatece@gmail.com.
I dont know how to upload the screenshot of my job is their any option to do it.

Thanks,
Senthil
Moderator

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Senthil,
I have tried to send the .zip file to you, but my outlook said "It is invalid address . So do you have other valid E-mail address except for @gmail.com?
You should register and log in as a Community member first, then you'll get a Image upload box that allows to uplaod screen captures and images up to 200KB. see my pic
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Senthil,
I have tried to send the .zip file to you, but my outlook said "It is invalid address . So do you have other valid E-mail address except for @gmail.com?
You should register and log in as a Community member first, then you'll get a Image upload box that allows to uplaod screen captures and images up to 200KB. see my pic
Best regards
Sabrina

Hi Sabrina,
You can try this email id sennat_26@yahoo.co.in. I have tried the previous issue and now it is working fine. I will register and upload the job design soon.
I have few more doubts in the design.
1. As the master and child relationship is one to many, I have duplicate master entries in the input file. So duplicate records are getting inserted. I tired using tUnique before tMysqlOutput1 that is the master table and the unique is getting inserted but now the child entry is only one as I have made it unique. How I can overcome this issue.
2. To test the mysql rollback I hard coded the foreign key value in the child table (that is master entry id as 1 which does not exists). So correct error message is thrown by the child table but the master table entries are created and roll back is not happening. This testing is correct for mysqlrollback.

Kindly help me out.
Thanks,
Senthil
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Sabina,
I like to do same but not with any database. I want to retrieve lastinserted id from MDM.
Please help me to get that value.
Actually I have some data in XML and i want to add that to MDM.
ques 2) How can i insert multiple value to column
ex: name LanguageKnown
Vishal English,Hindi,Spanish
as Talend MDM provide (0...many) mapping and also in UI with dynamically adding more element.
It is nicely handle in talend UI but how to do it with XML to MDM though job?

Regards,
Vishal Bhuva
Moderator

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi vishalbhuva,
Would you mind opening a new topic for your issue and put it under "Talend MDM" category so that more people in community will see it and share their experience, solution, option with you, thanks a lot.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Sabina,
Is that fine,
http://www.talendforge.org/forum/viewtopic.php?pid=112489#p112489

PLs reply as soon as possible.

Regards,
Vishal Bhuva
Four Stars

Re: How to insert a parent and many child entries from a CSV file to mysql

Hello Sabrina,
Thanks very much for this example and the screenshots; a very helpful reference!
I was able to get my process working based on the examples you shared, with one minor change (that I thought I would share for other people interested):
For tMySQLOutput_1 in the example, I need to use Insert (rather than Insert or Update) and that requires me to go to the Advanced settings tab for that component and set the number of batch rows to 1. (By default, the number is set to 100 which ends up causing the last insert ID to return as a 0.)
I'm not sure if there is an easier way to do things, but I thought I would share anyway.
Thanks again.
Nate
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

I'm not sure if there is an easier way to do things, but I thought I would share anyway.


__________________________
ford repair huntington beach
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

I know this is an older thread, but it helped me tremendously so thank you very much to all who contributed.
The question from Senthil on 3/28/2013:
1. As the master and child relationship is one to many, I have duplicate master entries in the input file. So duplicate records are getting inserted. I tired using tUnique before tMysqlOutput1 that is the master table and the unique is getting inserted but now the child entry is only one as I have made it unique. How I can overcome this issue.
I was able to solve with a simple change:
In the first tMap, under the Lookup properties, next to "Lookup Model" change the value to "Reload at each row", this effectively re-executes the join query after each master row has been inserted, picking up the newly inserted row and avoiding a duplicate master record insert.
Hope this helps, cheers!
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Sabrina,
I know this post is old, but i am facing the same problem (master-detail), so could you send me the sample to my email?. Let me know what is yours for requesting you by that way.
Thanks a lot!
Best regards
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

I share a variation i needed to get a value from the first tMap not mapped to first insert needed on second tmap
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
The course field is not a field in my master table. Its only available in the child table. So the master table is accepting the column course. While inserting the record an error occurs "Unknown column 'Course' in 'field list'Unknown column 'Course' in 'field list'". If I'm not adding it to the master table, I'm able to insert the record. You need to deactivate up-datable and insert-able option for course column on the advanced settings tab of tMysqlOutput_1(see my pic)
Could you upload your current work flow into forum so that I can understand your issue more clearly. In addition, I can send the demo job .zip file to you by e-mail. I think it will help you to know the job deeply.
Best regards
Sabrina

Sorry to Dig up an older thread. Found the post very useful. But don't know how to translate this step in tOracleOutput. i.e. passing a Child Table Field from the Input  via advanced option in the master table tOracleOutput. 
Thanks you
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

@xdshi Could you please upload this example job so we can get it?
Thank you
Nikolaos Papadakis
Moderator

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi Nikolaos Papadakis,
@xdshi Could you please upload this example job so we can get it?

We will try to find and check it and then come back to you as soon as possible.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi all,
I tried to follow xdshi's instructions and I managed to create the respective Talend job (see attached). However it doesn't work......
I can't pass the “course” parameter in the “DoNotExist” path and althought there are the commits for each subJob it seems the loockup “MysqlParent” is ignored???
For your convenience I upload herein also the mysql schema of the db “parent” (two tables) as well as the csv file.
I thank you in advance
N.
parent.sql.sqlmyJob.zip.zipin.zip.zip
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

Hi,
I am new to Talend and I am just started learning. 
I am trying to see if there are any good talent videos which explain step by step that covers most important testing concepts as below :
1) Recorde count check between source and target tables.
2) Duplication count check between source and target tables.
3) Null values count check between source and target tables.
4)  Ensure the key field data is not missing or Null.
5)  Check if the aggregate values and calculated measures are loaded in the fact tables.
6)  Check if CDC has been applied on the incremental load table.
7) Threshold value validation check.  For Example, Age attribute shouldn’t have a value greater than 100. In the date column , the month field shouldn’t have a value greater than 12)
8) Validating parent-child relationship in the data
Would realy appreciate your help in this regard as I have to implement the ablove all test scenarios by developing talend jobs.

Please let me know if you have any job examples for the mentioned above.

Best regards,
Chaitu
One Star

Re: How to insert a parent and many child entries from a CSV file to mysql

 I need One Help