Column order logic clarification

[vamsi.4a6]

I have a file consists of 3 columns col1,col2,col3 and i want to load this data into a table.I am facing below problem while loading data into table.

Number of columns are fixed in file but the order of columns may vary.i can expect any order in the file
ex:-col1,col2,col3
col3,col2,col1
col2,col3,col1 etc

Not sure how to mention metadata in column tab of sequential file and is there anyway to implement this logic in datastage,if it is not possible to implement in datastage i will look into Unix solution.Any input is really apreciated

[chulett]

I'd have a serious chat with whomever is generating that file for you, that sounds completely... nonsensical. Can you post a real example of the file so we can get some idea if the contents can be used to determine which target column they belong to?

And no, I don't see RCP as an option here.

[DataStage_Sterling]

This is a very unusual requirement. How about asking for a schema file for every source file you get? Is it possible? If yes, I believe, you can use schema file for metadata and RCP for column propagation. If not, you do not have a set pattern (consistent) of the problem to resolve. Hence it is not possible to implement in any technology.

[rkashyap]

Can get the file with column headers? If yes, then you can propogate data to the correct destination column for incoming data in a transformer.

[chulett]

I don't see either column headers or a schema file being possible here. I base that on the assumption that they mean the file can contain columns in any order record by record in a single file. This rather than a fixed but 'random' order file by file.

Vamsi, can you clarify please?

[ray.wurlod]

This problem is most easily remedied by talking with whoever is producing the file. Having some kind of threat available would also help.

[vamsi.4a6]

I can not post real data due to security issues in my project but following example
will help for better understanding

empid,ename,sal
1,vamsi,10
2,sam,20
3,ram,30

or

ename,sal,empid
vamsi,10,1
sam,20,2
ram,30,3

or

empid,sal,ename
1,10,vamsi
2,20,sam
3,30,ram


I want to load into a table which consists of columns defined in order empid,ename,sal

[rkashyap]

Request file with column headers. Trap column header row in a Transformer's stage variables ... then using stage variable value, propagate data to the correct destination column.

[vamsi.4a6]

@rkashyap

could you please elaborate more what ever u explained?

[chulett]

You need the column headers. The suggestion is to read them in as data, note which one is which and direct the output for the rest of the lines accordingly.

[rkashyap]

As Craig has said read all rows as data, and direct the output for the rest of the lines accordingly. i.e.

1. Execute the transformer in Sequential mode.
2. Read all columns as varchar.
3. Store values from first row (i.e Header row) in Stage variable.
4. In Output add a constraint to skip Header row.
5. In Derivation, populate output columns based on values of input (and if needed. perform Sqltype coversion). eg Populate EName as

If StgVar1 = "ename" then indat.Col1Val else
If StgVar2 = "ename" then indat.Col2Val else
If StgVar3 = "ename" then indat.Col3Val else
@Null