Create an excel spreadsheet from an Endevor CSV Report

Products

• Endevor
• Endevor Natural Integration
• Endevor - ECLIPSE Plugin
• Endevor - Enterprise Workbench

Issue/Introduction

 

Is there 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, 18.1 

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 BC1JCSVU
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=iprfx.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=iprfx.iqual.CSIQAUTU
//         DD  DISP=SHR,DSN=iprfx.iqual.CSIQAUTH
//CONLIB   DD  DISP=SHR,DSN=iprfx.iqual.CSIQLOAD
//CSVLST   DD  DSN=iprfx.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	USERID	SUBS1	BC1GJCL3	BC1GJCL3	DATA	STG1
S	E	0	ENV1	USERID	SUBS1	BC1GJCL3	BC1GJCL3	DATA	STG1
S	E	0	ENV1	USERID	SUBS1	DELOAD	DELOAD	PROCESS	STG1
S	E	0	ENV1	USERID	SUBS1	DELOAD	DELOAD	PROCESS	STG1