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

[Anonymous]

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?

[fdenis]

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

[fdenis]

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

[Anonymous]

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.

[akumar2301]

@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

[fdenis]

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.

[Anonymous]

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

[fdenis]

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