How can I import "Packed" mainframe data into Excel?

I've copied a file from an IBM Mainframe computer to my PC and need to do
some analysis in Excel. Some of the fields in the file are "packed" - for
example, the format of one field is " P(9.2). On the mainframe, that takes
up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the
sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC
8-bit) into two, 4-bit parts and stores one decimal value in each part.

Suggestions about how I can get this format into Excel without having to
re-format the data in a mainframe program first? HEX2DEC is not an answer
because "packed" fields are not true HEX values.

Dave_in_NH wrote:
> I've copied a file from an IBM Mainframe computer to my PC and need
> to do some analysis in Excel. Some of the fields in the file are
> "packed" - for example, the format of one field is " P(9.2). On the
> mainframe, that takes up six bytes, so $5.90 is stored as
> "00000000059F" (the last bit is the sign). This is NOT "hex" but an
> old convention that breaks each byte (EBCDIC 8-bit) into two, 4-bit
> parts and stores one decimal value in each part.
>
> Suggestions about how I can get this format into Excel without having
> to re-format the data in a mainframe program first? HEX2DEC is not
> an answer because "packed" fields are not true HEX values.

I always used MVS/CMS PIPELINEs to do this conversion, usually at the point
of extraction.

Remind me, if $5.90 is stored as "00000000059F", how would $5.95 be stored?

Also what is the format of the file now that it is on the PC?

Paul - I was mistaken, $5.90 is stored as "00000000590F", $5.95 is
"00000000590F", and ($5.95) is "00000000590D".

I allowed the file transfer process to convert EBCDIC to ASCII (but I can
re-run that and change the conversion properties at any time).

What is "MVS/CMS PIPELINEs"? I've Googled it and only come up with a
product from a company named XEPHON. I'd probably just write a mainframe
program to do what I need before I would buy another mainframe product - I
was hoping to learn that I had overlooked some built-in function in Excel
that could do this for me.

Thanks for your response.
Dave


"Paul Lautman" wrote:

> Dave_in_NH wrote:
> > I've copied a file from an IBM Mainframe computer to my PC and need
> > to do some analysis in Excel. Some of the fields in the file are
> > "packed" - for example, the format of one field is " P(9.2). On the
> > mainframe, that takes up six bytes, so $5.90 is stored as
> > "00000000059F" (the last bit is the sign). This is NOT "hex" but an
> > old convention that breaks each byte (EBCDIC 8-bit) into two, 4-bit
> > parts and stores one decimal value in each part.
> >
> > Suggestions about how I can get this format into Excel without having
> > to re-format the data in a mainframe program first? HEX2DEC is not
> > an answer because "packed" fields are not true HEX values.
>
> I always used MVS/CMS PIPELINEs to do this conversion, usually at the point
> of extraction.
>
> Remind me, if $5.90 is stored as "00000000059F", how would $5.95 be stored?
>
> Also what is the format of the file now that it is on the PC?
>
>
>

Dave_in_NH wrote:
> Paul - I was mistaken, $5.90 is stored as "00000000590F", $5.95 is
> "00000000590F", and ($5.95) is "00000000590D".
That's more like what I was expecting!


> I allowed the file transfer process to convert EBCDIC to ASCII (but I
> can re-run that and change the conversion properties at any time).
>
> What is "MVS/CMS PIPELINEs"?
Take a look at http://vm.marist.edu/~pipeline/
PIPELINES is part of VM, so if you're working on VM/CMS then you've already
got it.
If you're working on MVS/TSO then you MAY already have it as BatchPipes or
even installed as the full PIPELINEs module. PIPELINEs is VERY powerful!

> I was hoping to learn that I had overlooked some
> built-in function in Excel that could do this for me.
All may not be lost though even as it is. You didn't answer my final, but
very important, question :
"Also what is the format of the file now that it is on the PC?"

If you can show some sample records (or parts of records) from the file,
then we'll see what we can do. I have spent many years using things like 123
and Excel to analyse data from IBM mainframes.

If you have imported to cell A1 the text value "00000000590D" (or with
F at the end to indicate positive), the following formula will convert
this in Excel:

IF(RIGHT(A1,1)="D",-VALUE(LEFT(A1,LEN(A1)-1))/10,VALUE(LEFT(A1,LEN(A1)-1))/10)

This can be copied down the column to convert any other values you may
have in column A.

Hope this helps.

Pete

Dave_in_NH wrote:
> Paul - I was mistaken, $5.90 is stored as "00000000590F", $5.95 is
> "00000000590F", and ($5.95) is "00000000590D".
>
> I allowed the file transfer process to convert EBCDIC to ASCII (but I can
> re-run that and change the conversion properties at any time).
>
> What is "MVS/CMS PIPELINEs"? I've Googled it and only come up with a
> product from a company named XEPHON. I'd probably just write a mainframe
> program to do what I need before I would buy another mainframe product - I
> was hoping to learn that I had overlooked some built-in function in Excel
> that could do this for me.
>
> Thanks for your response.
> Dave
>
>
> "Paul Lautman" wrote:
>
> > Dave_in_NH wrote:
> > > I've copied a file from an IBM Mainframe computer to my PC and need
> > > to do some analysis in Excel. Some of the fields in the file are
> > > "packed" - for example, the format of one field is " P(9.2). On the
> > > mainframe, that takes up six bytes, so $5.90 is stored as
> > > "00000000059F" (the last bit is the sign). This is NOT "hex" but an
> > > old convention that breaks each byte (EBCDIC 8-bit) into two, 4-bit
> > > parts and stores one decimal value in each part.
> > >
> > > Suggestions about how I can get this format into Excel without having
> > > to re-format the data in a mainframe program first? HEX2DEC is not
> > > an answer because "packed" fields are not true HEX values.
> >
> > I always used MVS/CMS PIPELINEs to do this conversion, usually at the point
> > of extraction.
> >
> > Remind me, if $5.90 is stored as "00000000059F", how would $5.95 be stored?
> >
> > Also what is the format of the file now that it is on the PC?
> >
> >
> >

Thanks Paul and Pete for your help on this. I broke down and wrote a program
on the mainframe to unpack the number fields and I transferred them. I still
had a problem with negative numbers, though. I found that the last byte is
'}' when it is a negative number ending in zero, and 'J' through 'R' when -1
through -9.

To get around this, I came up with this formula;
=(VALUE(LEFT(A1,LEN(A1)-1))*(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,10,-10)))+(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,VALUE(RIGHT(A1,1)),IF(RIGHT(A1,1)="}",0,(CODE(RIGHT(A1,1))-73)*-1)))

I doesn't work when the field is only one byte, but I can deal with that.

Thanks again for your help.

Dave




"Dave_in_NH" wrote:

> I've copied a file from an IBM Mainframe computer to my PC and need to do
> some analysis in Excel. Some of the fields in the file are "packed" - for
> example, the format of one field is " P(9.2). On the mainframe, that takes
> up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the
> sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC
> 8-bit) into two, 4-bit parts and stores one decimal value in each part.
>
> Suggestions about how I can get this format into Excel without having to
> re-format the data in a mainframe program first? HEX2DEC is not an answer
> because "packed" fields are not true HEX values.

Thanks for feeding back, Dave.

Pete

Dave_in_NH wrote:
> Thanks Paul and Pete for your help on this. I broke down and wrote a program
> on the mainframe to unpack the number fields and I transferred them. I still
> had a problem with negative numbers, though. I found that the last byte is
> '}' when it is a negative number ending in zero, and 'J' through 'R' when -1
> through -9.
>
> To get around this, I came up with this formula;
> =(VALUE(LEFT(A1,LEN(A1)-1))*(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,10,-10)))+(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,VALUE(RIGHT(A1,1)),IF(RIGHT(A1,1)="}",0,(CODE(RIGHT(A1,1))-73)*-1)))
>
> I doesn't work when the field is only one byte, but I can deal with that.
>
> Thanks again for your help.
>
> Dave
>
>
>
>
> "Dave_in_NH" wrote:
>
> > I've copied a file from an IBM Mainframe computer to my PC and need to do
> > some analysis in Excel. Some of the fields in the file are "packed" - for
> > example, the format of one field is " P(9.2). On the mainframe, that takes
> > up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the
> > sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC
> > 8-bit) into two, 4-bit parts and stores one decimal value in each part.
> >
> > Suggestions about how I can get this format into Excel without having to
> > re-format the data in a mainframe program first? HEX2DEC is not an answer
> > because "packed" fields are not true HEX values.

What language did you write the progame in? In COBOL, the compiler will do
the conversion, by moving a field to an unpacked field, with the sign
specified as desired.

"Dave_in_NH" wrote:

> Thanks Paul and Pete for your help on this. I broke down and wrote a program
> on the mainframe to unpack the number fields and I transferred them. I still
> had a problem with negative numbers, though. I found that the last byte is
> '}' when it is a negative number ending in zero, and 'J' through 'R' when -1
> through -9.
>
> To get around this, I came up with this formula;
> =(VALUE(LEFT(A1,LEN(A1)-1))*(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,10,-10)))+(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,VALUE(RIGHT(A1,1)),IF(RIGHT(A1,1)="}",0,(CODE(RIGHT(A1,1))-73)*-1)))
>
> I doesn't work when the field is only one byte, but I can deal with that.
>
> Thanks again for your help.
>
> Dave
>
>
>
>
> "Dave_in_NH" wrote:
>
> > I've copied a file from an IBM Mainframe computer to my PC and need to do
> > some analysis in Excel. Some of the fields in the file are "packed" - for
> > example, the format of one field is " P(9.2). On the mainframe, that takes
> > up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the
> > sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC
> > 8-bit) into two, 4-bit parts and stores one decimal value in each part.
> >
> > Suggestions about how I can get this format into Excel without having to
> > re-format the data in a mainframe program first? HEX2DEC is not an answer
> > because "packed" fields are not true HEX values.

For some (not very good) reasons, I wrote it in Natural. I didn't see a way
in Natural to specify that the sign be in a separate byte so I was stuck with
the last bit being the sign, hence my unusual Excel formula.

Thanks.

Dave


"Patricia Shannon" wrote:

> What language did you write the progame in? In COBOL, the compiler will do
> the conversion, by moving a field to an unpacked field, with the sign
> specified as desired.
>
> "Dave_in_NH" wrote:
>
> > Thanks Paul and Pete for your help on this. I broke down and wrote a program
> > on the mainframe to unpack the number fields and I transferred them. I still
> > had a problem with negative numbers, though. I found that the last byte is
> > '}' when it is a negative number ending in zero, and 'J' through 'R' when -1
> > through -9.
> >
> > To get around this, I came up with this formula;
> > =(VALUE(LEFT(A1,LEN(A1)-1))*(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,10,-10)))+(IF(ISNUMBER(VALUE(RIGHT(A1,1)))=TRUE,VALUE(RIGHT(A1,1)),IF(RIGHT(A1,1)="}",0,(CODE(RIGHT(A1,1))-73)*-1)))
> >
> > I doesn't work when the field is only one byte, but I can deal with that.
> >
> > Thanks again for your help.
> >
> > Dave
> >
> >
> >
> >
> > "Dave_in_NH" wrote:
> >
> > > I've copied a file from an IBM Mainframe computer to my PC and need to do
> > > some analysis in Excel. Some of the fields in the file are "packed" - for
> > > example, the format of one field is " P(9.2). On the mainframe, that takes
> > > up six bytes, so $5.90 is stored as "00000000059F" (the last bit is the
> > > sign). This is NOT "hex" but an old convention that breaks each byte (EBCDIC
> > > 8-bit) into two, 4-bit parts and stores one decimal value in each part.
> > >
> > > Suggestions about how I can get this format into Excel without having to
> > > re-format the data in a mainframe program first? HEX2DEC is not an answer
> > > because "packed" fields are not true HEX values.