Exporting to Excel with Suprtool

Latest News: Importing Excel Data!

Exporting IMAGE/SQL Data to other Applications:

• Extract the IMAGE data using Suprtool and Suprlink
• Convert the files using STExport
• Transfer the file to the PC
• Import the delimited file into Excel

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

• TurboIMAGE data has:
  • Fixed-width fields
  • Binary storage formats (J2, K2, P28, etc)
  • Structure defined in Root File.
• PC Applications require:
  • variable-length fields
  • Ascii values for numerics
  • field delimiters

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 files
• Outputs ascii files
• Allows you to specify:
  • field delimiters to use
  • date format
  • fieldnames in first record
  • numeric format
  • fixed or variable length
  • quotes on character fields
  • separator in dates

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

• Numeric fields (Integers, Packed, etc) are converted to their character representations
• Character fields are enclosed in quotation marks
• Trailing blanks are (optionally) removed from character fields
• Leading zeros are (optionally) removed from numeric fields
• The fields are separated with delimiters
• Date fields can be reformatted
• Numeric fields with implied decimals have a decimal point inserted
• Output records can be variable-length
• ....and more...

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:

• enclosed character fields in “double-quotes”
• inserted commas between the fields
• removed trailing blanks from character fields
• converted numeric fields to ascii

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:

• More than one sign.
• More than one decimal place.
• Spaces in between numbers.
• Signs that are in between numbers.
• Characters that are not over punch characters.
• Fields that when edited do not fit in the defined space for the display field.
You can control the character that defines the currency, thousand and decimal symbol for other currencies and formats using the following commands:

>set decimalsymbol "."
>set thousandsymbol ","
>set currencysymbol "$"

Suprtool in the above case will strip the currency and thousand symbols and use the decimal symbol to determine the number of decimal places. You can set these characters to any values you want but the defaults for each are used in the above set commands. The decimal and thousand symbols are only single characters.

The currency symbol allows for four characters.

Importing Excel Data With Old Suprtool

If you don't have version 4.6 of Suprtool with the $Number enhancement, there are still some very simple steps that you can take to get the data into a format more usable by Suprtool or COBOL, and subsequently Image.

Typically in Excel, Numbers entered directly into Excel use the "general" or the "number" formats. When they are saved as a txt file the numbers are difficult to import into Suprtool, because they are usually right aligned and have a decimal point in them.

Prepare Excel Data for the Host

The best way to get numbers into Suprtool is to ensure that they have leading zeroes when they are exported from Excel. Once this is done, they can be defined in Suprtool as a Display type.



As you can see below that when you save the file in Excel as a text file, the file has the decimal point in them and they are right aligned. To import them into a database using Suprtool, would require multiple passes.

Neil Armstrong	12.00	14626
Bob Green       12.00	15253
Janine Edwards	14.00	18123
Trenton Roach	34.00	22154

A simple change to the number format from Number to Custom as shown below, results in data that can be easily imported with Suprtool.



This changes the data in the Excel spreadsheet to look as thus:



When you save the data to a text file, then you can easily import the data into Suprtool.

Neil Armstrong	0000000012	0000014626
Bob Green       0000000012	0000015253
Janine Edwards	0000000014	0000018123
Trenton Roach	0000000034	0000022154

Once you have transferred the data to your host system, the trick is to define the input numeric fields as display type. For example the code to get the data input in a format that is usable in Suprtool would be as follows:

Input importf
def name,1,30
def num1,32,10,display
def num2,43,10,display
ext name
ext num1
ext num2
out excelf,link
exit

Import as Double Integers

Notice that you must Define the beginning position of the fields as they appear in the file. If you had to have the numeric fields in say double integer format, then the task becomes:

input importf
def name,1,30
def num1,32,10,display
def num2,43,10,display
def dbl-num1,1,4,double
def dbl-num2,1,4,double
ext name
ext dbl-num1 = num1
ext dbl-num2 = num2
out excelf,link
exit

Using this method, Suprtool is capable of coercing the imported display data fields num1 and num2 into double integers (or other numeric storage types) in a single pass.

Decimal Places

For columns with two decimal places, changing from the two decimal places to the Custom format will cause the number to be a rounded whole number. For example 12.53, would become 13. In order to get rid of two decimal places you just have to multiply by 100. To do this, just put 100 into any blank cell. Select the cell and choose Edit, Copy. Then select the range of values and choose Edit, Paste Special. Choose Multiply and click OK. Then delete the cell that contains the 100.



Negative Values

These techniques work well if the data has no negative values. If the data has negative values, you have to make two passes. Let's say the field num1 has a negative number and the sign is in position 30 of the input file, the task then becomes:

input importf
set squeeze off
def name,1,30
def num1sign,31,1
def num1,32,10,display
def num2,43,10,display
def dbl-num1,1,4,double
def dbl-num2,1,4,double
if num1sign = "-"
ext name
ext dbl-num1 = num1 * -1
ext dbl-num2 = num2
out excelf,link
xeq
input importf
if num1sign <> "-"
ext name
ext dbl-num1 = num1
ext dbl-num2 = num2
out excelf,append
exit

We are planning a new Suprtool function to require only a single pass for accepting negative and positive values, so stay tuned.

Neil Armstrong

---

You might also be interested in two Suprtool articles on exporting 3000 data to mySQL and exporting to Oracle.

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