Comptroller pulls agency's numbers together

About 300 users already had Microsoft Office, so Harmon needed something that could export to Microsoft apps.

' Barry Harmon

Data conversion tool extracts data from diverse databases and makes it easier to generate reports and charts

Until recently, Barry Harmon could not find a given payroll report without sifting through spreadsheet after spreadsheet.

Every two weeks Harmon, a computer specialist in the Office of the Comptroller of the Currency, aggregates the personnel actions from eight budget centers that keep records about the agency's employees across the country.

Information from the budget centers goes through the Treasury Departments payroll information system, based on enterprise resource planning software from PeopleSoft Inc. of Pleasanton, Calif. Harmon receives the reports in plain text on a mainframe system.

In the past, Harmon said, the Treasury data would be printed by an impact printer on forms preloaded with employee names, salaries, and changes in leave time or status. But in 1999, Harmon learned that personnel information would be processed differently.

Because of year 2000 changes, the impact printer and preloaded forms were being eliminated, which left him with only strung-together text files that would have to be parsed and entered into Microsoft Access database fields.

'We had to find some way to extract the data and some way to print it' in understandable fashion, Harmon said. 'I didn't want to write a program.'

So he went technology hunting. He persuaded his supervisor that if the relevant data could be automatically extracted, the comptroller's office could export it to other applications to generate reports and charts more easily.

Export requirements

About 300 users in the comptroller's office already had Microsoft Office, so Harmon needed something that could export to Microsoft applications.

In May 2000, he installed the Monarch Professional data conversion tool from Datawatch Corp. of Lowell, Mass. The software competes with database report-writing software such as Crystal Reports from Crystal Decisions Inc. of Palo Alto, Calif.

Harmon used Monarch to build a template that would automatically extract data from the 40-line mainframe pages and populate the correct fields in Access.

Employees can now retrieve the files at their desktop computers. To make changes, Harmon can edit the template in Microsoft Word.

Information moves from the mainframe via File Transfer Protocol to a Monarch Data Pump server, which also archives it. Harmon can pull up personnel records from the Monarch archives with a simple record query. Any other personnel reports that arrive on paper can be converted to online format, too.

The Monarch software can parse data for other formats including Microsoft Excel, Extensible Markup Language, dBase, Paradox, IBM Lotus 123, text, comma-delimited text and HTML.

Reader Comments

Please post your comments here. Comments are moderated, so they may not appear immediately after submitting. We will not post comments that we consider abusive or off-topic.

Please type the letters/numbers you see above