Suprtool

Exporting to Excel with Suprtool

Latest News: Importing Excel Data!

Exporting IMAGE/SQL Data to other Applications:

STExport

In our Suprtool training modules, you learn how to use Suprtool and Suprlink’s powerful selection, extracting and linking features to create an output file containing the data you need.

Importing to a PC application such as Excel is similar in concept to almost any job where Suprtool is used to feed IMAGE data to a program on the 3000. The only extra step would be using STExport to reformat the output before transferring the file to the PC.

HP e3000 Data does need to be converted

STExport is the module of Suprtool that reformats HP e3000 data into formats that can be loaded directly into applications on PCs and other platforms.

STExport converts the data

STExport reads self-describing (“link”) files, and reformats the data into an ASCII output file:

For example ......

>base store.demo
>get m-customer
>out custsd,link
>xeq
>export
$in custsd
$out custexp
$xeq
In=20. Out=20. CPU-Sec=1. Wall-Sec=2.
$print custexp

$print custexp
"Edmonton",240000,10005,"30","Terry","Coyle","AL","17503 170th Street","","T5E2K1"
"Vancouver",200000,10010,"20","Wayne","Humphreys","BC","#403-1075 Comox","","V5T 1H6"
"Coquitlam",200000,10014,"20","Elizabeth","Welton","BC","2788 Oxtoby Place","","V6B3K9"
"Richmond",200000,10011,"20","William","Kirk","BC","8860 No 1 Rd","","V7X1B1"
"Calgary",200000,10017,"20","Jack","Morrison","AL","420 Macleod Trail S.E.","","T2G2E2"
...

The Suprtool steps above converted the M-CUSTOMER dataset into the custexp file, which is an ASCII file with variable-length records. You now download custexp to your PC using either Reflection or FTP.

Note that STExport has:

This format is known as PRN (printer) format, or also as CSV (Comma-Separated Values).

Once the custexpr file has been transferred to a PC, it can be loaded directly into Microsoft Excel (File|Open|custexp.csv). File|Open will delect that it is probably a CVS file and will put you into the Text Import Wizard:

In Step 2 of the Wizard, select "comma" as the delimter:

Click "Finish" on Step 3 of the Import Wizard and here is what the spreadsheet looks like:

Date Fields?

What if you have a date field? The D-SALES dataset of our sample database contains two such fields: DELIV-DATE and PURCH-DATE. They contain a 32-bit binary value in the format YYYYMMDD.

The answer is to use the ITEM command in Suprtool to identify them as dates and the DATE command in STExport to include a "/" separator in the cvs file.

>get d-sales
>form d-sales


Database: STORE.DEMO.GREEN

    D-SALES          Detail                  Set# 5
       Entry:                     Offset
          CUST-ACCOUNT         Z8      1  (!M-CUSTOMER)
          DELIV-DATE           J2      9
          PRODUCT-NO           Z8     13  (M-PRODUCT)
          PRODUCT-PRICE        J2     21
          PURCH-DATE           J2     25                    <>
          SALES-QTY            J1     29
          SALES-TAX            J2     31
          SALES-TOTAL          J2     35
    Capacity: 602 (14)  Entries: 8  Highwater: 8  Bytes: 38

>item deliv-date,data,yyyymmdd
>item purch-date,date,yyyymmdd
>out salessd,link
>xeq
>export
$in salessd
$date yyyymmdd "/"
$out salesexp
$xeq
$print salesexp
10020,1997/10/04,50511501,9831,**********,2,2753,22415
10003,1997/10/16,50511501,9831,1997/10/16,1,1376,11207
10003,1997/10/16,50512501,14562,1997/10/16,1,2039,16600
10003,1997/10/16,50513001,19220,1997/10/16,1,2691,21910
10016,1997/10/20,50521001,2459,1997/10/20,3,1033,8411
10016,1997/10/20,50532001,13985,1997/10/20,1,1958,15942
10020,1997/10/28,50512501,14660,1997/10/28,1,2052,16713
10010,1997/10/20,50533001,6992,1997/10/20,1,979,7970

Again go to Excel and import salesexp.cvs using File|Open. As you go through the Import Wizard, specify Comma as the delimiter. On the data formatting screen (Step 3), you can take the defaults again. That is because our date fields formatted as 1997/10/04 will be recognized as dates by Excel (under the "General" data type treatment).

However, dates with the century included will not display in the standard column width of Excel (all these examples were done in Excel 2000). Instead they are displayed as cross hatches (######).

No Problem! All you need to do is select the column by clicking on the heading ("B"), then right click, select Column Width and increase the width to 10:

..... Here is your final spreadsheet. The selected cell contains asterisks because Suprtool inserted them when it found that the date value was invalid (i.e., 19971000 is not a valid date).

Dollar Amount Fields

What about dollar amount fields or other fields with an implied decimal place? Our D-SALES dataset contains three such fields, PRODUCT-PRICE, SALES-TAX and SALES-TOTAL. The answer is to use the ITEM command in Suprtool to define them as having two decimal places.

>get d-sales
>item product-price,decimal,2
>item sales-tax,decimal,2
>item sales-total,decimal,2
>out salessd,link
>xeq
>export
$in salessd
$out salesexp
$xeq
$print salesexp
10020,1997/10/04,50511501,98.31,**********,2,27.53,224.15
10003,1997/10/16,50511501,98.31,1997/10/16,1,13.76,112.07
10003,1997/10/16,50512501,145.62,1997/10/16,1,20.39,166.00
10003,1997/10/16,50513001,192.20,1997/10/16,1,26.91,219.10
10016,1997/10/20,50521001,24.59,1997/10/20,3,10.33,84.11
10016,1997/10/20,50532001,139.85,1997/10/20,1,19.58,159.42
10020,1997/10/28,50512501,146.60,1997/10/28,1,20.52,167.13
10010,1997/10/20,50533001,69.92,1997/10/20,1,9.79,79.70
$

When you import this file into Excel, take the same options as before and your spreadsheet will look this:


You now know the basics of exporting to Excel. To learn more about exporting, including MS Access and Oracle, read our tutorial, Sharing HP e3000 Data to the World.


Importing Data From Excel

We have shown how to get data out of various databases and into other applications, such as SQL Server, mySQL and Excel. We also get requests on how best to put data back into Image or Eloquence databases from various applications, typically Excel.

Often when the request comes to us, the users have already exported the data out of Excel and are trying to get the data "into Suprtool" and subsequently back into their database. For the export from Excel, we suggest you save the data as "formatted text - space delimited".

The biggest problem facing users is often that their tool on the 3000 expects the sign in a specific spot, or leading zeroes. The new $Number function in Suprtool 4.7 allows you to easily "import" numbers into Suprtool, regardless of their format. With $Number, you can directly import numbers with signs, decimal places and even with or without leading zeroes. If your version of Suprtool does not have the $number function, then read the section entitled, "Importing Excel Data With Old Suprtool".

Suprtool can accept these free-form "numbers" as display data types. This means numbers in the form:

 1234.45-
 -12345
 -123.2134
 12343
 $123.45
can be accepted and converted to any other numeric data type. Consider the following data:
Item-number New-Price
12345       +123.45
34563       + 27.5
21312       + 1.545
Suprtool can read and convert the data in New-Price using the number function. Let's say we want New-Price to be a double integer, and it currently occupies eight bytes starting in position six. Here is the task you would use to convert the New-Price free-format number into a double integer.
 >in mynums
 >def item-number,1,5,byte
 >def new-price-ascii,6,8,display
 >def new-price,1,4,double
 >item new-price-ascii,dec,2
 >item new-price,dec,2
 >ext item-number
 >ext new-price=$number(new-price-ascii)
 >out somefile,link
 >xeq
The $Number function takes the free-format number and makes it a valid display number. It will determine the decimal, sign and add leading zeroes. It will round the number to the defined number of decimal places.

In the case of the 1.545 number, Suprtool will round the value to be 1.55, since the given number of decimal places is two and the following value is five or greater. If you have a whole number such as 54, with no decimal point the value becomes 54.00.

The Suprtool $Number function will not accept data that has: