Talking to a colleague this morning, we were discussing a vendor supplied series of spreadsheets (in CSV format) that his team needed to process.
“What’s the best way to read all the spreadsheets in an IFS folder so we can process them?”
bald bloke in the office
Well, we have a nice simple technique to use in a little CL (IBM i Control Language) program using QSHELL to list the files. Since we just want to read a list of all the *.CSV files in a folder, lets generate a simple list of those files into a temporary work file and read it, processing each one as we go.
List all files using QSHELL
QSH CMD('ls /home/littenn/*.csv -a | sort')
What are we saying here:
- LS – List
- /home/littenn – this is the folder that we want to list
- *.csv – only list the comma separated value files (ie: something.csv)
- -a – displays all files
- | sort – give it in a nice sorted list please old chap
QSHELL is a funny old thing – even though it runs as part of your interactive session, it’s not really running as part of your interactive session. QSHELL runs as a background job which talks to your interactive session while its doing things. It uses three different files to communicate with you:
- STDIN – the input file (or commands you want to run)
- STDOUT – the output file ( or results from the input command)
- STDERR – that’s right. The Error file. #oops
When you are using QSHELL, your running job becomes the terminal while the background QSHELL job launches into action waiting for input from the STDIN process. Anything you type into QSHELL gets placed in the STDIN pile, its processed and the results are placed in the STDOUT pile for you to enjoy. Or, obviously, the STDERR pile for you to not enjoy.
Example IBM i CLLE Program
This code snippet will override the response from QSHELL (STDOUT) direct into a file in QTEMP so we can read it. It will position itself into the folder that is being read. Use QSHELL to list the files – the response will be sent to our file. Then we read the file and do whatever magic we want with the files:
CMD – READ_DIR
CMD PROMPT('Process files in IFS')
PARM KWD(DIR) TYPE(CHAR) LEN(255) EXPR(YES) PROMPT('IFS Folder Name ie: /home/bob')
PARM KWD(FILTER) TYPE(CHAR) LEN(30) DFT('.') EXPR(YES) PROMPT('Filter files (ie: *.CSV)')
CLLE – READ_DIR
/* READ_DIR - Read all CSV files in a given IFS dir and process them */
/* Program : READ_DIR */
/* Author : nick litten */
/* Function: List selected files in an IFS DIR using QSHELL. */
/* Read that file and do some stuff with the files that are listed */
/* Compile Instructions.. 1: CRTPF FILE(QTEMP/READ_DIRF) RCDLEN(1000)*/ /* 2: CRTBNDCL PGM('library'/READ_DIR) */
/* SRCFILE('library'/QCLLESRC) */
/* SRCMBR(READ_DIR) */
/* REPLACE(*YES) */
PGM PARM(&DIR &FILTER)
COPYRIGHT TEXT('READ_DIR Ver.000')
DCLF FILE(READ_DIRF) OPNID(FILE)
DCL VAR(&DIR) TYPE(CHAR) LEN(255)
DCL VAR(&FILTER) TYPE(CHAR) LEN(30)
DCL VAR(&PREVPATH) TYPE(CHAR) LEN(1024)
DCL VAR(&PPATHLEN) TYPE(DEC) LEN(7 0)
DCL VAR(&QSHSTRING) TYPE(*CHAR) LEN(1024)
DCL VAR(&CNLSTS) TYPE(CHAR) LEN(1) VALUE('0')
DCL VAR(&CRASHED) TYPE(LGL) VALUE('0')
DCL VAR(&MSGID) TYPE(CHAR) LEN(7)
DCL VAR(&MSGDTA) TYPE(CHAR) LEN(256)
DCL VAR(&MSGFIL) TYPE(CHAR) LEN(10)
DCL VAR(&MSGFLIB) TYPE(CHAR) LEN(10)
DCL VAR(&MSGKEY) TYPE(CHAR) LEN(4)
DCL VAR(&RTNTYPE) TYPE(CHAR) LEN(2)
DCL VAR(&SENDER) TYPE(*CHAR) LEN(80)
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(CRASH)) /* Global monitor to pick non monitored error conditions */
/* Validate the directory name */
CHKLNK OBJ(&DIR)
MONMSG MSGID(CPF0000) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG)
MSGDTA(' Directory' *BCAT &DIR *TCAT ' not found')
TOPGMQ(PRV) MSGTYPE(ESCAPE)
ENDDO
/* collect the current directory we are pointed at - we will revert to this when we finish */
RTVCURDIR RTNDIR(&PREVPATH) DIRNAMLEN(&PPATHLEN)
CD DIR(&DIR)
MONMSG MSGID(CPFA09C) EXEC(DO)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG)
MSGDTA('* Not authorised to directory' BCAT &DIR)
TOPGMQ(PRV) MSGTYPE(*ESCAPE)
ENDDO
/* create a work file to hold the list of files that we load from the IFS directory */
DLTF FILE(QTEMP/READ_DIRF)
MONMSG MSGID(CPF0000)
CRTPF FILE(QTEMP/READ_DIRF) RCDLEN(1000)
/* load the work file with a list of all the selected files in the IFS directory */
OVRDBF FILE(STDOUT) TOFILE(QTEMP/READ_DIRF) OVRSCOPE(JOB)
CHGVAR VAR(&QSHSTRING) VALUE('ls' *BCAT &DIR *TCAT '/' *TCAT &FILTER *TCAT ' -a | sort')
QSH CMD(&QSHSTRING)
DLTOVR FILE(STDOUT) LVL(*JOB)
IF COND(&PPATHLEN *GT 0) THEN(CD DIR(&PREVPATH))
OVRDBF FILE(READ_DIRF) TOFILE(QTEMP/READ_DIRF)
POSITION(START) OVRSCOPE(CALLLVL)
/* read the list of files from the IFS folder we listed */
DOWHILE COND(&CNLSTS = '0')
RCVF OPNID(FILE)
MONMSG MSGID(CPF0864) EXEC(LEAVE)
/* field &READ_DIRF is the file name ie: something.csv */
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG)
MSGDTA('-- Doing something with' *BCAT &FILE_READ_DIRF)
TOPGMQ(*PRV) MSGTYPE(*COMP)
/* Add your logic here to do the IFS FILE magic */
RTVJOBA ENDSTS(&CNLSTS)
ENDDO
DLTOVR FILE(READ_DIRF) LVL(*)
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Directory' *BCAT &DIR *TCAT ' has been read and files(' *TCAT &FILTER *TCAT ') have been processed') TOPGMQ(PRV) MSGTYPE(COMP)
RETURN
/* CRASH : Routine to handle unexpected errors */
CRASH:
IF COND(&CRASHED) THEN(RETURN)
CHGVAR VAR(&CRASHED) VALUE('1')
RCVMSG MSGTYPE(*LAST) MSGDTA(&MSGDTA) MSGID(&MSGID) RTNTYPE(&RTNTYPE) MSGF(&MSGFIL) SNDMSGFLIB(&MSGFLIB)
IF COND(&RTNTYPE *EQ '15' *OR &RTNTYPE *EQ '17') THEN(DO)
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFIL) MSGDTA(&MSGDTA) MSGTYPE(*DIAG)
ENDDO
ESCAPE:
IF COND(&PPATHLEN *GT 0) THEN(DO)
CD DIR(&PREVPATH)
MONMSG MSGID(CPF0000)
ENDDO
SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('* Program READ_DIR ended abnormally') MSGTYPE(*ESCAPE)
RETURN
ENDPGM: ENDPGM
I know I know….
That program just sort of grew as I put in my ear buds (not those poncy apple air pods) and got some programming grooove on.
When this runs it looks something like this:

Hope it helps someone 🙂
Thanks Nick. We use SQL to accomplish this at my shop. After a little bit of upfront work to setup the UDTF things are so much easier and there are so many use cases. Here is the source code for the UDTF:
https://www.scottklement.com/udtf/ifsDirArticle.html