Importing Data using Suprtool

Import Data Using Suprtool

One of the more recent questions that has come up lately is How Do I Import data with Suprtool. Let's say we have the following data layout, Image, Eloquence or SD file, it doesn't matter. The purpose of this small white paper would be to extract data to a ".csv" file, and then import that same data back. Closing the loop so to speak.

• BIN-NO ~ J1
• LAST-SHIP-DATE ~ J2
• ON-HAND-QTY ~ J2
• PRODUCT-NO ~ Z8
• SUPPLIER-NO ~ Z8
• UNIT-COST ~ P8
• ITEM-DESC1 ~ X20
• ITEM-DESC2 ~ X20
• ITEM-DESC3 ~ X20
• ITEM-DESC4 ~ X20

To begin our project we will extract the data from a sample data source and output to an SD file and then use STExport to create a CSV file.

>get d-inventory
>out dinv,link
>xeq
IN=13, OUT=13. CPU-Sec=1. Wall-Sec=1.

:run stexport.pub.robelle
$in dinv
$out dinvcsv
$xeq
In=13. Out=13. CPU-Sec=1. Wall-Sec=1.
$listf dinvcsv,2

ACCOUNT=  GREEN       GROUP=  NEIL
FILENAME  code  ------------LOGICAL RECORD-----------  ----SPACE----
                  SIZE  TYP        EOF      LIMIT R/B  SECTORS #X MX

DINVCSV           152B  VA          13         13   1       16  1  *

So at this point we know have a file, typically how a customer would want to import using Suprtool for adding to a database etc. Note that Suprtool does not handle variable-length files so the first step is to convert the Variable length file to fixed length.

Step One: Convert to Fixed Length

On MPE you can convert using Fcopy:

/file dinvcsvf;rec=-152,1,f,ascii
/fcopy from=dinvcsv; to=*dinvcsvf;new

On HP-UX you can use Qedit:

/t dinvcsv
/set keep var off
/k dinvcsvf

Or Awk: (Thanks to Barry Lake from Allegro for fixify.sh)

#!/bin/sh
#
# Script to turn a typical bytestream file (variable length
# records) into a file with fixed length records by padding
# records with spaces as needed. The resulting record length
# will be that of the longest record in the file.

export INFILE=$1
OUTFILE=$(mktemp)

# Step 1: Find the length of the longest record in the input file
#         file and store it in a variable for use in the next step.

export MAXLEN=$(awk 'BEGIN { len = 0; }
  { if (length > len)
       len = length; }
  END { print len; }' $INFILE )

echo Maximum record length in \"$INFILE\" is $MAXLEN.

# Step 2: Get the MAXLEN variable; use it to create a string
#         of that many blanks; then use that to pad each input
#         record as needed to make it the same length as the
#         longest record.

awk 'BEGIN { "echo $MAXLEN" | getline maxlen; spaces = "";
             for(i=0; i < maxlen * 1; i++)
                spaces = " "spaces; }
  { print substr($0 spaces, 1, maxlen) }' $INFILE >$OUTFILE

# Step 3: awk can't edit a file in place so its output was
#         written to a new file which we then pour back into the
#         original file. Note: we could just as easily have done
#         cp $OUTFILE $INFILE or mv $OUTFILE $INFILE, but in that
#         case we'd lose the original file's creator and
#         permission bits.

cat $OUTFILE > $INFILE
rm $OUTFILE

You can run the above script:

$./fixify.sh datafile

At this point Suprtool can now read what is essentially variable-length data in a fixed length file.

Now remember what a typical CSV file looks like! Byte fields are surronded by quotes and separated or delimited with commas:

159,19910827,1,50532001,5053,9449,"Test index","Test index","Test index","Test index"

Step Two: Split out the Fields

The first step is to separate out each field from the record based on the delimeter, which in this case is the comma. I use byte lengths for each number field based on the rules for output ,ascii table.

Duplicated below:

  I1 J1 06 bytes      
  I2 J2 11 bytes
  I3 J3 16 bytes      
  I4 J4 20 bytes
  K1    05 bytes      
  K2    10 bytes
  Zn    n+1 bytes     
  Pn    n bytes

in dinvcsvf
{define targets}
def bin-x,1,6
def last-x,1,11
def qty-x,1,11
def prod-x,1,9
def supp-x,1,9
def cost-x,1,8
def desc1-x,1,22 {Note room for quotes}
def desc2-x,1,22
def desc3-x,1,22
def desc4-x,1,22

{define source}
def record,1,152

{extract using split }
 
ext bin-x=$split(record,first,",")
ext last-x=$split(record,",",1,",")
ext qty-x=$split(record,",",2,",")
ext prod-x=$split(record,",",3,",")
ext supp-x=$split(record,",",4,",")
ext cost-x=$split(record,",",5,",")
ext desc1-x=$split(record,",",6,",")
ext desc2-x=$split(record,",",7,",")
ext desc3-x=$split(record,",",8,",")
ext desc4-x=$trim($split(record,",",9,last))

{where?}
out myfile,link
xeq

Step Three: Closing the Loop

Now we just need to "close the loop" and extract the individual byte type fields into their appropriate data types. In this step we also "clean" the data of the double quotes.

Keep in mind that the data format that we want:

• BIN-NO ~ J1
• LAST-SHIP-DATE ~ J2
• ON-HAND-QTY ~ J2
• PRODUCT-NO ~ Z8
• SUPPLIER-NO ~ Z8
• UNIT-COST ~ P8
• ITEM-DESC1 ~ X20
• ITEM-DESC2 ~ X20
• ITEM-DESC3 ~ X20
• ITEM-DESC4 ~ X20

The defines for the above are in the section starting with the comment {Actual targets}

in myfile

{re-define number in display format}
def bin-z,bin-x,display
def last-z,last-x,display
def qty-z,qty-x,display
def prod-z,prod-x,display
def supp-z,supp-x,display
def cost-z,cost-x,display

{Actual targets}

{number}
def bin ,1,2,integer
def last,1,4,double
def qty ,1,4,double
def prod,1,8,display
def supp,1,8,display
def cost,1,4,packed

{bytes}
def desc1,1,20 
def desc2,1,20
def desc3,1,20
def desc4,1,20 

ext bin=$number(bin-z)
ext last=$number(last-z)
ext qty=$number (qty-z)
ext prod=$number(prod-z)
ext supp=$number(supp-z)
ext cost=$number(cost-z)

clean '"'
ext desc1=$trim($clean(desc1-x))
ext desc2=$trim($clean(desc2-x))
ext desc3=$trim($clean(desc3-x))
ext desc4=$trim($clean(desc4-x))

out loop,link
xeq

The SD file (loop) is now in the same format and layout of the original Dataset extraction prior to the STExport task. We've now closed the loop.

>form loop
    File: LOOP.NEIL.GREEN     (SD Version B.00.00)
       Entry:                     Offset
          BIN                  I1      1
          LAST                 I2      3
          QTY                  I2      7
          PROD                 Z8     11
          SUPP                 Z8     19
          COST                 P8     27
          DESC1                X20    31
          DESC2                X20    51
          DESC3                X20    71
          DESC4                X20    91
    Limit: 13  EOF: 13  Entry Length: 110  Blocking: 37

So to summarize the first step is to split out the data into separate fields with byte data types. Then to convert by re-defining with a new name to reference the data as display and use $clean and $number to extract into the final targets.