Home > IT Answers > AS/400 > extract data from as400 to excel
Copinky
45 pts.
 extract data from as400 to excel
AS/400 File Extract, excel
I have CL and DB files and i need to extract the data from the files to excel sheet by writing an program.How can i do that?

Software/Hardware used:
ASKED: December 16, 2011  8:17 AM
UPDATED: March 17, 2012  6:19 AM
RELATED QUESTIONS
Answer Wiki:
Here is some code to send an email of a file in .csv format. It is a stripped down verion of a command I wrote. It will sent the complete file. I you only want to send selected records, create a file using some records selection process: LF, CPYF, QRY, SQL. etc <pre> PGM PARM(&pFileIn) DCL VAR(&pFileIn) TYPE(*CHAR) LEN(20) DCL VAR(&ToStrmf) TYPE(*CHAR) LEN(60) DCL VAR(&Doc) TYPE(*CHAR) LEN(14) DCL VAR(&Mail) TYPE(*CHAR) LEN(128) CRTPF qtemp/csv rcdlen(32000) MONMSG MSGID(CPF0000) CLRPFM FILE(QTEMP/CSV) MONMSG MSGID(CPF0000) /* Copy Data to Import File */ CPYTOIMPF FROMFILE(&pFIleIn) TOFILE(QTEMP/CSV) + MBROPT(*Add) RCDDLM(*EOR) /* Set all the variables */ CHGVAR VAR(&DOC) + VALUE((%sst(&pfileIn 1 8)) *TCAT '.csv') CHGVAR VAR(&ToStrmf) VALUE('/QDLS/csv/' *cat &DOC) /* Copy to Stream File */ CPYTOSTMF + FROMMBR('/QSYS.LIB/QTEMP.lib/csv.FILE/csv.MBR') + TOSTMF(&ToStrmF) STMFOPT(*REPLACE) STMFCODPAG(*PCASCII) /* Grant authority to the stream file */ CHGAUT OBJ(&TOSTRMF) USER(*PUBLIC) DTAAUT(*RWX) + OBJAUT(*ALL) CHGVAR VAR(&MAIL) VALUE('CharlieB@TheMembersGroup.com') /* Send the file by mail */ SNDDST TYPE(*DOC) TOINTNET((&MAIL)) DSTD('Requested + csv file') DOC(&DOC) + FLR(CSV) EndPgm</pre>
Last Wiki Answer Submitted:  December 16, 2011  2:55 pm  by  CharlieBrowne   32,945 pts.
All Answer Wiki Contributors:  CharlieBrowne   32,945 pts. , Teandy   5,830 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

We need more info.
1. The simplist transfer fromat is .CSV (comma delimited). It creates a spreadsheet with virtually no cell formating. Is that acceptable
2. Is this a one-time process?
3. Is the data required located in a single database table?
4. Will the data need to be changed before delivery? Totals, etc.
5. What AS/400 tools, programming languages, query tools are you faimilar with?
6. Do you want the process to be AS/400 centered or PC centered?

Phil

philpl1jb
 44,220 pts.
 

i need to extract the data from the files to excel sheet by writing an program.

Why is a program needed? Why not just have Excel go get the data?

Tom

TomLiotta
 108,360 pts.