Creating CSV documents

When creating a CSV document, extraneous information such as page titles, field headings, and control break annotations must be removed from the input.

Additionally, you may want to remove float characters from the numeric fields and re-arrange the sign such that the spreadsheet program can intelligently format numeric cells.

Finally, you need to insert a special character between each field to delineate columns for import.

RMU provides methods and statements specifically designed to make these tasks simple.

To create a CSV file, code FORMAT CSV='&chr' on the PARM statement where: &chr is the separator character to be placed between the fields.
Use these RMU methods in the assign statement to format column values:

.ETEXT

For alphanumeric fields.

.ENUM1

For numeric and numeric edited fields.
Use PAGE-COUNT and LINE-COUNT to determine the page properties.
Use the BYPASS statement to remove a specific line or a range of lines.
Use the IDX subscript to control a DO loop for a specific number of lines.

The line is selected for output if at least one method is applied, otherwise the line is bypassed.

Note: CSS (color, fonts, and so on) and images cannot be applied to a CSV file.

The RMU Script program shown in the next figure creates a CSV file from the FZHRPT00 report located in &SYS1.SFZHJCLS. The source code is FZHTEST4 located in the &SYS1.SFZHJCLS library.

PARM LIST COBOL LKED FORMAT CSV=':' DECIMAL (PERIOD) CURRENCY($) 
*********************************************************************** 
* This program converts test report to RMU CSV file format.           *
*                                                                     * 
* The program demonstrates how to trim un-needed report lines and     * 
* strings from a report. The output is a CSV file ready for import    * 
* into a Spreadsheet.                                                 * 
*                                                                     * 
* The trimming is done via the .ETEXT and .ENUM1 RMU methods          * 
*********************************************************************** 
IF (PAGE-COUNT > 1)
* REPORT TITLES ARE BYPASSED ON ALL BUT FIRST PAGE.
   BYPASS LINE1-LINE7
ELSE
* PRESERVE THE FIRST PAGE TITLE LINES FOR spreadsheet
   LINE1 (2: 80) = .ETEXT
   IDX = 1
   DO 6 TIMES
      IDX = (IDX + 1)
      LINE (02: 07) = .ETEXT
      LINE (13: 06) = .ETEXT
      LINE (23: 07) = .ETEXT
      LINE (34: 14) = .ETEXT
      LINE (52: 06) = .ETEXT
      LINE (62: 10) = .ETEXT
   END-DO
END-IF
IDX = 7
    * Line 8 to LINE-COUNT are detail lines on every page
DO WHILE (IDX < LINE-COUNT)
   IDX = (IDX + 1)
* get rid of Control break and FINAL lines
   IF (LINE (2: 5) = 'FINAL')
   OR (LINE (2: 6) = 'FILEIN')
   OR (LINE (24: 4) = SPACES)
      BYPASS LINE
   ELSE
* construct a row of character separated values
      LINE (02: 07) = .ETEXT
      LINE (13: 06) = .ETEXT
      LINE (23: 07) = .ETEXT
      LINE (34: 14) = .ENUM1
      LINE (52: 06) = .ENUM1
      LINE (62: 10) = .ENUM1
   END-IF
END-DO

The next figure shows the first few records created by the program


4/08/08      COMPANY= 10    THIS IS A TEST REPORT-1               PAGE 1 
        :        :         :               :        :           : 
        :        :         :               :        :           :
        :        :         :               :        :           :
COMPANY : BRANCH : OFFICER :               :        :           :
  C     : NUMBER : NUMBER  :     WAGE      :  RATE  :    BONUS  : 
        :        :         :               :        :           :
10      :  001   :  DDDD   :     57500.00  : 10.900 :      6267 :
        :        :  EBEE   :     60000.00  : 11.000 :      6600 :
        :        :  EBEE   :     60000.00  : 11.000 :      6600 :
        :        :  EBEE   :     60000.00  : 11.000 :      6600 :
        :        :  EBEE   :     60000.00  : 11.000 :      6600 :
        :        :  EBEE   :     60000.00  : 11.000 :      6600 :