Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to transfer a list from AS400 to Excel

Posted on 2007-10-09
6
Medium Priority
?
1,486 Views
Last Modified: 2012-05-05
A user needs to trasnfer a list from AS400 to Excel. I have the query name and the library name, Can anyone tell me how to do it? Do I need to create an ODBC in Windows? Is there any option in Excel that should be activated in order to catch the list? Your Help will be greatly appreciated.
0
Comment
Question by:Hunter24
6 Comments
 
LVL 2

Expert Comment

by:csimonds
ID: 20041561
Are you using IBM iSeries Access (Client Access, as some know it)?
0
 
LVL 7

Accepted Solution

by:
Bruce Cadiz earned 2000 total points
ID: 20041604
Hunter24,

here is a step by step using ODBC
http://cornellcollege.edu/information_technology/database/AS400Excel.PDF

Bruce
0
 
LVL 14

Expert Comment

by:daveslater
ID: 20041732
Hi
use WRKQRY to start query. take option 2 (change) and enter the query name and library

select the
"Select output type and output form "
and ensure the output to 3=database file.

IMPORTANT MAKE SURE DO DO NOT REPLACE A LIVE DATABASE FILE

then run the query on exit.

If you have client access installed there shoule be an excel plug-in. In excel use
Tools>>Add-Ins
in client access is not there use <Browse> then select folder
C:\program Files\IBM\Client Acess\shared\
and select cwbtfxla.xll
The  simply download it into excel.

Dave
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:jordibartrina
ID: 20041758
Hello,

First mode: You can use CA/400 for send a file directly to xls format, and automatize it with rtopcb.
Second: Export file to IFS with cpytoifs and retrieve it to your pc.
Last: You can use a tool named xprtf (download for free here: http://www.recursos-as400.com/fonts/dbf400.exe) this tools can import/export files from/to as/400 and obtain .txt, .csv and .dbf. The three types of files can be open in MS Excel.

HTH
0
 
LVL 27

Expert Comment

by:tliotta
ID: 20043329
Hunter24:

Please clarify if the "query" is expected to run each time before the download to Excel and if running the query should be part of your desired answer. Is the query used _only_ to create this list or is it also used elsewhere? Can you (or another developer) create queries as needed?

Tom
0
 
LVL 4

Expert Comment

by:jordibartrina
ID: 20043901
Hello again,

New suggestion: convert outfile of your query to a comma separated flat file in IFS. Then send it via FTP to a ftp server in your lan. Send a message to the user: "Transfer done. File ready"
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

810 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