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

[_AnonymousUser]

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

[Anonymous]

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

[Anonymous]

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

[_AnonymousUser]

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

[_AnonymousUser]

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

[Anonymous]

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

[_AnonymousUser]

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

[_AnonymousUser]

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

[Anonymous]

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

[_AnonymousUser]

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