Creating CSV file in IFS using RPGLE

Creating CSV file in IFS using RPGLE.
Creating CSV file in IFS using RPGLE, Comma Separated Values (CSV) , stream file , How to generate CSV file in IFS using CPYTOIMPF command., RPGLE program for Creating CSV file in IFS, open(), write(), read(), close(), read() procedure in rpgle,  open(), open() api, file descriptor, ifs stream file, reading data from ifs stream file using rpg as400 in ibmi, c apis, as400, ibmi, as400 and sql tricks, as400 tutorial, ibmi tutorial, working with if, integrated file system,
Creating CSV file in IFS using RPGLE

Introduction to CSV file in IFS

A Comma Separated Values (CSV) stream file contains columns data of the SQL Table or Physical file (PF) and are separated by commas. for more information Please refer to How to generate CSV file in IFS using CPYTOIMPF command.

We already created the CSV file in IFS named txtfile3.csv as below:

/home/easyclass/txtfile3.csv
 Browse : /home/EASYCLASS/txtfile3.csv                                         
 Record :       1   of       7 by  14            Column :    1     59 by  79   
 Control :                                                                     
                                                                               
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....
 ************Beginning of data**************                                   
1,NAME1,10                                                                     
10,NAME10,20                                                                   
20,NAME20,30                                                                   
2,NAME2,11                                                                     
11,NAME11,20                                                                   
3,NAME3,12                                                                     
12,NAME12,30                                                                                                      

Create Table

Lets' create a table named PF1 to be used in RPGLE program for creating CSV file for its data.

CREATE TABLE PF1(EMPID CHAR ( 10) NOT NULL WITH DEFAULT, EMPNAME
CHAR ( 20) NOT NULL WITH DEFAULT, MANAGERID CHAR ( 10) NOT NULL 
WITH DEFAULT) rcdfmt rpf1                                       

Use the above SQL script to create a table named PF1 and then you can insert data whatever you want either using SQL INSERT or using UPDDTA command.

RPGLE program for Creating CSV file in IFS

RPG Code in Fixed format for creating a CSV file in IFS in RPGLE.
     HDFTACTGRP(*NO)                                                                                   
     FPF1       IF   E             DISK                                                                
                                                                                                       
     D unlink          PR            10I 0 ExtProc('unlink')                                           
     D ifspath                         *   Value options(*string)                                      
                                                                                                       
                                                                                                       
     D errorifs        PR              *   ExtProc('__errno')                                          
                                                                                                       
     D strerror        PR              *   ExtProc('strerror')                                         
     D error_num                     10I 0 value                                                       
                                                                                                       
                                                                                                       
     D close           PR            10i 0 extproc('close')                     *                      
     D fileds                        10i 0 value                                *file descriptor       
                                                                                                       
     D write           PR            10i 0 extproc('write')                     * 32 bit, no. of byt   
     D fileds                        10i 0 value                                *file descriptor       
     D buffer                          *   value                                * pointer to byte      
     D noofbytes                     10U 0 value                                * 32 bits         
                                                                                                  
     D open            PR            10I 0 extproc('open')                                        
     D ifspath                         *   value options(*string)               *ifs path         
     D oflag                         10I 0 value                                *string of 32 bits
     D mode                          10U 0 value options(*nopass)               * 9 bits          
     D codepage                      10U 0 value options(*nopass)               *                 
                                                                                                  
      * <-----oflag---->                                                                          
     D O_readonly      C                   1                                                      
     D O_writeonly     C                   2                                                      
     D O_readwrite     C                   4                                                      
     D O_createfileifnotexist...                                                                  
     D                 C                   8                                                      
     D O_exclusivecreate...                                                                       
     D                 C                   16                                                     
     D O_truncateto0bytes...                                                                      
     D                 C                   64                                                     
     D O_appendtofile  C                   256                                                    
     D O_converttextbycodepage...                                                                 
     D                 C                   8388608                      
     D O_openintextmode...                                              
     D                 C                   16777216                     
      *                                                                 
      * <-----mode---->                                                 
      * owner,group,other (RWX)                                         
      *                                         owner authority         
     D M_readowner     C                   256                          
     D M_writeowner    C                   128                          
     D M_executeowner  C                   64                           
      *                                         group authority         
     D M_readgroup     C                   32                           
     D M_writegroup    C                   16                           
     D M_executegroup  C                   8                            
      *                                         other people            
     D M_readother     C                   4                            
     D M_writeother    C                   2                            
     D M_executeother  C                   1                            
      *                                                                 
     Difspath          s            512a                                
     Dreturn_unlink    s             10i 0 inz                                                 
     Doflag            s             10I 0                                                     
     Dmode             s             10U 0                                                     
     Dcodepage         s             10U 0 inz(819)                             * ascii cccsid 
     Dfiledescriptor   s             10i 0                                                     
     Difsdata          s            400a   inz                                                 
     Dreturn_write     s             10i 0 inz                                                 
     Dreturn_close     s             10i 0 inz                                                 
                                                                                               
                                                                                               
     Derror_ptr        S               *                                                       
     Derror_num        S             10I 0 based(error_ptr)                                    
                                                                                               
     Derrormsg_ptr     S               *                                                       
     Derror_msg        S            500a   based(errormsg_ptr)                                 
                                                                                               
     DCRLF             C                   CONST(x'0D25')                                      
                                                                                               
     C                   EVAL      ifspath = '/home/easyclass/txtfile3.csv'                    
     C                   EVAL      return_unlink = unlink(%trim(ifspath))                      
     C                   IF        return_unlink < 0                        
     C                   EVAL      error_ptr = errorIFS()                   
     C                   EVAL      errormsg_ptr = strerror(error_num)       
     C                   ENDIF                                              
                                                                            
     C                   EVAL      oflag = O_writeonly +                    
     C                                     O_createfileifnotexist +         
     C                                     O_converttextbycodepage          
     C                   EVAL      mode = M_readowner +                     
     C                                    M_writeowner +                    
     C                                    M_executeowner                    
                                                                            
     C                   EVAL      filedescriptor = open(%trim(ifspath):    
     C                                                   oflag:             
     C                                                   mode:              
     C                                                   codepage)          
                                                                            
     C                   IF        filedescriptor < 0                       
     C                   EVAL      error_ptr = errorIFS()                   
     C                   EVAL      errormsg_ptr = strerror(error_num)       
     C                   RETURN                                             
     C                   ENDIF                                              
                                                                            
     C                   EVAL      return_close = close(filedescriptor)     
     C                   IF        return_close = -1                        
     C                   EVAL      error_ptr = errorIFS()                   
     C                   EVAL      errormsg_ptr = strerror(error_num)       
     C                   RETURN                                             
     C                   ENDIF                                              
                                                                            
     C                   EVAL      oflag = O_writeonly +                    
     C                                     O_openintextmode                 
                                                                            
     C                   EVAL      filedescriptor = open(%trim(ifspath):    
     C                                                   oflag)             
     C                   IF        filedescriptor < 0                       
     C                   EVAL      error_ptr = errorIFS()                   
     C                   EVAL      errormsg_ptr = strerror(error_num)       
     C                   RETURN                                             
     C                   ENDIF                                              
                                                                              
     C     1             SETLL     PF1                                        
     C                   READ      PF1                                        
     C                   DOW       NOT%EOF(PF1)                               
     C                   EVAL      ifsdata = %trim(ifsdata) +                 
     C                                        %trim(EMPID) + ',' +            
     C                                        %trim(EMPNAME) +  ',' +         
     C                                        %trim(MANAGERID)                
                                                                              
     C                   EVAL      return_write = write(filedescriptor:       
     C                                                  %addr(ifsdata):       
     C                                                  %len(%trim(ifsdata))) 
     C                   IF        return_write < %len(%trim(ifsdata))        
     C                   EVAL      error_ptr = errorIFS()                     
     C                   EVAL      errormsg_ptr = strerror(error_num)         
     C                   RETURN                                               
     C                   ENDIF                                                
                                                                              
     C                   EVAL      ifsdata   = CRLF                           
     C                   READ      PF1                                        
     C                   ENDDO                                              
                                                                            
     C                   EVAL      return_close = close(filedescriptor)     
     C                   IF        return_close = -1                        
     C                   EVAL      error_ptr = errorIFS()                   
     C                   EVAL      errormsg_ptr = strerror(error_num)       
     C                   RETURN                                             
     C                   ENDIF                                              
                                                                            
     C                   EVAL      *INLR = *ON                              
     C                   RETURN                                                                                             
RPG Code in /Free and /End-Free format for creating a CSV file in IFS in RPGLE.
     HDFTACTGRP(*NO)                                                                                   
     FPF1       IF   E             DISK                                                                
                                                                                                       
     D unlink          PR            10I 0 ExtProc('unlink')                                           
     D ifspath                         *   Value options(*string)                                      
                                                                                                       
                                                                                                       
     D errorifs        PR              *   ExtProc('__errno')                                          
                                                                                                       
     D strerror        PR              *   ExtProc('strerror')                                         
     D error_num                     10I 0 value                                                       
                                                                                                       
                                                                                                       
     D close           PR            10i 0 extproc('close')                     *                      
     D fileds                        10i 0 value                                *file descriptor       
                                                                                                       
     D write           PR            10i 0 extproc('write')                     * 32 bit, no. of byt   
     D fileds                        10i 0 value                                *file descriptor       
     D buffer                          *   value                                * pointer to byte      
     D noofbytes                     10U 0 value                                * 32 bits         
                                                                                                  
     D open            PR            10I 0 extproc('open')                                        
     D ifspath                         *   value options(*string)               *ifs path         
     D oflag                         10I 0 value                                *string of 32 bits
     D mode                          10U 0 value options(*nopass)               * 9 bits          
     D codepage                      10U 0 value options(*nopass)               *                 
                                                                                                  
      * <-----oflag---->                                                                          
     D O_readonly      C                   1                                                      
     D O_writeonly     C                   2                                                      
     D O_readwrite     C                   4                                                      
     D O_createfileifnotexist...                                                                  
     D                 C                   8                                                      
     D O_exclusivecreate...                                                                       
     D                 C                   16                                                     
     D O_truncateto0bytes...                                                                      
     D                 C                   64                                                     
     D O_appendtofile  C                   256                                                    
     D O_converttextbycodepage...                                                                 
     D                 C                   8388608                      
     D O_openintextmode...                                              
     D                 C                   16777216                     
      *                                                                 
      * <-----mode---->                                                 
      * owner,group,other (RWX)                                         
      *                                         owner authority         
     D M_readowner     C                   256                          
     D M_writeowner    C                   128                          
     D M_executeowner  C                   64                           
      *                                         group authority         
     D M_readgroup     C                   32                           
     D M_writegroup    C                   16                           
     D M_executegroup  C                   8                            
      *                                         other people            
     D M_readother     C                   4                            
     D M_writeother    C                   2                            
     D M_executeother  C                   1                            
      *                                                                 
     Difspath          s            512a                                
     Dreturn_unlink    s             10i 0 inz                                                 
     Doflag            s             10I 0                                                     
     Dmode             s             10U 0                                                     
     Dcodepage         s             10U 0 inz(819)                             * ascii cccsid 
     Dfiledescriptor   s             10i 0                                                     
     Difsdata          s            400a   inz                                                 
     Dreturn_write     s             10i 0 inz                                                 
     Dreturn_close     s             10i 0 inz                                                 
                                                                                               
                                                                                               
     Derror_ptr        S               *                                                       
     Derror_num        S             10I 0 based(error_ptr)                                    
                                                                                               
     Derrormsg_ptr     S               *                                                       
     Derror_msg        S            500a   based(errormsg_ptr)                                 
                                                                                               
     DCRLF             C                   CONST(x'0D25')                                      
      /free                                             
       ifspath = '/home/easyclass/txtfile3.csv';        
       return_unlink = unlink(%trim(ifspath));          
       if return_unlink < 0;                            
         error_ptr = errorIFS();                        
         errormsg_ptr = strerror(error_num);            
       endif;                                           
                                                        
       oflag = O_writeonly +                            
               O_createfileifnotexist +                 
               O_converttextbycodepage;                 
       mode =                                           
              M_readowner +                             
              M_writeowner +                            
              M_executeowner;                           
                                                        
       filedescriptor = open(%trim(ifspath):            
                             oflag:                     
                             mode:                      
                             codepage);                 
                                                          
       if filedescriptor < 0;                             
         error_ptr = errorIFS();                          
         errormsg_ptr = strerror(error_num);              
         return;                                          
       endif;                                             
                                                          
       return_close = close(filedescriptor);              
       if return_close = -1;                              
         error_ptr = errorIFS();                          
         errormsg_ptr = strerror(error_num);              
         return;                                          
       endif;                                             
                                                          
                                                          
       oflag = O_writeonly +                              
               O_openintextmode;                          
                                                          
       filedescriptor = open(%trim(ifspath):              
                             oflag);                      
                                                                
       if filedescriptor < 0;                                   
         error_ptr = errorIFS();                                
         errormsg_ptr = strerror(error_num);                    
         return;                                                
       endif;                                                   
                                                                
       setll 1 pf1;                                             
       read pf1;                                                
       dow not%eof(pf1);                                        
                                                                
         ifsdata = %trim(ifsdata) +                             
                   %trim(EMPID) + ',' +  %trim(EMPNAME) +  ','  
                   + %trim(MANAGERID);                          
         return_write = write(filedescriptor:%addr(ifsdata):    
                        %len(%trim(ifsdata)));                  
         if return_write < %len(%trim(ifsdata));                
           error_ptr = errorIFS();                              
           errormsg_ptr = strerror(error_num);                  
           return;                                              
         endif;                                 
         ifsdata   = CRLF ;                     
         read pf1;                              
       enddo;                                   
                                                
       return_close = close(filedescriptor);    
       if return_close = -1;                    
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;                                   
                                                
       *inlr = *on;                             
       return;                                  
      /end-free                                                                                                
RPG Code in Fully Free format for creating a CSV file in IFS in RPGLE.
**FREE                                       
CTL-OPT DFTACTGRP(*NO);                      
DCL-F PF1;                                   
                                             
DCL-PR unlink int(10) EXTPROC('unlink');     
  ifspath pointer VALUE options(*string);    
END-PR;                                      
                                             
DCL-PR errorifs pointer EXTPROC('__errno');  
END-PR;                                      
                                             
DCL-PR strerror pointer EXTPROC('strerror'); 
  error_num int(10) VALUE;                   
END-PR;                                      
                                             
DCL-PR close int(10) EXTPROC('close');       
  fileds int(10) VALUE;                      
END-PR;                                      
                                             
DCL-PR write int(10) EXTPROC('write');        
  fileds  int(10) VALUE;                      
  buffer  pointer VALUE;                      
  noofbytes uns(10) VALUE;                    
END-PR;                                       
                                              
DCL-PR open int(10) EXTPROC('open');          
  ifspath pointer VALUE options(*string);     
  oflag   int(10) VALUE;                      
  mode uns(10) VALUE options(*nopass);        
  codepage uns(10) VALUE options(*nopass);    
END-PR;                                       
                                              
// * <-----oflag---->                         
DCL-C O_readonly 1;                           
DCL-C O_writeonly 2;                          
DCL-C O_readwrite 4;                          
DCL-C O_createfileifnotexist 8;               
DCL-C O_exclusivecreate 16;                   
DCL-C O_truncateto0bytes 64;                  
DCL-C O_appendtofile  256;                                       
DCL-C O_converttextbycodepage 8388608;                           
DCL-C O_openintextmode 16777216;                                 
//    * <-----mode---->                                          
//    * owner,group,other (RWX)                                  
//    *                                         owner authority  
DCL-C M_readowner 256;                                           
DCL-C M_writeowner 128;                                          
DCL-C M_executeowner 64;                                         
//    *                                         group authority  
DCL-C M_readgroup 32;                                            
DCL-C M_writegroup 16;                                           
DCL-C M_executegroup 8;                                          
//    *                                         other people     
DCL-C M_readother 4;                                             
DCL-C M_writeother 2;                                            
DCL-C M_executeother 1;                                          
                                                                 
DCL-S ifspath CHAR(512);                                         
DCL-S return_unlink int(10);            
DCL-S oflag int(10);                                    
DCL-S mode  uns(10);                                    
DCL-S codepage uns(10) inz(819);   // ascii codepage    
DCL-S filedescriptor int(10);                           
DCL-S ifsdata char(400) inz;                            
DCL-S return_write int(10) inz;                         
DCL-S return_close int(10) inz;                         
                                                        
DCL-S error_ptr pointer;                                
DCL-S error_num int(10) based(error_ptr);               
DCL-S errormsg_ptr pointer;                             
DCL-S error_msg char(500) based(errormsg_ptr);          
                                                        
DCL-C CRLF CONST(x'0D25');                              
                                                        
       ifspath = '/home/easyclass/txtfile3.csv';        
       return_unlink = unlink(%trim(ifspath));          
       if return_unlink < 0;                            
         error_ptr = errorIFS();                        
         errormsg_ptr = strerror(error_num);            
       endif;                                   
                                                
       oflag = O_writeonly +                    
               O_createfileifnotexist +         
               O_converttextbycodepage;         
       mode =                                   
              M_readowner +                     
              M_writeowner +                    
              M_executeowner;                   
                                                
       filedescriptor = open(%trim(ifspath):    
                             oflag:             
                             mode:              
                             codepage);         
                                                
       if filedescriptor < 0;                   
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;                                   
                                                
       return_close = close(filedescriptor);    
       if return_close = -1;                    
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;                                   
                                                
                                                
       oflag = O_writeonly +                    
               O_openintextmode;                
                                                
       filedescriptor = open(%trim(ifspath):    
                             oflag);            
                                                
       if filedescriptor < 0;                   
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;                                   
                                                                  
       setll 1 pf1;                                               
       read pf1;                                                  
       dow not%eof(pf1);                                          
                                                                  
         ifsdata = %trim(ifsdata) +                               
                   %trim(EMPID) + ',' +  %trim(EMPNAME) +  ','    
                   + %trim(MANAGERID);                            
         return_write = write(filedescriptor:%addr(ifsdata):      
                        %len(%trim(ifsdata)));                    
         if return_write < %len(%trim(ifsdata));                  
           error_ptr = errorIFS();                                
           errormsg_ptr = strerror(error_num);                    
           return;                                                
         endif;                                                   
         ifsdata   = CRLF ;                                       
         read pf1;                                                
       enddo;                                                     
                                                                  
       return_close = close(filedescriptor);                      
       if return_close = -1;                      
         error_ptr = errorIFS();                  
         errormsg_ptr = strerror(error_num);      
         return;                                  
       endif;                                     
                                                  
       *inlr = *on;                               
       return;                                                             

Explanation of the code

Let's discuss what this code is doing.

       ifspath = '/home/easyclass/txtfile3.csv';        
       return_unlink = unlink(%trim(ifspath));          
       if return_unlink < 0;                            
         error_ptr = errorIFS();                        
         errormsg_ptr = strerror(error_num);            
       endif;                 

Here, we pass ifspath to the unlink() api along with the file name txtfile3.csv to delete the file from ifs if already exists with this name so that later we can create ASCII (codepage 819) based ifs file. We did called error handling apis as well to get error number and error message when unlink() api retruns less than zero.

       oflag = O_writeonly +                    
               O_createfileifnotexist +         
               O_converttextbycodepage;         
       mode =                                   
              M_readowner +                     
              M_writeowner +                    
              M_executeowner;                   
                                                
       filedescriptor = open(%trim(ifspath):    
                             oflag:             
                             mode:              
                             codepage);         
                                                
       if filedescriptor < 0;                   
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;                                 

Now, we create and open the ascii file using open() api by passing oflag and assigned owner read, write and execute authority on the file. Also, file created by passing codepage 819 which is for ASCII. We also did the error handling in case ASCII file creation failed i.e. filedescriptor is less than zero then call error handling apis to get error number and error message.

       return_close = close(filedescriptor);    
       if return_close = -1;                    
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;        

Then we call close() api by passing file descriptor returned by open() api. We also did the error handling in case ASCII file closing failed i.e. close() api return the return value as -1 then call error handling apis to get error number and error message.

       oflag = O_writeonly +                    
               O_openintextmode;                
                                                
       filedescriptor = open(%trim(ifspath):    
                             oflag);            
                                                
       if filedescriptor < 0;                   
         error_ptr = errorIFS();                
         errormsg_ptr = strerror(error_num);    
         return;                                
       endif;    

Now, the ASCII file is created, let's open the same file using open() api by passing oflag as write only and open in text mode. We also did the error handling in case ASCII file open failed i.e. filedescriptor is less than zero then call error handling apis to get error number and error message.

       setll 1 pf1;                                               
       read pf1;                                                  
       dow not%eof(pf1);                                          
                                                                  
         ifsdata = %trim(ifsdata) +                               
                   %trim(EMPID) + ',' +  %trim(EMPNAME) +  ','    
                   + %trim(MANAGERID);                            
         return_write = write(filedescriptor:%addr(ifsdata):      
                        %len(%trim(ifsdata)));                    
         if return_write < %len(%trim(ifsdata));                  
           error_ptr = errorIFS();                                
           errormsg_ptr = strerror(error_num);                    
           return;                                                
         endif;                                                   
         ifsdata   = CRLF ;                                       
         read pf1;                                                
       enddo;                          

Now we perform setll and read to set the pointer on rrn 1 on file PF1 and read whole file for all the data from start in loop. Inside the loop we will build ifsdata variable to be written, we concatenate file fields separated by comma and write the data to the ifs file using write() api. We did error handling here as well, incase if the actual data being written as return by write() api is less than length of the data to be written then we call error handling apis to get error number and error message.Then we set the ifsdata variable as constant hex value CRLF so that next write would happen on the next line.The code will run in loop and read all the data from PF1 and sepaarte all the rows by line and columns by commnas.

       return_close = close(filedescriptor);                      
       if return_close = -1;                      
         error_ptr = errorIFS();                  
         errormsg_ptr = strerror(error_num);      
         return;                                  
       endif;                                     
                                                  
       *inlr = *on;                               
       return;             

Finally we close the file using close() api by passing filedescriptor being return by the open() api and then check if return value by close() api is -1 then we call the error handling apis. Filally Last record indicator set to ON and return from the program. In case of any error during error handling on any api call we returned from the program.

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain