(FAQ) How to Automate AS400 File to Excel

When attempting to automate the downloads to Excel, I ran into a problem with using .csv because many of the fields contain imbedded commas. I wanted the user to be able to just click on the file and have all the data formatted. The answer is to copy to a tab-delimited import file first, then copy to a stream file making sure to use the .xls file extension.


CPYTOIMPF FROMFILE(&LIB/&FILE) TOFILE(QTEMP/TEMPXLS) +
MBROPT(*ADD) RCDDLM(X'0D') DTAFMT(*DLM) +
STRDLM(*NONE) FLDDLM(X'05') DATFMT(*USA) +
TIMFMT(*USA)

Hex '05' translates to a tab. And then all I had to do was copy to a file with an .xls extension:

CPYTOSTMF +
FROMMBR('/QSYS.LIB/QTEMP.LIB/TEMPXLS.FILE/TEMPXLS.MBR') +
TOSTMF(&STMF) +
STMFOPT(*REPLACE) CVTDTA(*AUTO) +
DBFCCSID(37) STMFCODPAG(850) ENDLINFMT(*LF)

Where &STMF = 'q:homedownloadmyfile.xls'