Generic File Loader

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

@iburtally Any ideas!

[Anonymous]

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?

[Anonymous · Accepted solution]

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.

[Anonymous]

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