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 🙂
Two changes to improve this slightly:
1. Adding column names by specifying ADDCOLNAM(*SQL) on the CPYTOIMPF command.
2. In order to include html code in the message body, adding CONTENT(*HTML) on the SNDSMTPEMM command. If nothing else, I add breaks in my message body by using . Bold, italics, underline, etc. are all useful too.
Agreed!!! It was a new IBM i Programmer that left a ticket asking how to send emails in CL, so I deliberately kept this as basic as possible.
Maybe *ding ding round 2* is to revisit this code adding some error handling, CSV formats, HTML and some other bells and whistles…. added to my future blog list
Unless *you* want to email me a sample and I will publish it 😉