transform N columns into N lines

One Star

transform N columns into N lines

Can anybody help me how to go about this.I am a complete beginner to Talend even to any ETL tool..Need to implement batch loading using talend.
Can anybody help me in the below
My input will be a complex join between different tables(oracle databse)
My output is a delimited file(which needs to be ftpd)
My problem is the structure of the output file is as below
id 9429979F6
value 96.5
sectype XYZ
secsubtype XYZ
secsubtyp2 XYZ
Coup 3.32
descript "ABC"
rating1 A
rating1id X97886
id 9229979F6
value 26.5
sectype X2YZ
secsubtype X2Z
secsubtyp2 X2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
and so on
The column names are at the left handside(dynamically generated from database eg rating1 exists in database so is present in the first data)
Can anybody help me how to go about this
Community Manager

Re: transform N columns into N lines

Hi
Use the tOracleInput to get the data from oracle and tFileOutputDelimited component as output file:
tOracleInput--main-->tFileInputDelimited
Please download the user documentation and learn more.
Feel free to post your questions!
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: transform N columns into N lines

Hi shong
Thanks for the reply..Yeah i went thru the user guide and used the same componenets you mentioned.But how do i specify the format of the file to in the same as the one i mentioned ? .. Please help
One Star

Re: transform N columns into N lines

Hi shong
Thanks for the reply..Yeah i went thru the user guide and used the same componenets you mentioned.But how do i specify the format of the file to be the same as the one i mentioned ? The left hand side names are the columns names of the resultant query and should be generated automatically.. Please help

Hi
Use the tOracleInput to get the data from oracle and tFileOutputDelimited component as output file:
tOracleInput--main-->tFileInputDelimited
Please download the user documentation and learn more.
Feel free to post your questions!
Best regards
shong
Community Manager

Re: transform N columns into N lines

Hi
The left hand side names are the columns names of the resultant query and should be generated automatically.

After defining the achema, click the 'Guess Query' button to generated the query statement automatically.(see screenshots)
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: transform N columns into N lines

Hi Shong
Thanks for your help ..As i said the headings are dynamically generated.
For eg Smiley Frustrateduppose my id can have any one or more of colums rating1,rating2,rating3,rating3.S
id 9429979F6 has a rating1 so we should get
id 9429979F6
value 96.5
sectype XYZ
secsubtype XYZ
secsubtyp2 XYZ
Coup 3.32
descript "ABC"
rating1 A
rating1id X97886
id 9229979F6 has rating2 so the this field is included in the details
id 9229979F6
value 26.5
sectype X2YZ
secsubtype X2Z
secsubtyp2 X2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
So here some columns in the output file depend on whether there is value in the database for that column..Hope this explains my problemIs there any way we could implement this
One Star

Re: transform N columns into N lines

Can anybody please help me??
One Star

Re: transform N columns into N lines

Hi abrar,
The issue that you are having is not related to TOS, but is related to the query that you'll need to run against the Oracle table. TOS will only work with a known structure, so an unknown number of columns (rating1, rating2 .. ratingN) is not possible.
I believe it will require a specialised SQL Query in the first instance - then creating the output file is easy.
For the output you've provided, can you post the source Oracle row(s)? What are the column names and the primary key? This is minimal information that is required to help you.
Cheers,
c0utta
One Star

Re: transform N columns into N lines

Hi c0utta
Thanks for the reply ... i will change my sql accordingly..Can i get the output text file in the format i mentioned.The Column heading at the left side and followed by the corresponding values..If yes how do i do it ?
id 9429979F6
value 96.5
sectype XYZ
secsubtype XYZ
secsubtyp2 XYZ
Coup 3.32
descript "ABC"
rating1 A
rating1id X97886
id 9229979F6
value 26.5
sectype X2YZ
secsubtype X2Z
secsubtyp2 X2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
id 922329F6
value 206.5
sectype X22YZ
secsubtype AX2Z
secsubtyp2 AX2YZ
Coup 3.32
descript "ABC"
rating2 A
rating2id Y9789-78
and so on.....
Thanks
Employee

Re: transform N columns into N lines

You could use a tMap to concatenate columns of each row such as this in an only one String typed column in the output table :
- Connect your DBInput to the main input of a tMap.
- Add a variable "separator" with the value as you want (" " for example)
- Add an output and an output column in the tMap :
 
"id" + separator + row1.id + "\n"
"value" + separator + row1.value + "\n"
"sectype" + separator + row1.sectype + "\n"
"secsubtype" + separator + row1.secsubtype + "\n"
"secsubtyp2" + separator + row1.secsubtyp2 + "\n"
"Coup" + separator + row1.Coup + "\n"
"descript" + separator + row1.descript + "\n"
"rating1" + separator + row1.rating1 + "\n"
"rating1id" + separator + row1.rating1id + "\n"
+ "\n"

- Add your file output delimited (with only one column).
Maybe \n will be escaped in tFileOutputDelimited, so you could copy it and modify it to remove this effect if necessary.
It should work, but I don't have time today to test it in a real case.
I will try tomorrow this solution.
One Star

Re: transform N columns into N lines

Hi abrar,
I would do this exactly the way amaumont has explained. My only change would be to include the "rating number" in the data coming back from Oracle. Your schema should be:
id
value
sectype
secsubtype
secsubtyp2
Coup
descript
ratingNumber
rating
ratingID

then the output column in tMap would be the same as amaumont, except for:
...
"rating" + row1.ratingNumber + separator + row1.rating + "\n"
"rating" + row1.ratingNumber + "id" + separator + row1.ratingid + "\n"
+ "\n"

Hope that helps,
c0utta
Employee

Re: transform N columns into N lines

Thank you cOutta ;-)
Employee

Re: transform N columns into N lines

I created the job which realize your need, the valid expression to set should be :
"id" + Var.separator + row1.id 
+ "\n" + "value" + Var.separator + row1.value
+ "\n" + "sectype" + Var.separator + row1.sectype
+ "\n" + "secsubtype" + Var.separator + row1.secsubtype
+ "\n" + "secsubtyp2" + Var.separator + row1.secsubtyp2
+ "\n" + "Coup" + Var.separator + row1.Coup
+ "\n" + "descript" + Var.separator + row1.descript
+ "\n" + "rating" + row1.ratingNumber + Var.separator + row1.rating
+ "\n" + "rating" + row1.ratingNumber + "id" + Var.separator + row1.ratingid
+ "\n"
One Star sid
One Star

Re: transform N columns into N lines

Thanks a lot Smiley Happy
One Star sid
One Star

Re: transform N columns into N lines

If i want to ftp the output file to some loacation ??
Employee

Re: transform N columns into N lines

To send your file output you can use this component :
> Internet > FTP > tFTPPut
One Star sid
One Star

Re: transform N columns into N lines

But the file shoule be ftp'd after its fully written .If i add a tFtpput to my tOutputDelimited,its placing an empty file without the contents
Employee

Re: transform N columns into N lines

You can use a "ThenRun" connection from your first Start component (with green background) to the tFTPPut. So the tFTPPut component will be processed only when your first processing will ended.
You can read the documentation TalendOpenStudio_UG_v2.2_b_EN.pdf at page 44 and 388.
One Star sid
One Star

Re: transform N columns into N lines

Thanks a lot amaumont ,it worked ...
One more help
How do i define a database schema using multiple tables. ie if my input comes from a join over multiple tables ,how do i create my tOracleInputScema ??
Employee

Re: transform N columns into N lines

Maybe you could create a new topic for this subject and precise your request ?
One Star

Re: transform N columns into N lines

Hi cOutta/amaumont
I have a problem with mapping two tables using tMap.Can you please help me out with this..This is related to the below topic..
2069
Abrar
One Star

Re: transform N columns into N lines

sGLgIY <a href="">kztbbwfesfsk</a>, bggqujtwyftr, fderfnuoqlwj,