AS/400 to Excel

995 pts.
Tags:
iSeries
iSeries FTP
iSeries import/export
Microsoft Excel
OS/400
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.

Answer Wiki

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

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.’)

Discuss This Question: 6  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
  • tjgm88
    Hi, We are running on V5R4. The command CVTPFXLS does not seem to be on the system. Does this need special licensing? GM
    995 pointsBadges:
    report
  • tjgm88
    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?
    995 pointsBadges:
    report
  • graybeard52
    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.
    3,115 pointsBadges:
    report
  • tjgm88
    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.
    995 pointsBadges:
    report
  • Cwc
    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.
    4,290 pointsBadges:
    report
  • slack400
    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
    2,705 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