Reading data file into Excel

[Gandalf]

I need to read a data file and use it in Excel. A problem is, some of the field definitions are of the form S9(13)V9(2). Is there a way to get such values into Excel?

If it affects the answer, the file has more records than an Excel sheet has rows, so I assume I'm going to have to read the file via a macro. I see how to read a file and treat each record as a text string. I don't know how to convert the text string to a number.

[Ben Kenobi]

Use the VBA VAL() function. Or is it more complex than that?

[Spectrum]

Read the file into Access create smaller data sets that can go into excel. Of course, if you're doing easy calcs in excel like summation, just do them in access and skip the excel part.

[Gandalf]

Use the VBA VAL() function. Or is it more complex than that?

Oops. I left out what I think is the hard part of the problem. The S9(13)V9(2) is stored as compressed format, so that what seems like 16 characters is stored in 8 positions. That's what I need to untangle.

[Arlie_Proctor]

Gandalf:

The algorithm for unpacking the numeric field will differ depending on the mainframe from which the original file came. The easiest thing to do is have a COBOL programmer do that part for you. If not, I think there was a discussion of this on the CAS WC about a year back. I think Snafu offerred several variants of the unpacking code there.

[snafu]

For some very handy information as well as commercial COBOL data conversion services try http://www.vedit.com/businesscom/ebcdic.htm

They also have a conversion utility with a trial version. The utility is pretty expensive but the trial version is free and will convert the first 5000 records. That should be enough to help figure things out.

Hope that helps a little.

[Gandalf]

Thanks, Arlie and snafu.

[Ben Kenobi]

At least on our systems, packed data is stored as two decimal digits per byte, written in hex, with a C terminating. In other words, 123456 would be stored as 12 34 56 7C, or 18 52 86 124 in decimal. To unpack, read it byte by byte (open the file for binary access).

[Dr T Non-Fan]

Have someone in the Systems area convert the numbers, or extract the numbers in a different format.