April 17, 2009 at 8:02 am
Hi, I have a situation here that I need to find the best way to handle:
We have some files that are extracted from Cobol (Flat Files), and loaded into SQL Server using SSIS.
The amounts in those files are specified that way:
So the value -> 0034255} means : -003425.5} since the } is in the negative part.
0034255F can be translated to 003425.56 since F is the positive 6....
Ok, I hate to say that, but that is a pain to translate to a real number, and all of that to save 1 character.. anywayz, does anyone know how to change those efficiently?
Thanks, I've pasted the corresponding table so you can understand better.
VALUE Positive Negative
0 { }
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R
Edited for readability
Cheers,
J-F
April 21, 2009 at 7:25 am
When I had to deal with a similar situation, I treated the numbers with characters as strings, and passed them through a script component that isolated the special character and used a case statement to replace it with the numeric equivalent. In my case, all of them were positive (legacy account numbers) but a similar approach might work in your situation.
April 21, 2009 at 7:43 am
Thanks Dave, I'll look into the script component to do a Big select case in Vb.net, that should do it! Thanks again!
Cheers,
J-F
April 21, 2009 at 5:48 pm
I think I would handle this slightly differently.
I would split the input columns so that the numeric data is in one column and the 'special' character is in another.
I would add (in principle) three derived columns:
1) Sign (1 for positive, -1 for negative)
2) Value (0-9)
3) Final Value = Sign * (orig numeric data + Value)
To work with the special character, I would convert it to its ASCII numeric value and test whether that falls within certain ranges, rather than testing every single letter ...
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply