Si te
sirve de algo, aqui te mando un comando que te convierte los archivos del 400 en
archivos .CSV y lo pone en el IFS ya despues lo puedes transferir por FTP
o puedes mapear el folder del IFS a tu pc y asi abrirlo desde Excel
directamente
Saludos
PD. El
archivo cpytoxls2 es un comando, renombralo como cpytoxls
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Ortega, Ricardo
Sent: Thursday, July 29, 2004 4:26 PM
To: Help400 Forum (E-mail)
Subject: Saben algo de Llamado a File Transfer desde 400 e importaci�n a ExcelHola amigos del Foro 400
Algunas vez cree un proceso que llama a una aplicaci�n en PC desde el emulador Client Access para al AS400
pero recuerdo que hay que crear un archivo.bat para que ese llame a los par�metros guardados que di en el file transfer,
saben posteriormente tengo que importar esos datos a Excel, por otra parte se que hay un plug-in para Excel en donde se conecta a la ODBC del 400 y lo extraigo directamente, obiamente se automatizar�a mas si salvo la consulta y la ejecuto con una macro despu�s, en fin esa es la idea.
Alguien tiene claro como se hace eso, deber�s agradecer�a si me dieran un Tip.
Saludos forum 400, saben con este forum me siento como si estuvieran en otra �rea de trabajo muy cerca de mi y eso vale mucho!
Ricardo Javier Ortega Espinosa
Analista Programador
Berol M�xico
Office: 011 52 55 5729-34-00 Ext. 3112
E-mail: [EMAIL PROTECTED]
THIS MESSAGE MAY CONTAIN INFORMATION THAT IS CONFIDENTIAL AND/OR PROTECTED BY LAW. IF THE READER OF THIS MESSAGE IS NOT THE INTENDED RECIPIENT, YOU ARE HEREBY NOTIFIED THAT ANY DISSEMINATION, DISTRIBUTION, COPYING OR COMMUNICATION OF THIS MESSAGE IS STRICTLY PROHIBITED. IF YOU HAVE RECEIVED THIS COMMUNICATION IN ERROR, PLEASE CONTACT THE SENDER IMMEDIATELY AND DELETE THE MESSAGE. PLEASE NOTE THAT ALTHOUGH WE WILL TAKE ALL COMMERCIALLY REASONABLE EFFORTS TO PREVENT VIRUSES FROM BEING TRANSMITTED FROM OUR SYSTEMS, IT IS THE RESPONSIBILITY OF THE RECIPIENT TO CHECK FOR AND PREVENT ADVERSE ACTION BY VIRUSES ON ITS OWN SYSTEMS .
| **************************************************************************************** Note: The information contained in this message may be privileged and confidential and thus protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. **************************************************************************************** |
/* PROGRAM : CPYTOXLS VERSION 2.00 */ /* SYSTEM : ISERIES V5RX */ /* AUTHOR : HERMAN VAN DER STAEY AUGUST 12, 2002 */ /* */ /* COPY DATABASE FILE TO EXCEL FORMAT */ /* AND INCLUDE (ALIAS) FIELD NAMES. */ /* */ /* THE FILE WILL BE COPIED TO CSV FORMAT (COMMA SEPARATED */ /* VALUES), WHICH CAN DIRECTLY BE IMPORTED IN EXCEL IF */ /* YOU GIVE THE FILENAME THE EXTENSION ".CSV" */ /* */ /* TO COMPILE : */ /* */ /* CRTCLPGM PGM(XXX/CPYTOXLS) SRCFILE(XXX/QCLSRC) */ /* */
CPYTOXLS: PGM PARM(&FILE &FROMMBR &TOFILE &TODIR +
&FIELDNAMES &ALIAS)
DCLF FILE(QSYS/QADSPFFD) /* FILE FIELD REFERENCE +
FILE */
DCL VAR(&FILE) TYPE(*CHAR) LEN(20)
DCL VAR(&FROMFILE) TYPE(*CHAR) LEN(10)
DCL VAR(&FROMLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&FROMMBR) TYPE(*CHAR) LEN(10)
DCL VAR(&TOFILE) TYPE(*CHAR) LEN(64)
DCL VAR(&TODIR) TYPE(*CHAR) LEN(128)
DCL VAR(&TOSTMF) TYPE(*CHAR) LEN(193)
DCL VAR(&MBROPT) TYPE(*CHAR) LEN(10)
DCL VAR(&MBROPT) TYPE(*CHAR) LEN(10)
DCL VAR(&NOT_FIRST) TYPE(*LGL) LEN(1) VALUE('0')
DCL VAR(&FIELDNAMES) TYPE(*LGL)
DCL VAR(&ALIAS) TYPE(*LGL)
DCL VAR(&ST) TYPE(*CHAR) LEN(1024)
DCL VAR(&COMMA) TYPE(*CHAR) LEN(1) VALUE(',')
DCL VAR(&DBLQUOTE) TYPE(*CHAR) LEN(1) VALUE('"') +
/* DOUBLE QUOTE */
CHGVAR VAR(&FROMFILE) VALUE(%SST(&FILE 1 10))
CHGVAR VAR(&FROMLIB) VALUE(%SST(&FILE 11 10))
CHGVAR VAR(&TOSTMF) VALUE(&TODIR *TCAT '/' *CAT +
&TOFILE)
IF COND(&FIELDNAMES) THEN(CHGVAR VAR(&MBROPT) +
VALUE(*ADD))
ELSE CMD(CHGVAR VAR(&MBROPT) VALUE(*REPLACE))
IF COND(&FIELDNAMES) THEN(DO) /* FIELDNAMES */
DSPFFD FILE(&FROMLIB/&FROMFILE) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/FIELDNAMES)
OVRDBF FILE(QADSPFFD) TOFILE(QTEMP/FIELDNAMES)
NEXT: RCVF
MONMSG MSGID(CPF0864) EXEC(GOTO CMDLBL(EOF))
IF COND(&NOT_FIRST) THEN(CHGVAR VAR(&ST) +
VALUE(&ST *TCAT &COMMA))
CHGVAR VAR(&NOT_FIRST) VALUE('1')
IF COND(&WHALIS *NE ' ' *AND &ALIAS) THEN(DO)
CHGVAR VAR(&ST) VALUE(&ST *TCAT &DBLQUOTE *CAT +
&WHALIS *TCAT &DBLQUOTE)
ENDDO
ELSE CMD(DO)
CHGVAR VAR(&ST) VALUE(&ST *TCAT &DBLQUOTE *CAT +
&WHFLDI *TCAT &DBLQUOTE)
ENDDO
GOTO CMDLBL(NEXT)
EOF: DLTF FILE(QTEMP/FIELDNAMES)
CRTPF FILE(QTEMP/PF1024) RCDLEN(1024)
MONMSG MSGID(CPF5813) EXEC(CLRPFM FILE(QTEMP/PF1024))
MONMSG MSGID(CPF7302)
OVRDBF FILE(PF1024) TOFILE(QTEMP/PF1024)
CALL PGM(WRTPF1024) PARM(&ST) /* CALL THE RPG +
PROGRAM */
CPYTOSTMF +
FROMMBR('/QSYS.LIB/QTEMP.LIB/PF1024.FILE/PF+
1024.MBR') TOSTMF(&TOSTMF) +
STMFOPT(*REPLACE) STMFCODPAG(*PCASCII) +
ENDLINFMT(*CRLF)
DLTF FILE(QTEMP/PF1024)
ENDDO /* FIELD NAMES */
CPYTOIMPF FROMFILE(&FROMLIB/&FROMFILE &FROMMBR) +
TOSTMF(&TOSTMF) MBROPT(&MBROPT) +
STMFCODPAG(*PCASCII) RCDDLM(*CRLF) +
DTAFMT(*DLM) STRDLM(&DBLQUOTE) +
FLDDLM(&COMMA) DECPNT(*PERIOD)
/* THE PARAMETER STMFCODPAG(*PCASCII) CAN BE ADDED */
/* ON THE CPYTOIMPF COMMAND STARTING FROM RELEASE V5R1. */
/* THE FILE IS USELESS FOR EXCEL IF NOT IN ASCII FORMAT. */
/* THE FLDDLM (FIELD DELIMITER) AND DECPNT (DECIMAL POINT) */
/* PARAMETERS MUST CORRESPOND WITH THE SETTINGS ON YOUR PC. */
/* CHECK VIA : */
/* START, CONTROL PANEL, REGIONAL SETTINGS, NUMBER */
/* AND VERIFY THE "DECIMAL SYMBOL" AND "LIST SEPARATOR" */
/* SETTINGS. */
/* F.E. IN BELGIUM YOU MUST CODE : */
/* FLDDLM(';') DECPNT(*COMMA) */
/* */
/* TO MAKE THE EXCEL FILE AVAILABLE TO YOUR PC : */
/* */
/* 1) YOU CAN FTP THE FILE IN THE IFS TO YOUR PC */
/* */
/* 2) YOU CAN SHARE THE DIRECTORY IN THE IFS VIA */
/* OPERATIONS NAVIGATOR. */
/* (CHECK THAT THE NETSERVER IS STARTED AND CONFIGURED.) */
/* ON THE PC YOU CAN MAP THE SHARED DIRECTORY TO A DRIVE */
/* LETTER. EXAMPLE : */
/* NET USE X: \\AS400NETSERVERNAME\SHARENAME */
END: ENDPGM
/* COMMAND : CPYTOXLS */
/* SYSTEM : ISERIES */
/* AUTHOR : HERMAN VAN DER STAEY */
/* COPY TO EXCEL FORMAT */
/* TO COMPILE : */
/* */
/* CRTCMD CMD(XXX/CPYTOXLS) PGM(XXX/CPYTOXLS) + */
/* SRCFILE(XXX/QCMDSRC) */
/* */
CPYTOXLS: CMD PROMPT('Copy to Excel Format')
PARM KWD(FILE) TYPE(FILENAME) PROMPT('File Name')
PARM KWD(MBR) TYPE(*NAME) LEN(10) DFT(*FIRST) +
SPCVAL((*FIRST)) PROMPT('Member Name')
PARM KWD(TOFILE) TYPE(*CHAR) LEN(64) +
DFT(MyFile.CSV) MIN(0) EXPR(*YES) +
CASE(*MIXED) PROMPT('IFS Filename + +
extension CSV')
PARM KWD(TODIR) TYPE(*PNAME) LEN(128) +
DFT('/MyDir') CASE(*MIXED) PROMPT('to IFS +
Directory')
PARM KWD(FIELDNAMES) TYPE(*LGL) DFT(*YES) +
SPCVAL((*YES '1') (*NO '0')) MIN(0) +
EXPR(*YES) CHOICE('*YES, *NO') +
PROMPT('Include Fieldnames')
PARM KWD(ALIAS) TYPE(*LGL) DFT(*YES) SPCVAL((*YES +
'1') (*NO '0')) MIN(0) EXPR(*YES) +
CHOICE('*YES, *NO') PROMPT('Use Alias +
Fieldnames')
FILENAME: QUAL TYPE(*NAME) LEN(10) MIN(1)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) +
SPCVAL((*CURLIB) (*LIBL)) PROMPT('Library')
H*****************************************************************
H*
H* PROGRAM : WRTPF1024
H*
H* ADD A RECORD TO FILE PF1024
H*
H*
H* TO COMPILE :
H*
H* CRTRPGPGM PGM(XXX/WRTPF1024) SRCFILE(XXX/QRPGSRC)
H*
H*****************************************************************
FPF1024 O F 1024 DISK A
IOUTREC DS
I 1 256 PART1
I 257 512 PART2
I 513 768 PART3
I 7691024 PART4
C *ENTRY PLIST
C PARM OUTREC
C WRITEPF1024 OUTREC
C MOVE *ON *INLR
