topic Re: Load Any CSV to Temporary table of OracleDB in Talend Studio

Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 12:52:11 GMT

I would to design a job that can take any csv file, and load in oracle database.

I tried to use tOracleBulkExe, but did not succeed to make it work.

Can someone helps me?

Sincerely

dgm

Re: Load Any CSV to Temporary table of OracleDB

vapukov — Wed, 10 May 2017 13:09:31 GMT

what it mean - any csv?

how look Your problem?

Re: Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 13:45:28 GMT

Hi,

Here are some examples:

file1.csv

Name;Surname

Zan;Yan

DON;Peter

ERESH; Matthieu

File2.csv

Id; Cat;Qte

1, School; 23

3;Class; 56

File3.csv

Product;Age;Cost;owner

Pen;1;23;DANGA

...

The sepperator is ";"

I would like the job to identify the number of columns, guest the schema and load the data in table.

I will provide the name of the table as context

Thannks for your help

dgm

Re: Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 13:49:43 GMT

Take a look at the dynamic schema option if you own the Talend enterprise edition.

You basically have to create a tFileInputDelimited Component which should have a single column with its datatype as dynamic.

Pretty useful and fits your use case very well assuming your source files have header.

Also, in your target db component you need to select the action on table as "Drop table if exists and create" (assuming you are using a temporary table of course).

Hope it helps.

Re: Load Any CSV to Temporary table of OracleDB

vapukov — Wed, 10 May 2017 14:00:13 GMT

If You use community version - it never Guess 🙂 all must be pre-defined

Subscription "support" dynamic schema. but it also have a lot of question - and it normal, because it unreal guess all possible cases (if not create all columns as VARCHAR)

and tOracleBulkExec - not support dynamic schema

Re: Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 15:20:36 GMT

Thanks ArvinRapt for your help

But I still have a problem.

It's ok for tFileDelimiterInput, but what should I put as schema for tOracleOutput?

One's more, thanks for your help

tOracleOutput.JPG

Re: Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 15:27:16 GMT

Just a single column with data type defined as dynamic.

Let me know if it works.

Re: Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 15:41:06 GMT

For tFileInputDelimited_1, I've created a single column with type Dynamic. No problem with this component.

Please, can you check the below image?

DB Type : Dynamic not available.

Re: Load Any CSV to Temporary table of OracleDB

Anonymous — Wed, 10 May 2017 15:49:42 GMT

The DB Type will be varchar but your talend data type has to be dynamic.