![[Access to Wang masthead]](images/access89.gif)
|
|
VS transfers to PC spreadsheets |
|
From "VS Workshop", Access to Wang, May 1989 |
|
| [ Prior Article ] [ Return to the Catalog of articles ] [ Next Article ] | |
With increasing frequency, minicomputer and mainframe users are requesting transfer of information to PC spreadsheet format. While several commercial products exist to meet these needs - including VS 20/20, PC/VS Data Exchange, VS InfoSharer, and others - many of these needs are occasional. How do you reconcile the obvious needs and desires of PC users with reasonable effort and cost?
One method we use is to prepare carefully structured VS print files, transfer them to the PC, and IMPORT them into a spreadsheet. Properly formatted, these files move directly into spreadsheet cells and require only minor adjustments to use.
To use the IMPORT approach, you will need experience with a report generator or program language (the REPORT utility will do), a method of transferring the VS file to the PC, and an appropriate PC spreadsheet system. Simple needs work best, since manual rework will still be required at the PC end, and your report should have no more than ten columns of data. Within these practical limits, the IMPORT approach is a viable alternative to costly software or programming effort.
Lotus 1-2-3 and similar PC spreadsheet products offer an option to load data from ASCII report files; in 1-2-3 this is done through the /FILE IMPORT command. Within this command there are two options: "Text" or "Numbers". (In QUATTRO these options are "ASCII Text File" and "Comma & "" Delimited File", respectively.) The first option creates an image of the file but does not recognize the beginning or end of a column of information; as a result, you must later use the /DATA PARSE command to identify the informational areas of the rows and load them into separate columns in the spreadsheet.
The NUMBERS option is of more interest, since it can recognize data items and create columns of information directly from them. In the NUMBERS approach, the content of each text row is examined, allowable items converted into columns, and anything else is ignored. To work properly, the file must meet the following requirements:
1. The file must be a true text file; that is, it must contain only normal typing characters ("a" through "z", "A" through "Z", "0" through "9", "%", @, etc.). PRINTER CONTROL CHARACTERS ARE NOT ALLOWED.
2. Lotus 1-2-3 requires a file extension of ".PRN." (This is optional with most other spreadsheets.)
3. Numeric data must contain only the numbers, a single decimal point, or a LEADING negative sign. Slashes, CR/DB indicators, commas, and plus signs will all be ignored, causing misinterpretation of the data. Leading zeros are no problem.
4. Character data must be enclosed in double quotes.
5. Multiple print lines are allowed, but each will form a row in the spreadsheet file.
Some examples:
VALUE RESULT 123456 123456 1234.56 1234.56 $123456 123456 1,234.56 1 234.56 -1234.56 -1234.56 1234.56- 1234.56 1234.56CR 1234.56 0000456 456 (1234.56) 1234.56 +1234.56 <blank> "This is a label" 'This is <more text hidden> "08/06/45" '08/06/45' 'bad data' <ignored> 1-2-3 1 -2 -3
(three columns of data)
In our shop, many of our COBOL report programs have been modified to include an option to create an IMPORT file. For ad hoc needs, I have also used the Wang REPORT utility, CREATE, and other approaches to build these files. The approach you take will be dictated by your needs and the tools available to you; the REPORT utility is universal among Wang users, so it provides the best example here.
REPORT definitions for IMPORT purposes can be little changed from ordinary REPORTs. Numeric items should avoid numeric edit characters such as commas and dollar signs. Since the REPORT utility uses only trailing signs, it will not be easy to capture a negative value for meaningful use in the spreadsheet, and a programming language may be the best approach. Text fields should be specified as follows:
1. Create a new field for each text item to be IMPORTed. Define this item as (C)haracter, with a length that is two characters greater than the original field.
2. Define the contents of each of these new fields as the double quote character appended to both sides of the original data item. (See example below.)
3. Select this new field for the print line instead of the normal field.
On the NEW FIELDS screen in REPORT, this would look like this:
FIELD/CONSTANT OP FIELD/CONSTANT OP NEWITEM = """___________ & OLDITEM_______ & """___________ _ ______________ _ ______________ _ ______________ _If you wish the column headers to appear, be sure to surround their names with double quotes as well.
Several major approaches and product types are available to transfer the completed VS file to the PC for use. These include:
VS-to-PC file transfer programs: the file is moved directly to the PC with transfer products such as Lightspeed MVS from MacSoft, VSPC928 from Software Business Applications, or Wang's PC/VS Data Exchange or PC InfoSharer.
2110 emulation software: the user logs onto the VS and transfers the file to the PC using the terminal emulation software. Examples include VS-Transit from DPZ, VSPC2110 from Software Business Applications, VSterm from MacSoft, VsCom from M/H group, AllegroServer/VS from OmniGate, and CPTALK from Creative Process.
Disk-to-disk copy and conversion programs: a VS-format disk is converted to DOS format, or areas of the VS disk are accessible by PCs (i.e. Virtual Disk). Products include VSACCESS from Wang, VSDISK from MacSoft and VSPCCOPY and VSPCVDSK from Software Business Applications.
Conversion to Word Processing: the VS print file is converted to Word Processing format and archived to diskette, then the archive diskette is converted to PC format. Products include Wang's PC Integrated Word Processing (the Document Filing option will convert VS archive diskettes) or document conversions programs such as Archive-Link and W-Links from M/H Group, MacSoft Document Conversion Utilities, or Word Doctor "PLUS" from MCS Group.
When transferring files between systems, be aware of the differences between a text file and a print file. Print files contain printer control characters that must be removed before the file can be IMPORTed. On the VS, these characters occupy the first two positions of the record. If the transfer method you select does not already offer a PC text file option, you may have to write a program or a procedure to remove these characters. (CREATE works well for this purpose.)
Once the file is actually on the PC, the process is very simple to create a spreadsheet from it. Enter the program, then the /(F)ile (I)mport commands. As mentioned above, the name of the process may vary with the spreadsheet product you are using, but there should be an option for comma or quote delimited files; choose this option. The program will prompt for the location of the PC file to be imported, with files with a '.PRN' extension found first. If another extension was used, you may have to specify the file name manually rather than picking the name from a list.
As the text file is converted, the information will appear in individual columns. Since many data columns will exceed the standard column width for a cell, and you will have to adjust the column width to see all of the information; use the /COLUMN WIDTH command to do so. Once the file appears the way you wish, you may save the new spreadsheet file normally.
Products mentioned:
Creative Process: CPTALK
DPZ: 2110 emulation; VS-TRANSIT
M/H Group: VsCom; Archive-Link; W-Links
MacSoft: Lightspeed MVS; VSterm; VSDISK; MacSoft Document Conversion Utilities
MCS Group: Word Doctor "PLUS"
Omnigate: AllegroServer/VS
Software Business Applications: VSPC928; VSPC2110; VSPCCOPY; VSPCVDSK
Wang: PC/VS Data Exchange; PC InfoSharer; PC Integrated Word Processing
Copyright © 1989 Dennis S. Barnes
Reprints of this article are permitted without notification
if the source of the information is clearly identified