Six Stars

Generic File Loader

Hi Everyone

 

I have a requirement where I will be getting approx. 10-12 files with varied schemas.

I will need to load them to their respective database tables.

 

There will be very little manipulation, such as:

1.) One Sequence Number Field will be extra  on the table(basically row_num sort of a thing)

2.) One Date_load field will be extra, which will be derived from filename.

 

How do I achieve this in one generic job?

 

Example:

File Name: TableA_04-07-2017.csv

 

File Contents:

col1,col2,col3

ABC,123,XYZ

DEF,456,PQR

 

Table Name: TableA_Staging

 

Expected Output on Table:

1,ABC,123,XYZ,04-07-2017

2,DEF,456,PQR,04-07-2017

 

Regards,

Rabi

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

Re: Generic File Loader

Was able to resolve the issue.

The file needs to have a header and the column names on the header needs to adhere to the column names on the table.

10 REPLIES
Employee

Re: Generic File Loader

Use the dynamic schema functionality in the subscription version of the product.

 

You will read the name of the file, use string manipulation to extract the table name from the file name, then generic job to read the file using dynamic schema, send to a tmap, add the additional sequence number and date column to the tmap, and out to your database.  The output component will use the table name you extracted from the file name.

 

 

Six Stars

Re: Generic File Loader

Hi @iburtally

 

I am using dynamic schema now. And am able to transform fields that are present on the files using tJavaFlex.

 

But not sure how to add extra fields.

 

Can you show me manipulate dynamic schema on the tmap component.

 

Regards

Rabi Panda

Six Stars

Re: Generic File Loader

Was able to do it. But now I am getting an error that says

[ERROR]: sat_control.testdynamicschema3_0_1.testDynamicSchema3 - tOracleOutput_1 - ORA-00917: missing comma

 Any ideas?

Employee

Re: Generic File Loader

Can you change the order and put the dynamic columns at the end of the schema?  Just try that

Six Stars

Re: Generic File Loader

@iburtally

Tried that... Got the same error!

Also tried writing to a file and the read all fields as dynamic instead of part dynamic and part conventional fields.

Got another error after that : ORA-00928: missing SELECT keyword.

Six Stars

Re: Generic File Loader

@iburtally Any ideas!

Employee

Re: Generic File Loader

This is difficult to diagnose by post like this.  Do you have an annonymised job that you can export and attached to show what problems you are having?

Six Stars

Re: Generic File Loader

 
Six Stars

Re: Generic File Loader

Was able to resolve the issue.

The file needs to have a header and the column names on the header needs to adhere to the column names on the table.

Employee

Re: Generic File Loader

That's great.  Been busy hence the delay in responding.