extract data from as400 to excel

45 pts.
Tags:
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    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
    49,840 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following