Have you ever wanted to extract some data out of an IBMi/AS400 file using SQL and then email that data to someone as an Excel Spreadsheet?
Now you can… all from one IBMi command without having to write any CL code to do it.
I added this new function to the PROJEX4i – Distribute Data command last night – so it can now take a bite of selected data using SQL and send it as a TXT, CSV, XML or PDF as EMAIL.
Huge time saver for me and has been on my wish-list for ages.
DSTDTA FILE(CSP20) DATA(*SQL) DATASQL('select * from CSP20 where CONO20 = ''??'' and PSTD20 between 1061001 and 1101022') METHOD(*EMAIL) TYPE(*XML) EMAIL(*prompt) INSERT(*LINK) SUBJECT('Here is the spreadsheet for data range 10/01/2010 thru 10/22/2010') IFSDIR(*USER) IFS('/home/nlitten/csp20spreadsheet[10-26-2010_08.34.06].csv') IFSDOC('CSP20 Spreadsheet') STAMP(*YES)
This captures the list of data selected by your SQL statement, converts that data in a spreadsheet and saves it in the IFS – then it convert the spreadsheet to the format you selected (plain TXT, PDF, XML, CSV) and emails it directly to the address you specified on the command using the IBM i SNDSMTPEMM command. The new version allows you to choose between using SNDSMTPEMM, MMAIL and SNDDST as your email delivery mechanism.
It’s a thing of beauty. Hope it helps somebody.
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
IBM i SQL – Using SUBST to insert data into a string
Funky IBM i Email Validation Program using SQL Regex
Simple email validation SQL RPG ILE program
Cleaning messy IBM i Integrated File System (IFS) file names
IBM i ACS 5250 EMULATOR FONT – and other ridiculous mumbo jumbo
Email every spool file in an output queue – EMLOUTQ for IBM i