AS/400 to Excel
Hi Someone,
I am using FTP to send a data file fromthe iseries to the server as a .txt file usint put statement.
Can anyone suggest how I would sent the file direct to an Excel spreadsheet, preferable to a pre-formatted sheet.
Thanks.
Software/Hardware used:
Software/Hardware used:
ASKED:
November 10, 2008 4:00 AM
UPDATED:
November 13, 2008 9:36 PM
Answer Wiki:
You can create a CL program and convert the physical file to Excel with the following command and also send it as an email attachment if needed.
CVTPFXLS FROMFILE(*LIBL/filename) +
TOSTMF('/folder name on AS400/filename.xls') FLDHDR(*TEXT)
SNDMAIL RECIPADDR(email@company.com) RECIPNAME('the person') +
SENDERADDR(you@company.com) SENDERNAME('you') +
ATTACHMENT('/folder name on AS400/filename.xls') +
SUBJECT('Whatever') MESSAGE('Please review the File.')
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
We are running on V5R4. The command CVTPFXLS does not seem to be on the system. Does this need special licensing?
GM
I have managed to down load the software for CVTPFXLS from the web and activated the same. However, after issuing the command get a successful messge but cannot see the resultant excel file in the IFS using WRKFLR command. Don’t wuite understand whats happening here. Can someone help please?
Normally you write the xls file to the IFS. I suggest NOT using QDLS. Check the path you specified for the file and use Navigator or Windows share to view the file.
You may want to post the command line so we can see where the file is being directed. It is both important and sometimes tricky to get the path specified correctly if you are not used to using the IFS.
Hi Friend,
This the command line and the response suggesting successful conversion. But where is the file placed? I do have the path “\gopal\excel”. Thanks for your assistance.
CVTPFXLS FROMFILE(ASMDEV/QSYSPRT) TOSTMF(‘\gopal\excel\qsysprt.xls’) FLDH
DR(*TEXT)
Ownership of object PFXLSSPC in QTEMP type *USRSPC changed.
19 records converted.
Excel spreadsheet in \gopal\excel\qsysprt.xls.
Physical file QSYSPRT in library ASMDEV converted to Excel Format.
In addition to viewing the file through iSeries Navigator or Windows Explorer, you can also use the WRKLNK command to get to your file. This shows you all the directories in the IFS that you have authority to. From there, you should be able to see the /gopal directory, and drill down into its subdirectories accordingly to view your file.
For more clarification about the IFS:
The WRKFLR command only sees the folders/directories contained in the /QDLS directory, which is just a part of the IFS (Integrated File System). This is why you can’t see your file through the WRKFLR command. /QDLS is the file system for Document Library Services, and as Graybeard recommended, it is better to not use it for what you’re doing, as it has some limitations, such as only supporting the old DOS naming convention (no long file names). Its use has been deprecated by the greater flexibility of the IFS as a whole. Through the IFS, any directory or library on the System i can be viewed.
In addition to the software you downloaded, you can also use the native CPYTOIMPF, CPYFRMIMPF, CPYTOSTMF, and CPYFRMSTMF commands to copy to and from files in the IFS.
The AS400 can do this natively as Cwc describes.
Use the CpyToImpF command and convert the file to a delimited file.
Use CSV it’s a standard format and will import into Excel just fine.
Or if this is on demand the user can use the Client Access file download feature. There’s a plugin for Excel so they have a download and upload button in excel.
Link