Page 1 of 1
Readin data
Posted: Wed Sep 09, 2009 10:48 pm
by swerajan
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?
Posted: Wed Sep 09, 2009 11:41 pm
by kiran259
Is the quote "none" here? else single or double quote present.
Kiran Vaduguri
Posted: Wed Sep 09, 2009 11:44 pm
by swerajan
There are no quotes there in the file. !! It is a file from mainframe system without quotes
Posted: Thu Sep 10, 2009 12:49 am
by ArndW
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.
Posted: Thu Sep 10, 2009 1:24 am
by swerajan
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
Posted: Thu Sep 10, 2009 1:28 am
by swerajan
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
Posted: Thu Sep 10, 2009 1:56 am
by ArndW
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.
Posted: Thu Sep 10, 2009 5:46 am
by chulett
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.
Posted: Thu Sep 10, 2009 5:48 am
by kiran259
Swerajan:Did you get the solution?
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.
Posted: Thu Sep 10, 2009 6:47 am
by srinivas.g
FIELD(In.String,'~',2) -->name clumn
FIELD(In.String,'~',3) --->balance column
Posted: Thu Sep 10, 2009 6:48 am
by srinivas.g
FIELD(In.String,'~',2) -->name clumn
FIELD(In.String,'~',3) --->balance column
Posted: Thu Sep 10, 2009 7:06 am
by ArndW
Actually, more like:
Code: Select all
FIELD(In.String,'~',1,DCOUNT(In.String,'~')-1) -->name clumn
FIELD(In.String,'~',DCOUNT(In.String,'~')) --->balance column