Oracle Database Tables into Csv files

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

 

Tags (4)

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

All Replies
Fifteen Stars TRF
Fifteen 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

Fifteen Stars TRF
Fifteen 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

Cloud Free Trial

Try Talend Cloud free for 30 days.

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.