Home > IT Answers > AS/400 > AS/400 to Excel
Help

Question

Asked:
Asked By:
Nov 10 2008   4:00 AM GMT
Tjgm88   380 pts.

AS/400 to Excel


OS/400, iSeries, Microsoft Excel, iSeries import/export, iSeries FTP

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.

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
-1
Click to Vote:
  •   0
  •  -1
Last Answered: Nov 10 2008  5:21 PM GMT  by Breinhart   45 pts.




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.')
  • AddThis Social Bookmark Button

Browse more Questions and Answers on AS/400 and Microsoft Windows.

Looking for relevant AS/400 Whitepapers? Visit the Search400.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

Tjgm88   380 pts.  |   Nov 10 2008  11:36PM GMT

Hi,

We are running on V5R4. The command CVTPFXLS does not seem to be on the system. Does this need special licensing?

GM

 

Tjgm88   380 pts.  |   Nov 11 2008  10:30PM GMT

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?

 

Graybeard52   1915 pts.  |   Nov 12 2008  12:38AM GMT

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.

 

Tjgm88   380 pts.  |   Nov 12 2008  3:49AM GMT

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.

 

Cwc   3660 pts.  |   Nov 12 2008  6:50PM GMT

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.

 

Slack400   975 pts.  |   Nov 13 2008  9:36PM GMT

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