Using Microsoft Excel To Download, Access And Cross Correlate Data Residing On Mainframe Systems
Authors: Gair Robert A. 
Published: December 2007

The Problem: Automatic Downloading of Data

PanCanadian is in the process of migrating from mainframe based systems and databases to personal computers and network servers. The process has been underway for about four years now, and it will probably be at least as long again until all the mainframe systems have been replaced. Though data is increasingly available within the client server environment, much of the key data is still resides on the old mainframe. PanCanadian's in-house Information Systems group is primarily focused on the new client server workplace, and responsibilities for the mainframe systems have been out-sourced. Though the now out-dated mainframe reports continue to be available, these hard copy paper stacks are the only way that some of this data can be directly accessed.

The Solution: Spreadsheet Parsing of Mainframe Print Buffers

When a mainframe report is run a buffer file is created before the report is actually printed. This buffer file can be manually captured and file transferred from the mainframe environment to the PC workplace. In those instances where the report parameters are set sufficiently broad, the printed report represents an echo of the mainframe database — albeit in text form. The captured file usually contains the line printer feed codes as well as job and page header information. To access this information within a spreadsheet or database application the text must be cleaned up and parsed into columns. Though Excel contains parsing or 'text to columns' commands, these are not sufficiently robust to handle most of the mainframe report formats encountered. To facilitate conversion of the text reports into spreadsheet format a general purpose parsing spreadsheet was developed.

The Opportunity: Direct Business Access to Data on a PC

Once mainframe data is in spreadsheet or database format it can be readily accessed and manipulated. Data can be cross correlated to yield additional information and identify patterns and dependencies not evident in the raw data alone. The business can use this information to measure performance and establish targets for improvement.

Example

To evaluate the results of 1994 deep gas drilling within a district, data had to be brought together from a number of mainframe systems (Financial, Production and Reserves) as well as a server based completions database. A financial report was first used as the basis for identifying wells drilled as part of each project within the district. The report was converted from a purely text file into a spreadsheet file which honored the report layout, through use of the parsing spreadsheet. The spreadsheet file was then converted from a report layout into a database layout by means of some simple Excel look-up and index functions. A list of wells drilled was then extracted by project, and Authorization For Expenditure (AFE) descriptions were converted into well locations. Look-up and index functions were then used to link production data back to this well list. Subsequent data correlation (not illustrated) was then used to link completions costs and reserves data. Charts are shown for drilling outcome both by program type as well as by area. Completion success has been charted for those horizons identified at casing point as well as for subsequent completion attempts.