How to create an excel spreadsheet from an Endevor CSV Report

book

Article ID: 17650

calendar_today

Updated On:

Products

CA Endevor Software Change Manager (SCM) CA Endevor Software Change Manager - Natural Integration (SCM) CA Endevor Software Change Manager - ECLIPSE Plugin (SCM) CA Endevor Software Change Manager - Enterprise Workbench (SCM)

Issue/Introduction

 

I need a way to run a custom Endevor report that will be compatible with a Microsoft Excel Spreadsheet.

 


Environment

Product:  CA  Endevor Software Change Manager (SCM) 

Release:  18.0 

Resolution

The Comma Separated Value (CSV) utility enables users to create their own reports. By using data from the Master Control File (MCF) and the Package Master File (PMF). This information can be passed to any PC software that supports a Comma Separated Value such as Microsoft Excel or Microsoft Access.

To use the CSV utility, follow these steps:

  1. Create the file to be used with Excel or Access running job BC1JPCSV0
  2. Transfer the information from the host to the PC using a download utility
  3. Import the information into the Excel / Access software

The following example shows the JCL used to extract information from CA Endevor Software Change Manger to an Excel spreadsheet for report that will list all elements with a defined inventory structure.

The JCL and scl used to extract the information from Endevor:

 

//*--------------------------------------------------------------------------
//*     STEP 1  --  clean up any files left over from a previous CSV job
//*--------------------------------------------------------------------------
//BR14  EXEC  PGM=IEFBR14
//SYSOUT     DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//DELE1  DD  DSN=iprefix.iqual.CSVFILE,
//           DISP=(OLD,DELETE,DELETE)
//*--------------------------------------------------------------------------
//*     STEP 2  --  EXECUTE THE CSV UTILITY
//*--------------------------------------------------------------------------
//STEP1  EXEC  PGM=NDVRC1,REGION=4M,
//              PARM='CONCALL,DDN:CONLIB,BC1PCSV0'
//STEPLIB  DD  DISP=SHR,DSN=prefix.qual.CSIQAUTU
//         DD  DISP=SHR,DSN=prefix.qual.CSIQAUTH
//CONLIB   DD  DISP=SHR,DSN=prefix.qual.CSIQLOAD
//CSVLST   DD  DSN=iprefix.iqual.CSVFILE,
//                DCB=(DSORG=PS,RECFM=VB,LRECL=4092,
//                BLKSIZE=4096),DISP=(NEW,CATLG,DELETE),
//                SPACE=(TRK,(5,1),RLSE),UNIT=SYSDA
//C1MSGS1  DD  SYSOUT=*         
//BSTERR   DD  SYSOUT=*         
//BSTIPT01 DD  *                        
  LIST ELEMENT *         
   FROM ENVIRONMENT xxxx
               SYSTEM      *  
               SUBSYSTEM   *
               TYPE        *    
               STAGE       *      
    TO DDN CSVLST

 

Once the information is generated, you will see a file similar to the following:

 

"M","0","ENV1","USERID","TESTING","$AMBLST","$AMBLST","DATA"
"M","0","ENV1","USERID","TESTING","#PSXCOME","#PSXCOME","DATA"
"M","0","ENV1","USERID","TESTING","#PSXCOMP","#PSXCOMP","DATA"
"M","0","ENV1","USERID","TESTING","#PSXCOMS","#PSXCOMS","DATA

 

The report information must now be downloaded and saved to your desktop. Make sure that you save your file named for this case ELEMENT_LIST.csv. This step can be done using a variety of different download utilities. Regardless of what utility you use, be sure to specify the following values:

  • ASCII/EBCDIC: Enable this option to translate the file from EBCDIC (IBM Host character set) to ASCII (PC character set) while downloading the file. This option is required when transferring text file.
  • CRLF: Converts the "end of record' character from the host file to carriage returns and line feeds in the PC file. This option is required when transferring text files.
  • TSO: Select the appropriate operating system that your host is running

After the information is downloaded to your desktop, you can just double click on the file "ELEMENT_LIST.csv" and it will open as an EXECL Spreadsheet. If for some reason this does not work for you, you need to import it into the tool that you will be using to generate your report. For example, to import the data into an Excel spreadsheet, follow these steps:

  1. Start Excel
  2. From the Data Menu, select Get External Data, then select from text
  3. The Import test file dialog opens. select the file you downloaded, then click import
  4. The Text Import Wizard Step 1 of 3 dialog opens. Select Delimited and click Next
  5. The Text Import Wizard Step 2 of 3 dialog opens. De-select Tab, select comma, and click Next
  6. The Text Import Wizard Step 3 of 3 dialog opens. Select text select finish
  7. Select either existing worksheet or new worksheet then select ok

The information that was retrieved will now look like this in the Excel spreadsheet:

RCD TYPE DATA TYPE SITE ID ENV NAME SYS NAME SBS NAME ELM NAME FULL ELM NAME TYPE NAME STG NAME
S E 0 ENV1 LEWPA02 TESTING BC1GJCL3 BC1GJCL3 DATA STG1
S E 0 ENV1 LEWPA02 TESTING BC1GJCL3 BC1GJCL3 DATA STG1
S E 0 ENV1 LEWPA02 TESTING DELOAD DELOAD PROCESS STG1
S E 0 ENV1 LEWPA02 TESTING DELOAD DELOAD PROCESS STG1