?
Solved

How to convert an AS400 physical file to EXCEL using STRPCCMD

Posted on 2003-12-11
10
Medium Priority
?
11,361 Views
Last Modified: 2007-12-19
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...

gcasavantes@usa.net
0
Comment
Question by:gcasavantes
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 6

Accepted Solution

by:
dedy_djajapermana earned 336 total points
ID: 9926106
0
 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 332 total points
ID: 9927066
Hi
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 */
STRPCO
/* 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')

Rgs

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 9927118
Ps
instead of using the full path name I find it easier to create a short cut on to c:\
then
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)

Dave
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 17

Assisted Solution

by:theo kouwenhoven
theo kouwenhoven earned 332 total points
ID: 9940842
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.

Regards,
Murph

0
 
LVL 17

Expert Comment

by:theo kouwenhoven
ID: 10062977
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 http://www.geocities.com/~alex_nubla/zip/cvtpfxls.zip
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
0
 
LVL 1

Expert Comment

by:SuperCCA
ID: 10494095
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.
0
 
LVL 17

Expert Comment

by:theo kouwenhoven
ID: 10494332
Great thinking SuperCCA,

Thats tha same as the other 3 experts already have written
0
 
LVL 1

Expert Comment

by:SuperCCA
ID: 10494424
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..
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Media Temple is proud to announce our partnership with the Society of Digital Agencies (SoDA) as their exclusive hosting partner.
Welcome back to our beginners guide of the popular Unix tool, cron. If you missed part one where we introduced this tool, the link is below. We left off learning how to build a simple script to schedule automatic back ups. Now, we’ll learn how to se…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question