topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Friday December 13, 2024, 8:51 pm
  • Proudly celebrating 15+ 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.
  • donate

« previous next »
Pages: [1] • bottom

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

dementedmuppet

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 26
    • View Profile
    • Donate to Member
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
[Select]
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
    • View Profile
    • Donate to Member
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: 26
    • View Profile
    • Donate to Member
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 :P 

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: 26
    • View Profile
    • Donate to Member
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?
http://download.orac...DataFormatUtils.html
Impossible is nothing
« Last Edit: February 12, 2008, 10:27 AM by dementedmuppet »

jgpaiva

  • Global Moderator
  • Joined in 2006
  • *****
  • Posts: 4,727
    • View Profile
    • Donate to Member
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: 26
    • View Profile
    • Donate to Member
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
    • View Profile
    • Donate to Member
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: 26
    • View Profile
    • Donate to Member
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
    • View Profile
    • Donate to Member
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...

[Select]
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: 26
    • View Profile
    • Donate to Member
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: 26
    • View Profile
    • Donate to Member
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  :Thmbsup:

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
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;W:\Output\30923\Encounter_Data\Archive\PDE Convert to Excel\target.txt", _
        Destination:=Range("A7"))
        .Name = "target"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = 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
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .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
    • View Profile
    • Donate to Member
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: 26
    • View Profile
    • Donate to Member
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
    • View Profile
    • Donate to Member
Re: How do I convert (wtf is this? Cobol?) to numbers? Examples included. Help!
« Reply #13 on: February 15, 2008, 03:07 AM »
 ;D ;D ;D

Ok then, great! :)

abhaytanu

  • Participant
  • Joined in 2008
  • *
  • default avatar
  • Posts: 2
    • View Profile
    • Donate to Member
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
Pages: [1] • top
« previous next »