Email an IBM i File as a CSV

Sample IBM-i Control Language Program to take a physical file (aka SQL Table) convert it to a CSV Spreadsheet and email to someone… somewhere..

First let’s create a nice little CMD wrapper to give us a smarter way of calling our CL code:

/* --------------------------------------------------------- */
/*                                                           */
/* EMLCSVFILE *CMD - this calls EMLCSVFILE *CLLE             */
/*                                                           */
/*     CRTCMD CMD(LITTENN/EMLCSVFILE)                        */
/*            PGM(LITTENN/EMLCSVFILE)                        */
/*            SRCFILE(LITTENN/QCMDSRC)                       */
/*            SRCMBR(EMLCSVFILE)                             */
/*                                                           */
/* --------------------------------------------------------- */
/* Modification History                                      */
/* NJL01 Nick Litten 04/01/2022 Created                      */
/* --------------------------------------------------------- */
 CMD        PROMPT('Email DB2 file as CSV')

 PARM       KWD(FILE) TYPE(PF_FILE) MIN(1) PROMPT('File Name')
 PF_FILE: QUAL TYPE(*NAME) LEN(10) MIN(1) EXPR(*YES)
 QUAL       TYPE(*NAME) LEN(10) DFT(*LIBL) SPCVAL((*LIBL *LIBL) (*CURLIB +
                *CURLIB)) PROMPT('Library')

 PARM       KWD(EML) TYPE(*CHAR) LEN(255) MIN(1) PROMPT('Email Address')

 PARM       KWD(SUBJECT) TYPE(*CHAR) LEN(100) MIN(1) PROMPT('Email Subject')

 PARM       KWD(BODY) TYPE(*CHAR) LEN(400) MIN(1) PROMPT('Email Body') 

and the executing CL looks like this:

/* --------------------------------------------------------- */
/*                                                           */
/* EMLCSVFILE *CLLE - called by EMLCSVFILE *CMD              */
/*                                                           */
/* This is a very simple example Control Language Program    */
/* that will recieve a file, email address, subject and body */
/* and attach the file (as a CSV spreasdheet) and email it   */
/*                                                           */
/* NOTE: Minimal error handling to show simple code example  */
/*                                                           */
/*       CRTBNDCL  PGM(EMLCSVFILE)                           */
/*                 SRCFILE(LITTENN/QCLLESRC)                 */
/*                 SRCMBR(EMLCSVFILE)                        */
/*                                                           */
/* --------------------------------------------------------- */
/* Modification History                                      */
/* NJL01 Nick Litten 04/01/2022 Created                      */
/* --------------------------------------------------------- */
 PGM        PARM(&DATAPARM &EML &SUBJECT &BODY)

 DCL        VAR(&DATAPARM) TYPE(*CHAR) LEN(20)
 DCL        VAR(&DATAFILE) TYPE(*CHAR) LEN(10)
 DCL        VAR(&DATALIB) TYPE(*CHAR) LEN(10)
 DCL        VAR(&EML) TYPE(*CHAR) LEN(255)
 DCL        VAR(&SUBJECT) TYPE(*CHAR) LEN(100)
 DCL        VAR(&BODY) TYPE(*CHAR) LEN(400)

 DCL        VAR(&USER) TYPE(*CHAR) LEN(10)
 DCL        VAR(&IFS) TYPE(*CHAR) LEN(255)
 DCL        VAR(&STMF) TYPE(*CHAR) LEN(255)

 /* get the user name running the command */
 RTVJOBA    USER(&USER)

/* break file and lib out of the incoming parm */
 CHGVAR     VAR(&DATAFILE) VALUE(&DATAPARM)
 CHGVAR     VAR(&DATALIB) VALUE(%SUBSTRING(&DATAPARM 11 10))

/* construct the working IFS folder name for this user */
 CHGVAR     VAR(&IFS) VALUE('/home/' *CAT %TRIM(&USER) *CAT '/')

/* create the folder and ignore if it already exists */
 CRTDIR     DIR(&IFS)
 MONMSG     MSGID(CPFA0A0) /* already exists */

 CHGVAR     VAR(&STMF) VALUE(%TRIM(&IFS) *CAT %TRIM(&DATAFILE) *CAT '.csv')

 CPYTOIMPF  FROMFILE(&DATALIB/&DATAFILE) TOSTMF(&STMF) MBROPT(*REPLACE) +
                FROMCCSID(*FILE) STMFCCSID(*PCASCII) RCDDLM(*CRLF) +
                DTAFMT(*DLM) STRDLM(*DBLQUOTE)

 SNDSMTPEMM RCP((&EML)) SUBJECT(&SUBJECT) NOTE(&BODY) ATTACH((&STMF))

 /* some housekeeping */
 RMVLNK     OBJLNK(&STMF)

 RETURN

 ENDPGM:
 ENDPGM 

now that’s it!

Compile them both (obviously changing the library name from LITTENN to your library) and run it like this:

You should receive an email that looks something like this:

If you want to go through a code walk through with me waffling about every single line and some coding alternatives, then check out this 🙂