Legacy Data Extracted and Converted to PC Format

Document ID : KB000055780
Last Modified Date : 14/02/2018
Show Technical Document Details

As users have increasingly employed CA VISION:Builder or CA VISION:Two to prepare data for input to other products, processes, and platforms, it has become increasingly important to present that data in industry-standard open formats. To facilitate this process, CA VISION:Builder includes specifications to allow you to specify alternate report output methods. In addition to the standard report format, CA VISION:Builder provides five additional methods which can be used to output the content of a report: comma delimited, tab delimited, plain text, HTML and raw data.

The Comma-Delimited Data method is used for preparing data for import into spreadsheets or other tools that will accept this form of input. It formats the columns of each detail or summary line as a series of fields delimited by commas. Column heading lines are also formatted as delimited data lines.

Here we will show you how easy it is to extract data to a comma delimited file and import it into a Microsoft Excel Spreadsheet for a simple Inventory report.

First, extract the data with these CA VISION:Builder commands:

 CONTROL
 FILE MASTER INPUT, NAME ITEMFILE
 FILE REPORT
 ORDERPT: REPORT,
          COLUMNS ITEMNO, ITEMNAME, QANONHND, REORDPT, ORDQTY
          FORMAT METHOD CSV   DDNAME CSVDATA
 END REPORT

This results in the extracted file we will import into a Microsoft Excel Spreadsheet:

 ITEM,ITEM,QTY ON,REORDER,ORDER
NUMBER,NAME, HAND, POINT, QTY
BL-B01,BLDR QUICK REF,249,100,200
BL-E68,BUILDER CONCEPTS,87,100,200
BL-F43,BLDR MESSAGES,16,100,200
BL-H11A,BLDR HANDBOOK ASL,56,100,200
BL-H11F,BLDR HANDBOOK FIXED,50,50,100
BL-T01B,BLDR TRAINING BASIC,15,5,5
BL-T02A,BLDR TRAINING ADV,15,5,5
GN-N34,DATA BASE CONCEPTS,14,50,50
IN-E13,INFORM USER GUIDE,175,100,200
IN-G61,INFORM TRAINING,85,100,200
IN-J17,INFORM CONCEPTS,197,100,200
IN-M41,INFORM MESSAGES,62,75,150
IN-N19C,INFORM INSTALL CICS,11,75,150
IN-N19I,INFORM INSTALL IMS,38,25,50
TR-M52,TRANSACT CONCEPTS,28,25,25
TR-M55,TRANSACT INSTALL,3,25,25
TR-M89,TRANSACT MESSAGES,19,25,50
TR-N94C,TRANSACT CICS GUIDE,30,25,25
TR-N94I,TRANSACT IMS GUIDE,15,25,25
TR-O13,TRANSACT QUICK REF,59,25,50
TR-O17,TRANSACT INST UPDT,25,25,25

You can use any file transfer program or the file transfer option on any emulator to download the data to your PC. In this example, WS_FTP95 PRO by Ipswitch, Inc. is used.

Target file, CSVDATA.txt, is specified under Local System and source file, CSVDATA, is specified under Remote System:

Figure 1

Import the downloaded CSVDATA.txt file into Microsoft Excel using the Text Import Wizard feature under Tools.
Specify "Delimited" as the Original Data Type and "Comma" as the Delimiter:

Figure 2

and you are DONE - the hero of your department!

Figure 3

Also note that CA VISION:Builder is delivered with "ready to run" examples from the Reference Guide that are very useful in understanding the product for new users.