DownLoad File to Excel from batch Job

[tpayne]

We have a program that creates a file which is currently being downloaded in Excel format by our users, however MIS wants to take the ability to download files away from them.

They are suggesting instead that the job FTP the file down to the network as a flat ASCII file and that the user creates a macro to import this into Excel.

Seems very cumbersome to me, when there are already tools out there that will convert a database file into Excel, but I don't know of any that will do this automagically.

Does anyone know of a command/tool we could use to create an Excel file from a batch job? This needs to also be created on the network, since MIS does not want the users to have access to the Integrated File System.

Thanks in advance.

 

[scott_parker]

Actually,
If you have your IS dept just create an ASCII CSV file directly from the AS/400 you can automatically open that up in Excel with no problems. No importing or anything. Just make sure the CSV extension is associated with Excel. (Most computers it already is by default when you load MS Office.)

After you open in excel and review/make changes it will prompt you to change the file type when you save.
It is really simple.


Scott Parker
Grote Industries, LLC
mailto:[email protected]





Scott Parker
Grote Industries, LLC.
WorldSoftware Version 8.1.2 AS/400 V5R1

 

[dale_draper]

Just my opinion, but your IT dept. needs an attitude adjustment. At V4R4 or
higher the IFS is fully securable so that they could deny everything but a
single folder.

Bottom line, you should not be the one asking this question, if they want to
take a needed functionality away from the users it is their job the replace
it. If they cannot, they should not be allowed to pull back this
functionality.

Fear and the "don't allow" is too often the fallback position of a lazy IT
manager. And unfortunately, it is hard to argue effectively against a fear
monger.

BTW, if you have query users, they are bypassing security already. ;0

Hope you don't lose this one

Just my opinion,

Dale
a7.3c9
v4r4

 

[Hal_McGee]

Tony,
We use ODBC and MS Access and Excel to get data from the AS/400. Within
Excel you would use MS Query to set up an ODBC query to the AS/400 to
retrieve. data. Seeing as it appears from the underlying context of your
message that your IT group is more than somewhat paranoid and may have a
few control issues to deal with, I would recommend that you set up a user
with a permanent password that has strict read only access to the AS/400
and a limited set of libraries. Since JDE uses SQL any functionality you
may have regarding Add/Change/Delete changes to Insert/Update/Delete in SQL
and works on production tables if you are not careful. Generally speaking
any user who understands enough to set this up also understands enough to
know better than to update production tables in a commercial product,
unless they have a bad case of ego. Also restrict them to only the
libraries they need access to as well. We have been using MS Access and
Excel against the AS/400 for some time and no one has abused it yet.
Access beats the tar out of World Writer as far as reporting capabilities.
There is a good book on using Office with the AS/400 that has very clear
simple examples that I recommend, The AS/400 and Microsoft Office
Integration Handbook. The reviews on Amazon will tell you that this is a
very simple book and is disappointing if you are trying to learn new
techniques. If you are absolutely starting from scratch, it saves you a lot
of confusion and holds your hand. I would recommend getting a used copy
instead of new as it is not worth the cover price.
You can drive Access using Visual Basic and use Task Scheduler to launch
Access at certain times as well. There have also been threads about using
PCCMD to send a string to a PC in which Access or any other program is
started. Unfortunately, with Excel, the only similar feature is to have a
macro run on open. I use Access to drive Excel so that each time I open
Excel, it doesn't have to repeat the whole process.
Good Luck!
Hal

Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC

 

[ame]

What version is this...



They are suggesting instead that the job FTP the file down to the network as
a flat ASCII file and that the user creates a macro to import this into
Excel.

Seems very cumbersome to me, when there are already tools out there that
will convert a database file into Excel, but I don't know of any that will
do this automagically.

Does anyone know of a command/tool we could use to create an Excel file from
a batch job? This needs to also be created on the network, since MIS does
not want the users to have access to the Integrated File System.

Thanks in advance.

 

[cbower]

Tony,

We have a couple of applications that automatically FTP a file (thru a batch
job) from the AS/400 to a server. I can provide the scripts and CL from one
of the apps for you to review and use as an example...

Of course, you could also create the file as a comma delimited output format
(on the AS/400), then it would be easily opened in Excel after it arrives on
the server.

Chuck



Chuck Bower
VP of IS
Coachmen Industries, Inc.
A73C8

 

[tpayne51]

Hi Chuck,

Thanks for the info.
I'm not aware of a way to create a comma delimited file on the AS400, except
by doing it manually in an RPG program - guess that would work, but it's a
bit of an effort to create the file that way.

One thing I developed that might be helpful if you are creating your own DW
driven programs is a generic CL program for DW. The program name and
OPNQRYF names are stored in variables and used throughout the CL, the
OPNQRYF and Printer Overrides are put together and only executed if the
report name or file name variables are not blank.
Pretty much all you need to do when creating the CL for a new DW program is
to copy a previous one and change the values for 2 variables. Takes a lot
of the effort out of it...

See you tomorrow for lunch.
Tony

 

[slandess]

----- Original Message -----
From: "dale_draper" <[email protected]>

or


Well said, Dale...I couldn't agree more.



Steve Landess
V4R4 A7.3 cume9
EDI
(512) 423-0935

 

[dale_draper]

Hey Tony, it's easy to create a CSV file. You still havent' told us your OS
level, which makes a difference, believe it or not.


Dale
v4r4

 

[tpayne51]

Sorry - it's V4R5.
A CSV file would be ideal - it's almost as good as having an Excel file -
saves that manual column splitting.

 

[dale_draper]

Off the top of my head the command is CPYTOSTMF or CPYTOIMPF which will
create a .csv file in the IFS, which can then be FTP'd.

We are doing exactly that for presenting info to the web but are using an
older 2 step version, the new OS will allow the conversion to be done in one
step easily.

Here's a sample from someone on Midrange-l: (you need PTF SF62142)

CPYTOIMPF FROMFILE(WHPMAS)
TOSTMF('\QDLS\MAKINS\TEST.CSV')
RCDDLM(*CR)
DTAFMT(*DLM)
STRDLM('"')
FLDDLM(',')


Dale
a7.3c9
v4r4

 

[dr2]

Tony,


OK, it's actually base operating system. They can do it with a few simple
CL line of code.

Tell them to look at the CPYTOIMPF command. It's base operating system.
This will take a AS/400 physical file and convert it to a .csv. We use
this all the time for helping people either download files or for
converting FROM as AS/400 (something we're doing alot of nowadays).

This kind of AS/400 question can easily be answered from the midrange-l
listserver archives at www.midrange.com....and it's free.

-----

On Thu, 12 Sep 2002, tpayne51 wrote: