topic transform N columns into N lines in Talend Studio

transform N columns into N lines

_AnonymousUser — Sat, 16 Nov 2024 14:28:47 GMT

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

Re: transform N columns into N lines

Anonymous — Fri, 18 Jan 2008 08:04:13 GMT

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

Re: transform N columns into N lines

_AnonymousUser — Fri, 18 Jan 2008 08:07:22 GMT

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

Re: transform N columns into N lines

_AnonymousUser — Fri, 18 Jan 2008 08:12:12 GMT

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

Re: transform N columns into N lines

Anonymous — Fri, 18 Jan 2008 08:30:03 GMT

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

Re: transform N columns into N lines

_AnonymousUser — Fri, 18 Jan 2008 08:41:55 GMT

Hi Shong
Thanks for your help ..As i said the headings are dynamically generated.
For eg uppose 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

Re: transform N columns into N lines

_AnonymousUser — Mon, 21 Jan 2008 02:15:25 GMT

Can anybody please help me??

Re: transform N columns into N lines

c0utta — Mon, 21 Jan 2008 06:34:41 GMT

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

Re: transform N columns into N lines

_AnonymousUser — Mon, 21 Jan 2008 06:44:02 GMT

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

Re: transform N columns into N lines

amaumont — Mon, 21 Jan 2008 18:04:13 GMT

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.

Re: transform N columns into N lines

c0utta — Tue, 22 Jan 2008 03:58:19 GMT

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

Re: transform N columns into N lines

amaumont — Tue, 22 Jan 2008 09:16:35 GMT

Thank you cOutta 😉

Re: transform N columns into N lines

amaumont — Tue, 22 Jan 2008 09:53:58 GMT

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"

Re: transform N columns into N lines

Anonymous — Wed, 23 Jan 2008 01:40:32 GMT

Thanks a lot

Re: transform N columns into N lines

Anonymous — Wed, 23 Jan 2008 08:53:41 GMT

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

Re: transform N columns into N lines

amaumont — Wed, 23 Jan 2008 09:55:55 GMT

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

Re: transform N columns into N lines

Anonymous — Wed, 23 Jan 2008 10:24:07 GMT

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

Re: transform N columns into N lines

amaumont — Wed, 23 Jan 2008 13:51:11 GMT

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.

Re: transform N columns into N lines

Anonymous — Thu, 24 Jan 2008 08:59:42 GMT

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 ??

Re: transform N columns into N lines

amaumont — Thu, 24 Jan 2008 09:14:33 GMT

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