topic Re: Oracle Database Tables into Csv files in Talend Studio

Oracle Database Tables into Csv files

Anonymous — Sat, 16 Nov 2024 09:45:46 GMT

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

Re: Oracle Database Tables into Csv files

TRF — Thu, 18 May 2017 15:44:42 GMT

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

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 !

Re: Oracle Database Tables into Csv files

Anonymous — Fri, 19 May 2017 14:48:53 GMT

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 :

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 :

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

Re: Oracle Database Tables into Csv files

TRF — Fri, 19 May 2017 18:54:16 GMT

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.

Re: Oracle Database Tables into Csv files

Anonymous — Mon, 22 May 2017 14:56:16 GMT

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