Legacy Data Extracted and Converted to PC Format

Products

• Vision:Builder

Issue/Introduction

As users have increasingly employ VISION:Builder 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, VISION:Builder includes specifications to allow you to specify alternate report output methods. In addition to the standard report format, 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.

 

Resolution

Here we will show you how 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 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:

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:

 

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