Using the Endevor CSV Utility (Comma Separated Value) to interface with Microsoft Excel

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

Introduction:

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

Environment: 

All supported releases of Endevor Software Change Management. 

Instructions:

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=prefix.qual.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=prefix.qual.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      xxx  
               SUBSYSTEM   xxx
               TYPE        *    
               STAGE NUM   1    
    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 TYPEDATA TYPESITE IDENV NAMESYS NAMESBS NAMEELM NAMEFULL ELM NAMETYPE NAMESTG NAME
SE0ENV1LEWPA02TESTINGBC1GJCL3BC1GJCL3DATASTG1
SE0ENV1LEWPA02TESTINGBC1GJCL3BC1GJCL3DATASTG1
SE0ENV1LEWPA02TESTINGDELOADDELOADPROCESSSTG1
SE0ENV1LEWPA02TESTINGDELOADDELOADPROCESSSTG1

Additional Information:

All information concerning CSV can be found in the Utilities Guide Using the Comma Separated Value (CSV) Utility or you can open an issue with your local CA Endevor Support Team.