topic Loop through .csv at location x & create db tables automatically in Talend Studio

Loop through .csv at location x & create db tables automatically

Anonymous — Sat, 16 Nov 2024 06:22:08 GMT

Dear Talend Community,

We use the most recent version of Talend Open Studio and have about 15 .csv files that we need to import into our DB2 DB.
Is it possible to build a single job that imports those .csv files (via pre-defined list or just all
files at location x) and creates a DB2 table or drops and recreates and existing one?

Re: Loop through .csv at location x & create db tables automatically

fdenis — Mon, 11 Mar 2019 14:22:59 GMT

yes it's possible but hard on open studio, there is no dynamic column types.
so think about generation sql insert in place of getting data.

good luck

Re: Loop through .csv at location x & create db tables automatically

fdenis — Mon, 11 Mar 2019 14:24:30 GMT

think about time…
how many time to do it 15 time?
time of build and time of debug...

Re: Loop through .csv at location x & create db tables automatically

Anonymous — Mon, 11 Mar 2019 15:40:41 GMT

Hi,
Thanks for the feedback.
I see.
I think I made some progress using tFileList, tFileInputDelimited, tLogRow and tDBOutput
using ((String)globalMap.get("tFileList_1_CURRENT_FILE")) in tDBOutput for the "Table Name".
However, my ((String)globalMap.get("tFileList_1_CURRENT_FILE")) has usually odd naming and
file formats, I need to map all my 15 ((String)globalMap.get("tFileList_1_CURRENT_FILE")) to
DB-naming that I chose. I wonder if tMap could map and create a formated
_CURRENT_FILE_MAPPED_ or so.

The .csv files are small, non has more than 2000 rows.
Rather than doing 15 jobs I want to have one job that does it dynamic.

Re: Loop through .csv at location x & create db tables automatically

akumar2301 — Mon, 11 Mar 2019 18:56:20 GMT

@ph12345

What about schema of table ? Schema will change with file?
If it is same , then you could make it dynamic.

Otherwise open studio, you needto create 15 subjob for each diff schema

Re: Loop through .csv at location x & create db tables automatically

fdenis — Tue, 12 Mar 2019 09:03:56 GMT

so you have to dinamicaly build create table script first you are in csv so all your fields are string… Varchar
you can check the max length of each.
then create te insert query.

Re: Loop through .csv at location x & create db tables automatically

Anonymous — Wed, 13 Mar 2019 08:27:59 GMT

Hi,

thanks for the replies.

@abhishekKUMAR

@fdenis

((String)globalMap.get("tFileList_1_CURRENT_FILE")) is already the dynamic schema/table name
that is used for every file (iterated). So I can use that inside
the tDBOutput "Table Name" Textbox.
The table is then created if not already existing (that is also a setting).
However, ((String)globalMap.get("tFileList_1_CURRENT_FILE")) will, in my case, contain
.csv at the end or others and I need to Map it to a proper, DB table object name.

example.png

Re: Loop through .csv at location x & create db tables automatically

fdenis — Wed, 13 Mar 2019 08:49:58 GMT

you cannot use this component due to open version.
there is no dynamic type.
you have to user tFileInputFullRow and tDBRow.
and a tmap to convert the string of data to sql create table and sql insert string