Five Stars dgm
Five Stars

Load Any CSV to Temporary table of OracleDB

Hi

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

  • Data Integration
Tags (2)
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: Load Any CSV to Temporary table of OracleDB

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

dynamicSchema.PNG

8 REPLIES
Seven Stars

Re: Load Any CSV to Temporary table of OracleDB

what it mean - any csv?

how look Your problem?

-----------
Five Stars dgm
Five Stars

Re: Load Any CSV to Temporary table of OracleDB

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

Seven Stars

Re: Load Any CSV to Temporary table of OracleDB

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

dynamicSchema.PNG

 

Hope it helps. 

 

Seven Stars

Re: Load Any CSV to Temporary table of OracleDB

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

-----------
Five Stars dgm
Five Stars

Re: Load Any CSV to Temporary table of OracleDB

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

Seven Stars

Re: Load Any CSV to Temporary table of OracleDB

Just a single column with data type defined as dynamic.

Let me know if it works. 

Five Stars dgm
Five Stars

Re: Load Any CSV to Temporary table of OracleDB

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

 

Please, can you check the below image?

 

tOracleOutput.JPG

DB Type : Dynamic not available.

 

 

Seven Stars

Re: Load Any CSV to Temporary table of OracleDB

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

dynamicSchema.PNG