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

[Dave_in_NH]

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.

 

[Paul Lautman]

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]

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]

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.

 

[Pete_UK]

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]

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

 

[Pete_UK]

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

 

[Patricia Shannon]

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.

 

[dbahooker]

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

 

[Dave_in_NH]

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