Five Stars

Oracle Database Tables into Csv files

Hello, I am trying to retrieve data from tables to registre it into csv files. Here are the components i am using :

 

tOracleInput_1 -> tFlowIterate_1 -> tOracleInput_2 -> tFileOutputDelimited_1

 

In tOracleInput_1 i am doing this query : "SELECT table_name FROM user_tables"

In tOracleInput_1 : "select * from "+((String)globalMap.get("table_name"))"

In tFileOutputDelimited_1 :  context.directory_output + ((String)globalMap.get("table_name")) + ".csv"

 

I gave the schema of tables like column1, column2, ... up to 50 because i don't know how many columns i have in each table.

 

The job is working, I am retrieving data in csv files. But my issue is that I want to have the columns name in my files but I don't know how to do it.

 

Also I don't know if I am doing it the right way, I just started working on talend 2 weeks ago.

 

Thank you.

 

PS : sorry for my english I am a french student

 

  • Big Data
Tags (4)
1 ACCEPTED SOLUTION

Accepted Solutions
Five Stars

Re: Oracle Database Tables into Csv files

Thank you for your answer !
I found my solution in the comments of the article, the component I was looking for was tDernomalized.

It's working now.

Best regards,
Dregop
4 REPLIES
Nine Stars TRF
Nine Stars

Re: Oracle Database Tables into Csv files

Hi,

 

You can select the column names using user_tab_columns view on Oracle side.

This will give you the list of the columns defined for each table accessible from user_tables.

Now, having these names you can construct the header for each expected CSV file - as your are a student, I let you search a little how... Smiley Wink

Check the column order is the same that the order from "select *".

If yes, you can jump to the final step.

If not, you need to construct the list of column names (for exemple, "nom, prenom, age, adresse") to build the desired select using:

"SELECT " + ((String)globalMap.get("column_list")) + " FROM "+((String)globalMap.get("table_name"))"

Finaly, uncheck the option "Include Header" from tFileOutputDelimited_1 (because you have built your own) and probably remove undesired ";;;..." at the end of most lines.

Hope this helps.

 

Note: ne t'inquiète pas pour ton anglais si même un français a réussi à te comprendre !

 


TRF
Five Stars

Re: Oracle Database Tables into Csv files

Hello and thank you for your answer it helped a lot !

 

I managed to had the column 's names to the file and then had the data to it without erasing the column's names.

 

But here is what my file looks like :

image.png

Is there a way I can put the column's names in each column so it can fit with the data ?

 

I tried to reverse the separators (";" instead of "\n") of the csv file when I registre the column's names in it, the result was close but not as expected :

image.png

I have to admit I don't know what to do now. I searched on the internet and found that I should perhaps use tMap or tNormalized but it don't seems to be working for my issue.

 

Do you have any ideas how to solve this ?

 

Thanks

 

Dregop

Nine Stars TRF
Nine Stars

Re: Oracle Database Tables into Csv files

You need to convert rows to columns before to push the header to the final file. Here a good article about this subject http://bekwam.blogspot.fr/2011/01/tutorial-turning-column-into-rows-with.html?m=1
Let me know if you need more help.

TRF
Five Stars

Re: Oracle Database Tables into Csv files

Thank you for your answer !
I found my solution in the comments of the article, the component I was looking for was tDernomalized.

It's working now.

Best regards,
Dregop