Exporting IMAGE/SQL Data to other Applications:
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:
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).
>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.
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.45can 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.545Suprtool 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 >xeqThe $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:
>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.
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.
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
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.
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.
Or another article on exporting to XML, with an introduction to XML for newcovers.