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;newOn HP-UX you can use Qedit:
/t dinvcsv /set keep var off /k dinvcsvfOr 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.
