TechTip: Download Physical Files Using Microsoft Excel

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
Why not use EXCEL query vs. transfering a whole physical file? Joins and data selections make the process very easy

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
Correct me if I'm wrong, but isn't Excel similar to Access in that the query will ALWAYS be performed on the client machine? If so, that means all of the data will be retrieved but only the selected data will be presented. chuck Opinions expressed are not necessarily those of my employer. "finncoiv" wrote in message news:6b16f7c6.0@WebX.WawyahGHajS... > Why not use EXCEL query vs. transfering a whole physical file? > Joins and data selections make the process very easy

[P.Weyer]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
Yes you can use Excel Query. It is a very powerful tool. However, it can also create a lot of overhead on the iSeries if the query needs to build an access path. The intent of the article is to provide the basics of downloading data via Excel.

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
When using the Browse button, how is the library list determined? All we see currently is QGPL. Do we need to change something in iSeries Navigator or somewhere else?

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
The article says, "There is a limitation of a maximum of 65,535 records that can be transferred from an iSeries physical file to an Excel spreadsheet". I'm not sure what release it started in, but i think that at least as of V4R5, when there is more than 65,535 records returned, additional worksheets will be added to accomodate the overflow.

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
Actually, it turns out that if you specify a data transfer from within Excel, you cannot create a library list prior to browsing. You can however type in a library name eg MYLIB and then click to browse, which will reflect the library you just entered. Furthermore, it doesn't appear to accept comma delimeted lists of Libraries unless you fully qualify LIB/FILE. An alternative would be to create the transfer request definition via Client Access Transfer-->Receive File From Host. This not only allows you to define a transfer request which can be used with Exel, but also allows defining of a true query.

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
David, It's still the limit in Office 2003. chuck Opinions expressed are not necessarily those of my employer. "David Abramowitz" wrote in message news:6b16f7c6.7@WebX.WawyahGHajS... > AFAIK, the 65535 limitation is an Excel limitation in many versions, not an iseries limitation. > > To get around it, download to a CSV, and then bring the data into MS Access. > > Dave

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
Chuck Ackerman wrote: > Correct me if I'm wrong, but isn't Excel similar to Access in that > the query will ALWAYS be performed on the client machine? If so, > that means all of the data will be retrieved but only the selected > data will be presented. Nope, the data selection is done on the 400. You can even prove this to yourself by running a query and looking at the open files for that session. Bill

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
I'm using Excel 2002 and there is no option for data transfer from the iSeries. I can find the file cwbtfxla. But nothing happens when I open it. Unfer the "Data" menu option in Excel there is an "Import External Data" option but nothing in there indicates an option to x-fer from iSeries. Where am I going wrong ?

[Guest.Visitor]

** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
I just don't know my Excel. Thanks I have it sorted now.