 |
| 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
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
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
**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.