Did you miss your activation email?

• April 22, 2018, 05:57 AM
• Proudly celebrating 13 years online.
• Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.

### Author Topic: DONE: How do I convert (wtf is this? Cobol?) to numbers? Examples included  (Read 15352 times)

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### DONE: How do I convert (wtf is this? Cobol?) to numbers? Examples included
« on: February 12, 2008, 09:09 AM »
The data is received in a funky format because negative values are not allowed.  The numbers are converted as:

On a positive number:                                On a negative number:

{ = 0                                                         } = 0
A = 1                                                         J = 1
B = 2                                                         K = 2
C = 3                                                         L = 3
D = 4                                                        M = 4
E = 5                                                         N = 5
F = 6                                                         O = 6
G = 7                                                         P = 7
H = 8                                                         Q = 8
I = 9                                                          R = 9

Examples:

0000568G = \$56.87
0001651L = -\$165.13

I need to convert the wacky 0000568G types to currency types.  Anyone got code for this?

I have sample code that does the opposite but reversing code I barely understand is a waste of time if someone already has code to do what I need:

Spoiler
Function format_currency1(ByVal money As String, ByVal width As Integer) As String

'convert to internal format for currency

Dim positive As Boolean

Dim decmoney As Currency

'need to convert to decimal to format number

decmoney = money

'set switch for positive/negative value

If decmoney >= 0 Then

positive = True

Else

positive = False

End If

'ensure there are no negative numbers

decmoney = Math.Abs(decmoney)

'format number to ensure the 2 right characters are the cents and convert back to string

If Left(Right(decmoney, 3), 1) = "." Then

money = decmoney

Else

money = decmoney & ".00"

End If

'omit decimal point

money = Left(money, Len(money) - 3) & Right(money, 2)

If positive = True Then

'positive currency amount

Select Case Right(money, 1)

Case 0

money = Left(money, Len(money) - 1) & "{"

Case 1

money = Left(money, Len(money) - 1) & "A"

Case 2

money = Left(money, Len(money) - 1) & "B"

Case 3

money = Left(money, Len(money) - 1) & "C"

Case 4

money = Left(money, Len(money) - 1) & "D"

Case 5

money = Left(money, Len(money) - 1) & "E"

Case 6

money = Left(money, Len(money) - 1) & "F"

Case 7

money = Left(money, Len(money) - 1) & "G"

Case 8

money = Left(money, Len(money) - 1) & "H"

Case 9

money = Left(money, Len(money) - 1) & "I"

End Select

Else

'negative currency amount

Select Case Right(money, 1)

Case 0

money = Left(money, Len(money) - 1) & "}"

Case 1

money = Left(money, Len(money) - 1) & "J"

Case 2

money = Left(money, Len(money) - 1) & "K"

Case 3

money = Left(money, Len(money) - 1) & "L"

Case 4

money = Left(money, Len(money) - 1) & "M"

Case 5

money = Left(money, Len(money) - 1) & "N"

Case 6

money = Left(money, Len(money) - 1) & "O"

Case 7

money = Left(money, Len(money) - 1) & "P"

Case 8

money = Left(money, Len(money) - 1) & "Q"

Case 9

money = Left(money, Len(money) - 1) & "R"

End Select

End If

'pad left with leading zeros to meet field width requirements

While Len(money) < width

money = "0" & money

Wend

format_currency1 = money

End Function

Hoping for help
Sincerely
Muppet

edit by jgpaiva: added spoiler/code tag
Impossible is nothing
« Last Edit: February 15, 2008, 03:10 AM by jgpaiva »

#### jgpaiva

• Global Moderator
• Joined in 2006
• Posts: 4,727
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #1 on: February 12, 2008, 09:26 AM »
That sounds like coding snack material.

Could you explain it a bit better?

Where are those numbers? In a file? One per line?

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #2 on: February 12, 2008, 09:35 AM »
The numbers start in a flat file (.txt) for EDI.  They are fixed width (not comma delimited)

I use a macro to format the data into columns for a more meaningful Excel spreadsheet with column headings.

Just knowing wtf type of system this is would help me with the conversion to currency.  Obviously it isn't hex

While the sample code works to convert from currency to the (wtf) string, it isn't helful converting from (wtf) string to currency.

ASCII??

Any hints?
Impossible is nothing
« Last Edit: February 12, 2008, 09:37 AM by dementedmuppet »

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #3 on: February 12, 2008, 10:21 AM »
It seems to be a COBOL standard but I haven't determined which standard
(How many standards did they need?? Ugh!)

0000568G = \$56.87
0001651L = -\$165.13

What is the likelihood that any of these will help?
Impossible is nothing
« Last Edit: February 12, 2008, 10:27 AM by dementedmuppet »

#### jgpaiva

• Global Moderator
• Joined in 2006
• Posts: 4,727
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #4 on: February 12, 2008, 10:44 AM »
Actually,the system makes sense. The idea is to have a fixed maximum length for the number. The fact it has 2 types of characters allows it to have the same maximum size equal for both positive and negative numbers.

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #5 on: February 12, 2008, 11:27 AM »
Yes, it is logical and convenient for transmissions but I need to figure out precisely what the standard is so I can write the code to convert it back to currency.

COBOL 'making sense' is why it doesn't die.  From accounting/finance perspective, COBOL is beautiful.  It is the backbone of financial systems since it is the best tool for the job.  Just today, while I'm frustrated, "tool" takes on a different meaning.
Impossible is nothing

#### tinjaw

• Supporting Member
• Joined in 2006
• Posts: 1,927
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help
« Reply #6 on: February 12, 2008, 11:35 AM »
I understand in general want you want to do, i.e. convert from "COBOL" to currency.

What do you want to do in practical terms? Are you looking to input a text file and output a text file? If you already have the data in Excel, write a VBA macro to do the conversion.

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #7 on: February 12, 2008, 01:00 PM »
VBA maro would be ideal

Either to convert the values while keeping them in the Excel cells they currently occupy, or create a new tab that has the converted values.

The sample code is VBA used to create "COBOL" values.  Theoretically, I could edit & rearrange it to reverse the process.  My failure to understand the code makes that difficult.

Any reference sites you've found helpful?

Impossible is nothing
« Last Edit: February 12, 2008, 01:44 PM by dementedmuppet »

#### tinjaw

• Supporting Member
• Joined in 2006
• Posts: 1,927
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help
« Reply #8 on: February 12, 2008, 01:48 PM »
I have never actually done VBA in Excel before, but try this...

Function format_currency2(ByVal money As String) As String
'convert from internal format to currency

Select Case Right(money, 1)
'positive numbers

Case "{"
money = Left(money, Len(money) - 1) & "0"
Case "A"
money = Left(money, Len(money) - 1) & "1"
Case "B"
money = Left(money, Len(money) - 1) & "2"
Case "C"
money = Left(money, Len(money) - 1) & "3"
Case "D"
money = Left(money, Len(money) - 1) & "4"
Case "E"
money = Left(money, Len(money) - 1) & "5"
Case "F"
money = Left(money, Len(money) - 1) & "6"
Case "G"
money = Left(money, Len(money) - 1) & "7"
Case "H"
money = Left(money, Len(money) - 1) & "8"
Case "I"
money = Left(money, Len(money) - 1) & "9"

'negative numbers

Case "}"
money = "-" + Left(money, Len(money) - 1) & "0"
Case "J"
money = "-" + Left(money, Len(money) - 1) & "1"
Case "K"
money = "-" + Left(money, Len(money) - 1) & "2"
Case "L"
money = "-" + Left(money, Len(money) - 1) & "3"
Case "M"
money = "-" + Left(money, Len(money) - 1) & "4"
Case "N"
money = "-" + Left(money, Len(money) - 1) & "5"
Case "O"
money = "-" + Left(money, Len(money) - 1) & "6"
Case "P"
money = "-" + Left(money, Len(money) - 1) & "7"
Case "Q"
money = "-" + Left(money, Len(money) - 1) & "8"
Case "R"
money = "-" + Left(money, Len(money) - 1) & "9"
End Select

format_currency2 = money * 0.01

End Function

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #9 on: February 12, 2008, 03:12 PM »
It works.  If my child did not already have a cool name, he woud be "chaim"
Impossible is nothing

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Hopeless
« Reply #10 on: February 14, 2008, 03:02 PM »
Tinjaw's code works well.  I was supposed to have the reports done two days ago.  My macros were buggy.  Scoured VB help & killed a few trees printing some VB help (hopefully, this addiction to paper will pass).

Yesterday, Excel was getting hung up on converting cells.  Driving home, I realized the problem was likely that I left "#VALUE!" errors in the range I told Excel to convert to numbers.  Brilliant

After six hours of trying to fix the macros, I clicked the little "!" help icon that was beside the data that needed modified.  One option was "help with this error."  The solution was "multiply by 1".  That WOULD explain why there are no VB methods to fix the issue.

See?  Hopeless

Spoiler
Sub PDE_RSPNS2()
'
' PDE_RSPNS2 Macro
' Macro recorded 2/11/2008 by muppet
'
' Keyboard Shortcut: Ctrl+Shift+I
'
"TEXT;W:\Output\30923\Encounter_Data\Archive\PDE Convert to Excel\target.txt", _
Destination:=Range("A7"))
.Name = "target"
.FieldNames = True
.RowNumbers = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 5, 2, 5, 5, 2, 9, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 9, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 9, 1)
.TextFileFixedColumnWidths = Array(3, 7, 40, 20, 20, 8, 1, 8, 8, 9, 2, 19, 2, 15, 2, 1, 1, 1 _
, 10, 3, 2, 15, 1, 1, 1, 1, 1, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 109, 3, 2, 15, 5, 5, 20, 2, 3, 3, 3, 3, 3, 3, _
3, 3, 3, 3, 15)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.ScrollRow = 1

With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
End Sub

Sub convertToCurrency()
'
' convertToCurrency Macro
' Macro recorded 2/13/2008 by muppet
'
' Keyboard Shortcut: Ctrl+Shift+C
'
'Add currency conversion formula to cells in temporary columns
Range("BE9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BF9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BG9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BH9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BI9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BJ9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BK9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BL9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BM9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BN9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BO9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BP9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"
Range("BQ9").Select
ActiveCell.FormulaR1C1 = "=format_currency2(RC[-30])"

'Copy formulas down columns to format all temporary currency cells
Range("BE9").Select
Selection.Copy
Range("BE10").Select
Range("BE10:BE18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BF9").Select
Selection.Copy
Range("BF10").Select
Range("BF10:BF18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BG9").Select
Selection.Copy
Range("BG10").Select
Range("BG10:BG18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BH9").Select
Selection.Copy
Range("BH10").Select
Range("BH10:BH18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BI9").Select
Selection.Copy
Range("BI10").Select
Range("BI10:BI18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BJ9").Select
Selection.Copy
Range("BJ10").Select
Range("BJ10:BJ18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BK9").Select
Selection.Copy
Range("BK10").Select
Range("BK10:BK18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BL9").Select
Selection.Copy
Range("BL10").Select
Range("BL10:BL18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BM9").Select
Selection.Copy
Range("BM10").Select
Range("BM10:BM18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BN9").Select
Selection.Copy
Range("BN10").Select
Range("BN10:BN18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BO9").Select
Selection.Copy
Range("BO10").Select
Range("BO10:BO18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BP9").Select
Selection.Copy
Range("BP10").Select
Range("BP10:BP18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Range("BQ9").Select
Selection.Copy
Range("BQ10").Select
Range("BQ10:BQ18280").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Copy format_currency VALUES to new columns, convert to NUMBER, format as CURRENCY
Range("BE9").Select
Range("BE9:BQ18280").Select
Selection.Copy
Range("BR9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("BR9").Select
Range("BR9:CD18280").Select
Application.WindowState = xlNormal
Application.WindowState = xlNormal
Application.WindowState = xlNormal
Application.WindowState = xlMaximized
Application.WindowState = xlNormal
ActiveWindow.LargeScroll Down:=8
Selection.NumberFormat = "\$#,##0.00_);(\$#,##0.00)"

'Hide columns that contained non-currency data partially processed data
Columns("AA:AM").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SmallScroll ToRight:=15
Columns("BE:BQ").Select
Selection.EntireColumn.Hidden = True

End Sub
'This code block was provided by Chaim Krause
Function format_currency2(ByVal money As String) As String
'convert from internal format to currency

Select Case Right(money, 1)
'positive numbers

Case "{"
money = Left(money, Len(money) - 1) & "0"
Case "A"
money = Left(money, Len(money) - 1) & "1"
Case "B"
money = Left(money, Len(money) - 1) & "2"
Case "C"
money = Left(money, Len(money) - 1) & "3"
Case "D"
money = Left(money, Len(money) - 1) & "4"
Case "E"
money = Left(money, Len(money) - 1) & "5"
Case "F"
money = Left(money, Len(money) - 1) & "6"
Case "G"
money = Left(money, Len(money) - 1) & "7"
Case "H"
money = Left(money, Len(money) - 1) & "8"
Case "I"
money = Left(money, Len(money) - 1) & "9"

'negative numbers

Case "}"
money = "-" + Left(money, Len(money) - 1) & "0"
Case "J"
money = "-" + Left(money, Len(money) - 1) & "1"
Case "K"
money = "-" + Left(money, Len(money) - 1) & "2"
Case "L"
money = "-" + Left(money, Len(money) - 1) & "3"
Case "M"
money = "-" + Left(money, Len(money) - 1) & "4"
Case "N"
money = "-" + Left(money, Len(money) - 1) & "5"
Case "O"
money = "-" + Left(money, Len(money) - 1) & "6"
Case "P"
money = "-" + Left(money, Len(money) - 1) & "7"
Case "Q"
money = "-" + Left(money, Len(money) - 1) & "8"
Case "R"
money = "-" + Left(money, Len(money) - 1) & "9"
End Select

format_currency2 = money * 0.01

End Function

The macro comments included my name (now removed).  Why don't I add my social security number, passwords, etc...   Genius, I tell ya.  GENIUS!
Impossible is nothing
« Last Edit: February 14, 2008, 03:22 PM by dementedmuppet »

#### jgpaiva

• Global Moderator
• Joined in 2006
• Posts: 4,727
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #11 on: February 14, 2008, 05:03 PM »
Sorry, but i didn't understand from your post: Did you get the problem solved?

#### dementedmuppet

• Supporting Member
• Joined in 2006
• Posts: 25
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help
« Reply #12 on: February 14, 2008, 09:25 PM »
Nice of you to ask

I got the reports formatted and sent.

My code needs some work so future reports aren't so much hassle.

Tinjaw's code really helped in getting me on the right track.  Just a few quirks to sort out to fully automate it.

I also have to take out the wacky bits like scrolling & resizing the window.  My boss & coworkers get all impressed by the errors because it makes it LOOK like the code is doing something.  But eventually I have to turn it over to I.T. for them to add to their processing schedule. They won't be impressed by wacko irrelevant lines.

Concise answer:  problem solved.  Thanks!!
Impossible is nothing

#### jgpaiva

• Global Moderator
• Joined in 2006
• Posts: 4,727
##### Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #13 on: February 15, 2008, 03:07 AM »

Ok then, great!

#### abhaytanu

• Participant
• Joined in 2008
• Posts: 2
##### Re: DONE: How do I convert (wtf is this? Cobol?) to numbers? Examples included
« Reply #14 on: December 03, 2008, 04:45 AM »
Hi
may i know which jar is require for com.bankframe.ei.txnhandler.dataformat.DataFormatUtils. It is urgent. In my application i want to use DataFormatUtils. but i'm not getting that which jar must we need for the same.Waiting for reply