How to Convert Database Records to CSV within RPG IV

Nearly 100% of IBM i shops have the need to convert one or more files to CSV (comma separated values) format. Often these CSV files are imported into Microsoft Excel but there are other uses for CSV such as transfer to a different database on another operating system/platform.

I have been adding CSV support to applications for years. I first created the capability to do this in the late 1990's but have since enhanced and optimized the routine--rewriting 80% of it over the last year or so. Today its easier than ever to convert a database record into CSV format.

Preparing RPG IV Apps for CSV Support

One of the things that CSV support gives you is the ability to integrate it directly into your existing code without modifying any routines. Obviously a couple of lines of code need to be added here and there to actually converts the input records to CSV format, but it is easy to embed this capability without ruining your existing apps.

The first thing you need to do is make sure the Input record you want to convert to CSV format is in contiguous storage. This means the Input Specs for your file needs to be mapped to a Data Structure. Fortunately all contemporary versions of RPG include this capability using the PREFIX keyword. When adding the PREFIX keyword to your File spec, you can include a trailing period. For example:

PREFIX('CM.')

The literal 'CM.' indicates that all Input fields for this file are renamed to include the letters "CM" and a period as their prefix. A field named CMPNAME is renamed to CM.CMPNAME when it is brought into the program. To link this and all the other fields in the file to contiguous storage, a Data Structure must be declared. The data structure in this case must be named "CM" and has an identical layout to the Input record for the file in question. Here is an example:

FCUSTMAST  IF   E             DISK    PREFIX('CM.')
    // This data structures map the RPG input buffer to a data structure
D CM            E DS                  EXTNAME(CUSTMAST) Qualified Inz

The data structure named CM is an externally described data structure based on the CUSTMAST file. I've included the Qualified keyword so that all subfields of the data structure are referenced using the CM.subfield qualification syntax. For example, the Company Name field CMPNAME would be referenced as CM.CMPNAME which is identical to the name created by using the PREFIX keyword on the File spec for the CUSTMAST file. Using this technique, the declaration of the data structure causes the input fields to be declared in contiguous storage. Which is exactly what we need.

Other way to declare the CM data structure is to use the LIKEREC keyword. When you do this, you have to specify the Record Format name of the associated File (the one declared on the File spec) as the parameter of the LIKEREC keyword. For example, if the record format name for CUSTMAST is CUSTREC then LIKEREC(CUSTREC) would be used. Note that when this technique is applied there are three differences: (1) The "E" in position 20 is not specified, (2) The EXTNAME keyword is not used, and (3) The QUALIFIED keyword is not used. In addition there are some anomalies when using LIKEREC that cause issues when the file is declared other than Input (i.e., read-only). A 2nd parameter of the LIKEREC keyword of *ALL normally resolves that issue. But for input-only LIKEREC(CUSTREC) should be fine.

FCUSTMAST  IF   E             DISK    PREFIX('CM.')
    // This data structures map the RPG input buffer to a data structure
D CM              DS                  LikeRec(custRec) Inz

Depending on the version of RPG IV you're using, you can now read the database file directly into the CM data structure. This puts the input data in contiguous storage which can be passed to the CSV routines.

CSV Functions

Just like the regular DB2 database file, you also have to declare and open a CSV file. To do that the csvOpen function is used. They include csvOpen, csvWrite, csvClose (among others).

csvOpen -- Open IFS Text File as a CSV File for Writing

The csvOpen function creates (if necessary) and opens an ASCII text file on the IFS and assigns the format of the DB2 data that will be sent to csvWrite operations. If the file does not exist, it is created. If the file already exists and the Replace parameter is not specified or contains *OFF ('0') subsequent csvWrite operations add the data to the existing file. If Replace=*ON ('1') any existing file is truncated to zero-length upon return from csvOpen.

handle = csvOpen( ifs-streamFile-name : database-File : replace );

The returned handle is a 10i0 value that is used as a reference to the open CSV file. You must pass this value to other CSV functions to indicate that "this" CSV file is the one you want to impact. The returned handle will be greater than or equal to 0 if the open operation is successful. Otherwise a negative value is returned if the open operation fails.

The ifs-streamFile-name parameter is, as the name implies, the name of a file on the IFS that will receive the CSV text. If the file does NOT exist, it will be created as an ASCII text file using CCSID(819).

The database-File parameter is the name of the database whose format is used to convert the data stored in the data structure into CSV format. This should be the same file name in which the CM structure (in our example) is based. Note that this parameter must be the File name, not the format name.

The optional replace parameter indicates what to do if the CSV file (IFS stream file) already exists. The default '0' means add to the existing file; pass a value of '1' to always replace any existing CSV file with new data. Note that if replace='1' the existing data in the file is removed when the csvOpen function returns.

csvWrite - Write DB2 Data as CSV

The csvWrite function converts data stored in a data structure into CSV format and writes the CSV text to the IFS file. The IFS file is identified by the handle parameter. Optionally, the format used for the database data may be changed to a different format using the option 3rd parameter (database-File). If the 3rd parameter is not specified, the format file specified on the csvOpen function is used. Once a database-file name is specified for parameter 3 of csvWrite, that database-file format is used on subsequent csvWrite operations, including those that avoid specifying a database-File name. In other words, specifying a database file name on parameter 3 of csvWrite changes the internal format used for the conversion process for this and subsequent csvWrite operations.

bytesWritten = csvWrite( handle : dataStruct [: database-File ]);

The returned bytesWritten is value is the number of bytes actually written to the CSV file on the IFS.

The handle parameter is the 10i0 variable whose value was returned from a previous call to csvOpen.

The optional dataStruct parameter is the name of the data structure that contains the input data to be converted to CSV format.

csvClose - Close IFS CSV Text File

The csvClose function closes the IFS file, using the provided handle to the file. This is simply a wrapper for the the IFS close() function but also performs additional cleanup tasks.

closeCode = csvClose( handle );

The returned closeCode is merely a file reference for the close operation is is nearly always ignored.

The handle parameter is the 10i0 variable whose value was returned from a previous call to csvOpen.

Once a CSV file is closed, it may be viewed on your IFS file system using any of the usual methods.

DB2 to CSV Logic Cycle

The normal flow of a routine that converts data to CSV format is as follows:

1. Declare the Database File
   • Include the PREFIX keyword with the declaration.
2. Declare a Data Structure with the same format as the File being converted.
   • Name it the same as the ID used on the PREFIX keyword of the File spec.
3. If necessary, open the database file
4. Open the CSV file using the csvOpen function.
   • Specify the IFS file name and the database file name.
5. If the CSV File Handle is negative, issue an error and return--the open failed.
6. Read the database file into the data structure.
7. Write the database record to the IFS as a CSV stream using the csvWrite function.
8. Repeat steps 6 and 7 until EOF.
9. Close the CSV file using the csvClose function.

Converting Database Files to CSV Format using RPG IV - Example

The following is a working example that converts the records of the CUSTMAST file to CSV format and writes them to the IFS file named CUSTMAST.CSV in the /home/cozzi folder.

H BNDDIR('COZTOOLS/COZTOOLS') DFTACTGRP(*NO) ACTGRP(*NEW)
H FIXNBR(*ZONED : *INPUTPACKED)
    // NOTE: The EXTFILE and EXTDESC keywords are used here for
    //       compile-time referencing. However, in real end-user
    //       code, you can declare the file on the File Spec
    //       anyway you want.
FCUSTMAST  IF   E             DISK    EXTFILE('COZTEST/CUSTMAST')
F                                     EXTDESC('COZTEST/CUSTMAST')
    // The Prefix keyword is required in order to pull the record
    // into a continuous (contiguous) memory location.
    //  Declare a Data Structure with the same name.
F                                     PREFIX('CUST.')
 
 /include cozTools/qcpysrc,joblog
 /include cozTools/qcpysrc,csvlib
 
D count           S             10I 0
D hCust           S             10I 0
    // This data structures map the RPG input buffer to a data structure
D cust            DS                  LikeRec( CUSTREC )
C                   MOVE      *ON           *INLR
 /free
        // Open/Create a text file on the IFS using the CUSTMAST file
        // as the "record format".
    hCust = csvOpen('/home/cozzi/CUSTDATA.csv' : 'custmast');
    read custRec;
    dow NOT %EOF();
      count +=1;
          // The hCust variable is the IFS file "handle".
          // The CUST data structure contains the data
          // just read from the CUSTMAST file.
          // The csvWrite function automatically converts
          // the data to CSV format and writes it to
          // the IFS for you.
      csvWrite( hCust: cust );
      read custRec;
    enddo;
    csvClose( hCust );
    joblog('Wrote %s CSV records to the IFS.': %char(count));
    return;
 /end-free

This example includes the BNDDIR('COZTOOLS/COZTOOLS') binding directory so that the CSV library can be included in the program. In addition, the JOBLOG member is included so that the JOBLOG function may be used to write ad hoc messages to the joblog. The CSVLIB member is also included, so that the CSV library functions are accessible within this program.  Remember, when you use a function in COZTOOLS, the no-charge runtime allows the compiled code to run on any other system, unchanged.

Alternative CSV Options

The csvOpen, csvWrite and csvClose routines were created to provide RPG programmers with the tools necessary to create CSV files from within RPG. If you have simpler needs, the CPYTOCSV command that is included in the COZTOOLS package does the best job. It provides additional functionality such as embedding Excel functions for columns totals, converting dates to text, preventing zero-suppression in numeric columns and more; it also allows you to select the fields that are converted and specify the sequence for the data (using an SQL ORDER BY clause).

If you want to convert all the data in a database file to CSV, and don't have a license to COZTOOLS, IBM includes a basic CPYTOIMPF (copy to import file) command that does a fine job for most basic CSV conversion needs--but there are few bells or whistles. The CPYTOCSV command in COZTOOLS provides much more capability. Read more about CPYTOCSV on the COZTOOLS website.

Contact Me

Bob Cozzi is a technical advisor to IBM i clients around the world. His specialty is solving difficult programming issues for his clients, training their programming staffs, and performing system migration/upgrades for small shops. His consulting rates are available online. To contact Bob, send an email to: bob at cozTools.com

You can subscribe to RPG Report (we call it "follow") by visiting the RPG Report page on midrangeNews.com and then click the FOLLOW link in the table of contents for that page. To unsubscribe, simply click that same link. You must be signed up and signed in to midrangeNews.com to start following RPG Report.

Follow Bob Cozzi on Twitter