How to convert an AS400 physical file to EXCEL using STRPCCMD

Is there a way to transfer a PF from the AS400 to an existing or new EXCEL spread sheet on my PC using CLIENT ACCESS and the STRPCCMD command...?

At the time I am using the STRPCCMD in a CLP to open an existing EXCEL spread sheet but I need it to be refreshed from the PF before every user access...  Thanks in advance...
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you want it to run as part of the CLP you can
create a file transfer, using the properties tag to
Close when finished, Run automatically, omit display transfer completion mesasge
the transfer request extracts the as400 file and puts it on to the c drive. then launch excel to read the file

/* strta PC organiser */
/* download to user pc using previous request */
STRPCCMD PCCMD('Progra~1\IBM\Client~1\cwbtf.exe C:\tfr.TFR') PAUSE(*NO)  
/* Launch excel with downloaded file */
STRPCCMD PCCMD('C:\Progra~1\Micros~2\Office\excel.exe C:\file.XLS')


instead of using the full path name I find it easier to create a short cut on to c:\
STRPCCMD PCCMD('C:\excel.lnk C:\TEMP\DB1.XLS') pause(*no)
and do the same with the transfer request
STRPCCMD PCCMD('C:\cwbtf.lnk C:\d1.tfr') pause(*no)

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Theo KouwenhovenApplication ConsultantCommented:
If you have installed the total Client Access application, you can directly retrieve AS/400 files from your as/400 to excel with the excel "transfer data from AS/400 option"

This option you can find in excel by menu option "data" (you also see here the "to AS/400 transfer")
If it is not in the datamenu, you can look if the add-in is available by selecting in excel:
     => Tools => Add-ins => (select Client Access Data transfer and click OK)

If you choose now "data" and the transfer option, you can not only download the data, but also select the required fields and records by some SQL windows.


Theo KouwenhovenApplication ConsultantCommented:
Hi gcasavantes

The AS/400 has an standard command to create import files CPYTOIMPF, this will make a importfile the way you like Fixed or CSV quoted or non quoted etc.

Or download a free tool

Click this link
This is a free tool that will:
Extract the physical file data into an IFS directory as ".xls" file instead of ".csv". This is faster than CPYTOIMPF, plus the generated Excel file takes up lesser space in the IFS directory. This tool handles the new advance data type like Floating, Real, Double, VarChar, Integer, Small Integer and Big Integer. Use this tool in conjunction with Send Mail (SNDMAIL) utility and you can automate the data extraction and send the Excel attachment by email. If you desire to send the Excel file into your network drive, try using FTP File (FTPFILE) utility.

Have Fun
CPYTOSTMF would get the data to a network drive and STRPCCMD could then be used to start up Excel on your PC and immediately access this file. This approach will give you the greatest control and takes advantage of the 32 bit drive support in OS/400. You will need to insert .csv formatting characters yourself.
Theo KouwenhovenApplication ConsultantCommented:
Great thinking SuperCCA,

Thats tha same as the other 3 experts already have written
Err, no it's not.
In some ways it is poorer solution for this specific problem as you must work harder yourself but it does give you greater possibiloities for a more generalised solution..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.