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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave_in_NH - 13 Jun 2006 13:26 GMT

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.

Reply to this Message

Paul Lautman - 13 Jun 2006 13:40 GMT

> 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
[quoted text clipped - 7 lines]
> 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?

Reply to this Message

Dave_in_NH - 13 Jun 2006 14:30 GMT

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

> > 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
[quoted text clipped - 14 lines]
>
> Also what is the format of the file now that it is on the PC?

Reply to this Message

Paul Lautman - 13 Jun 2006 14:48 GMT

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

Reply to this Message

Pete_UK - 13 Jun 2006 14:59 GMT

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

> Paul - I was mistaken, $5.90 is stored as "00000000590F", $5.95 is
> "00000000590F", and ($5.95) is "00000000590D".
[quoted text clipped - 29 lines]
> >
> > Also what is the format of the file now that it is on the PC?

Reply to this Message

Dave_in_NH - 13 Jun 2006 17:01 GMT

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

> 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
[quoted text clipped - 6 lines]
> re-format the data in a mainframe program first? HEX2DEC is not an answer
> because "packed" fields are not true HEX values.

Reply to this Message

Pete_UK - 13 Jun 2006 17:10 GMT

Thanks for feeding back, Dave.

Pete

> 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
[quoted text clipped - 21 lines]
> > re-format the data in a mainframe program first? HEX2DEC is not an answer
> > because "packed" fields are not true HEX values.

Reply to this Message

Patricia Shannon - 14 Jun 2006 16:23 GMT

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.

Reply to this Message

dbahooker@hotmail.com - 14 Jun 2006 18:50 GMT

you don't want to do this.

use a tool with real ETL capabilities; like MS Access, DTS or even SQL
Server.

Excel isn't a database; it is a disease.

And all you Excel dorks; everywhere in the world-- should be homeless;
drunk on the side of the road.
Excel is for babies. it isn't regimented enough.

it isn't automatable.

excel macro viruses are unstoppable. the only solution is uninstalling
excel from every machine of every computer at every company.

-Aaron

> 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
[quoted text clipped - 25 lines]
> > > re-format the data in a mainframe program first? HEX2DEC is not an answer
> > > because "packed" fields are not true HEX values.

Reply to this Message

Dave_in_NH - 14 Jun 2006 19:08 GMT

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

Reply to this Message