Column order logic clarification
Moderators: chulett, rschirm, roy
Column order logic clarification
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
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
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.
And no, I don't see RCP as an option here.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 26
- Joined: Wed Jul 17, 2013 9:00 am
Re: Column order logic clarification
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.
Re: Column order logic clarification
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.
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?
Vamsi, can you clarify please?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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
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