Tags: Hi all. I have to read a file Excel and for each record, i need to write on many database tables. I tried to use tFileInput and tDBOutput but i found i can write only on *one* table. How can i perform many operations for each row? By the way, operations for a single row must be in a transaction: if something goes wrong, they have to rollback. Also this, i do not know how to do. Someone may suggest me some hint? Thank you
What database are you using?
For most databases, you have the following types of components:
tConnection
tRow
tCommit
tRollback
The tRow component allows you to execute ONE SQL command for the current row in your input stream (in your case a row from the Excel spreadsheet).
If your right click on most components, under trigger, you'll see OnComponentOK and OnComponentError. So, you would use the Rollback if there was an error.
Perhaps someone else could post a more detailed example - my time is short today.
Hi,
depending on your needs you can do:
a) more than on tRow/Output behind another
b) split your flow with tReplicate (same metadata)
c) use a tMap to split your flow (different metadata)
Bye
Volker
Hi, I have the same need: I read an Excel spreadssheet and I need to insert in 3 tables of my PostgreSQL database. In tableA, I use some columns to insert a row, as this is an auto increment column (SERIAL) I get a new id value = idA. In tableB, I use some other columns, plus idA to insert a row, as this is an auto increment column (SERIAL) I get a new value id = idB. In table C, I use idA and idB to insert a new row ! I have created a few others jobs before, to insert rows on tables with SERIAL columns from Excel ... but so far, I have never needed to re-use the value of the id, so I don't know how to do this.
Hi, sorry I haven't had this problem . So I couldn't give you a solution. But have you tried to use tlLastInsertId ? This could solve your problem. This component is very new. So, depending on your version, not available. Bye Volker
Thank you all for your suggestion, i am still working on them. I am yet a newbie on talend.
iroche i have to do something very similar to your example: for every row from Excel file, i have to generate a keycode which will be inserted in new records on two tables: one containing main info and the other containing additional info.
So, to better explain, i have a single row to be splitted into two tables, *whose column names are different from excel ones*, and some of the field in tables are filled with extra infos (i.e constants, keycode, and evaluated expression ).
By the way, how do i manage to map excel fields into DB table fields when names are different? i tried with a tMap component but cannot get it.
Database is MySQL.
thank you
Hi,
one idea for your foreign key problem: Can you read the table after the insert again?
For mapping Excel to you database: tMap is the right one. The best way is to define your Excel-metadata, than connect to your tMap, define you database (with metadata) and as *last* step map tMap to your database. TOS will ask you if you would like to use the metadata for tMap. Say yes and do your mapping.
Bye
Volker
Hi, depending on your needs you can do: a) more than on tRow/Output behind another b) split your flow with tReplicate (same metadata) c) use a tMap to split your flow (different metadata) Bye Volker
Thank you Volker, it works! I followed exaclty the order you suggested, and now tMap propose me the rigth DB colum names.
In my previous trial, tMap proposed me the file schema for both input and output mapping
To be honest, i do not know why previous map was wrong.
The logic how (or when) TOS adopt the metadata of the previous or next component is sometimes confusing (for me). But this is one of the rules i learned in the time I use TOS. First the database, then the tMap. 🙂 Bye Volker
