I did try to change formats for the columns involved but this did not work. It did not work and I assume that this is because of the formats in the downloaded files. I tried to use the Vlookup function in Report 1 Column D to lookup the matching Invoice # in report 2 and bring in the corresponding Amt paid. Report 1 is downloaded from SAP into Excel and Report 2 is downloaded from AS400 into Excel. MSG(‘Body’) DOC(filename.CSV) FLR(FDOCRPT.VLookup In Reports Downloaded From SAP & AS400 Nov 29, 2006 Step-3-EmailSubject heading for the DocumentĬHGDOCD DOC(filename.CSV) FLR(FDOCRPT.TPC) DOCD(‘Subject-Test Report’) or generate a report with minimum heading.ĬPYTOIMPF FROMFILE(LIBRARY/FMSPOOLF)TOSTMF(‘QDLS/FDOCRPT.TPC/filename.CSV’) MBROPT(*REPLACE) STMFCODPAG(437) RCDDLM(*CRLF) STRDLM(‘”‘) FLDDLM(‘,’) So use a query or a rpgoram to exclude certain lines from the spool file before if needed. It may require to exclude headings and certain heading. Step-2- copy the flat or dds file into CSV. Need to capture jobname, worstation and number in the CLĬPYSPLF FILE(QPQUPRFIL) TOFILE(LIBRARY/FMSPOOLF) JOB(244404/USERIF/SFLDPCW1) SPLNBR(2) MBROPT(*REPLACE) CTLCHAR(*PRTCTL) Pick up a a specific user job, and spool number.

Step1- copy spool file to a DDS file or a flat file then email.Ĭopy a spool file -default last job from a QĬPYSPLF FILE(QPQUPRFIL) TOFILE(LIBRARY/FMSPOOLF) JOB(*) SPLNBR(*LAST) MBROPT(*REPLACE) CTLCHAR(*PRTCTL) MSG(‘Body’) DOC(filename.CSV) FLR(FDOCRPT.TPC) CSVĬPYTOIMPF FROMFILE(LIBRARY/TEST.ITH) TOSTMF(‘QDLS/FDOCRPT.TPC/TST.TXT’) TEST.ITHHD is a text file TXT in the QDDSSRC/RPCUSR library. If you like to add a different meaningful heading, then follow the below stepsĬPYTOIMPF FROMFILE(LIBRARY/QDDSSRC TEST.ITHHD) TOSTMF(‘QDLS/FDOCRPT.TPC/TEST.TXT’) MBROPT(*REPLACE) STMFCODPAG(437) RCDDLM(*CRLF) DTAFMT(*FIXED) The above command will just include the field name in the excel as a coloumn heading.

*QDLS/FDOCRPT is a default IFS file folder-TRy WRKLNK to navigate. (*REPLACE) STMFCODPAG(437) RCDDLM(*CRLF) STRDLM(‘”‘) FLDDLM(‘,’) ADDCOLNAM(*SYS) Follow the parameters for excel.ĬPYTOIMPF FROMFILE(Library/Filename) TOSTMF(‘QDLS/FDOCRPT.TPC/filename.CSV’) MBROPT Step 1-copy the file to the ifs folder as a. You have two options to email reports from AS400 system to any user in an excel format.ġ- Email a database file-This is a raw data from a file and email as an excel sheet to external usersĢ-Email a spool file report- Convert the spool file report into excel and email.

As400 report to excel