File with packed-data

  • Thread starter Thread starter gary
  • Start date Start date
G

gary

How do I convert a file containing packed-data to a file
in ASCII-text format?

For example:

From:

....c.............00000000.. ........
00087202320000000FFFFFFFF20408000103
00232C087C20C000C000000007202C00072C

To:

0000283722 0028372 02000 0000000 270240028 000000017023
 
I don't think all your fields are packed.

FFFFFFFF
00000000

Doesn't looked packed to me.

And I think that a lot of FTP programs will "fix" some of the funny packed
decimals when you transfer them to the pc (from a mainframe???). (I think they
try to translate some rudimentary graphic characters to pc graphic characters.
If that's the case, then you've still got trouble.)

And if you separate each of those fields into separate cells, maybe you could
use a userdefined function.

Option Explicit
Function Pack2ASCII(myPack As String) As Long

Dim i As Long
Dim myDecimal As String
Dim myTemp As Long
Dim mySign As String

myDecimal = ""
For i = 1 To Len(myPack) Step 1
myTemp = Asc(Mid(myPack, i, 1)) And &HF0
myDecimal = myDecimal & Left(Hex(myTemp), 1)
If i < Len(myPack) Then
myTemp = Asc(Mid(myPack, i, 1)) And &HF
myDecimal = myDecimal & Left(Hex(myTemp), 1)
Else
myTemp = Asc(Mid(myPack, i, 1)) And &HF
mySign = Left(Hex(myTemp), 1)
End If
Next i

If mySign = "D" Then
myDecimal = "-" & myDecimal
Else
myDecimal = myDecimal
End If

Pack2ASCII = myDecimal

End Function

If your packed decimal is in A1, then
=pack2ascii(a1)

There's no validation in this routine. You'll want to add some.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top