STExport For Transforming Data

STExport is one of the many components of the Suprtool product.

Using STExport with Suprtool, you can export data from sources on your HP 3000 or HP 9000 (including MAGE, Eloquence, Oracle or Allbase databases, KSAM files, and disk files) to just about any other platform.

All without having to write a custom program.

First you use Suprtool to select the data and write it to a self-describing file, then you format the data from the self-describing file into almost any format you want, from fixed-length fields to HTML or XML. You can control field delimiters, quotation marks, numeric and date formats among other things.

STExport: To Excel Spreadsheets

Your HP 3000 and HP 9000 data sources contain a wealth of information that would be useful in an Excel spreadsheet. But that data does need to be converted.

TurboIMAGE data, for example, has fixed-width fields, binary storage formats (J2, K2, P28, etc), and a structure defined in the Root File.

PC Applications require variable-length fields, Ascii values for numerics, and field delimiters such as comma or tab. To pass field names, the most common method is to use column headings.

The purpose of STExport is to transform data into formats that can be loaded directly into applications on PCs and other platforms.

For complete details on how STExport makes Excel importing easy, read Robelle's Exporting to Excel technical report.

For another application of this technique, read the the Alliant Foodservice case study.

STExport: Allbase To Oracle

The example below shows how to extract data from an Allbase database on your HP 3000 and load it into an Oracle database on your HP 9000.

    :run Suprtool.pub.robelle
    >	        {extract the data into a self-describing file}
    >open   allbase employee.db humres
    >select * from humres.employees
    >sort   department_no
    >output emp,link
    >xeq

    >export   	{invoke STExport}
    $input emp
    $output empfmt
    $xeq

In this example, we are using the following defaults: no heading record, text fields in quotation marks with trailing blanks suppressed, comma separators, and variable length columns. Because the Oracle database resides on the HP 9000, you have to transfer the formatted file with FTP. Once the file is on the destination machine, you can use Oracle’s SQL*Loader to insert the data in the appropriate table.

The command to run SQL*Loader is:

    sqlload userid=username/userpass control=loaddata.ctl log=load.log

where sqlload is the filename of the SQL*Loader program; userid is the username and password to connect to the database; control points to the file that contains the load specifications (shown below); and log requests SQL*Loader to write the various messages generated during the load operation, including error messages. The control file should contain the following:

    load data
    infile '/users/mike/oracle/data/empfmt'
     append into table employees
     fields terminated by ","
     optionally enclosed by '"'
    (employee_name, employee_number, status, address, city, province_state,
    country, postal_zip_code, home_phone_number, sex, social_insurance_number,
    hire_date, marital_status, spouse_name, monthly_salary, bank_name,
    bank_account_id, birth_date, title, department_name, work_phone_number,
    last_review_date, vacation_days)

This list describes the elements in the file:

    load data -  the load command
    infile - the name of the data file
    append into table - the destination table
    insert - assumes the table is empty
    append - creates new rows
    replace - deletes existing rows before loading
    truncate - the same as replace
    fields terminated by - the character used between column values
    optionally enclosed by - the character used around text strings
    (employee_name etc.) - the column names, in the same sequence as the data file

For another example, using STExport to integrate TurboIMAGE databases on MPE with Oracle databases on HP-UX, read the Hammacher Schlemmer case study.

If Oracle is your primary export destination, read this paper on Oracle datatypes.

STExport: Summary

It’s as simple as that. The applications of STExport are unlimited. Read the STExport section in the Suprtool manual (available online) and find new ways to exploit its power. If you have a minute, let us know what you did. We are always on the lookout for new, innovative ideas.

---

For more articles on transforming data, visit Robelle's Migration Web Center. There you will find in-depth technical articles on Oracle, Excel, Eloquence, and many other related topics.

Also of interest is Bob Green's paper for HPWorld 2002: Transforming TurboIMAGE Data for Eloquence, Oracle and More.

Or another article on exporting to XML, with an introduction to XML for newcomers.