Converting Cobol Text amounts to real amounts...

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  April 17, 2009 at 8:02 am

  #136789

  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

• Dave23

  Hall of Fame

  Points: 3903

  More actions

  April 21, 2009 at 7:25 am

  #981407

  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.

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  April 21, 2009 at 7:43 am

  #981426

  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

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  April 21, 2009 at 5:48 pm

  #981886

  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 ...

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel