Hi
I have a sequential file with 2 columns and delimited by ~ .
If the data in the file is somthingike this
Name~Balance
~~~2000
Are there any ways to read the first 2 ~ into the first column and place the balance amount in the balance column?
Declare the file as a long VarChar() or Char() string with no delimiters. Then, in a transform stage, use function such as DCOUNT() and FIELD() to parse the string into the 2 components you wish.
Actually i missed out a point in my question.
The data would look somehting like this. even if it comes likes this i need to handle it in the ETL. Will the same logic of yours work for this scenario Arnd?
Name~Balance
~~~2000
sss~9000
bbb~1000
Actually i missed out a point in my question.
The data would look somehting like this. even if it comes likes this i need to handle it in the ETL. Will the same logic of yours work for this scenario Arnd?
Name~Balance
~~~2000
sss~9000
bbb~1000
Yes, but you need to handle this in your transform. With DCOUNT(In.String,'~') you find out how many tilde you have, then with FIELD(In.String,'~',nnn) you can pick out field nnn from the string. The rest is up to you and your business logic.
Why not just have your Mainframe folks stop providing you with an invalid file? Meaning, correct the delimiters so you don't have to screw around like that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
Arnd:Even if I count no. of tildes,FIELD function looks into delimeter,how can I distinguish starting two tildes with actual delimeter '~'?Could not see complete post because of premium member constraints.
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
</a>
