(Q&A) How To Convert AS/400 File To Excel Compatible Format

I just want to ask on how can i go about transfering a flat file to an excel file, which uses comma (,) as a delimeter. And how about if it's from a physical file and i want to transfer it to an excel file? Can you also provide a link on how to do it or maybe you can give me a step by step sample program.

(From : kNOw_wELL)

For the first question you can use FTP to transfer your flat file from AS/400 to your desktop. Use your desktop's dos shell to manually FTP your file. You can use the following commands:
a. FTP (AS/400 IP Address)
b. Enter AS/400 Username
c. Password AS/400 Password
d. Binary
e. Get MYLIB/MYFLATFILE C:\MYFILE.CSV
f. Quit

Now if you want to transfer your PF to your desktop, you need to convert your PF first into a delimeted flatfile format. You can concatenate (,) or (X'05') delimeter inbetween fields then write it in a flatfile. Or you can use CPYTOIMPF to automatically convert your PF into delimeted flatfile format.

Sample:
CPYTOIMPF FROMFILE(MYLIB/MYPF) TOFILE(MYLIB/MYFLATFILE) MBROPT(*ADD) STRDLM(*NONE) FLDDLM(&HEX)

Where &Hex contains hexadecimal constant X'05'.

After converting your PF into flatfile you can now use FTP.
Note:
- For comma delimeter, use .CSV as your file extension format.
- For X'05' delimeter, use .XLS as your file extension format.

For batch FTP, first create a simple CL program. Let's call ours FTPSCRIPT. In it we'll
have these lines:
PGM PARM(&RMTSYSTEM)
DCL VAR(&RMTSYSTEM) TYPE(*CHAR) LEN(200)
OVRDBF FILE(INPUT) TOFILE(MYLIB/FTPSCRIPT)
OVRDBF FILE(OUTPUT) TOFILE(MYLIB/FTPOUTPUT)
FTP RMTSYS(&RMTSYSTEM)
DLTOVR FILE(INPUT)
DLTOVR FILE(OUTPUT)
ENDPGM
The file MYLIB/FTPSCRIPT should be a one field file. The record length is pretty flexible.
The file MYLIB/FTPOUTPUT should also be a one field file. Again, the record length is pretty flexible. In the file MYLIB/FTPSCRIPT put the following:

user myuserid mypassword
cd mylib
quote rcmd crtpf mylib/myfile rcdlen(80)
put myfile.txt myfile
quote rcmd runpost
quit

Now when you type CALL FTPSCRIPT it will run the CL program FTPSCRIPT, which will use the FTPSCRIPT file. You can use DSPPFM/RUNQRY or some other utility to check the FTPOUTPUT file for the status of the transmission.