Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

TechTip: Download Physical Files Using Microsoft Excel

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • TechTip: Download Physical Files Using Microsoft Excel

    ** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
    ** This thread discusses the Content article: TechTip: Download Physical Files Using Microsoft Excel0

  • #2
    TechTip: Download Physical Files Using Microsoft Excel

    ** 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

    Comment


    • #3
      TechTip: Download Physical Files Using Microsoft Excel

      ** 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

      Comment


      • #4
        TechTip: Download Physical Files Using Microsoft Excel

        ** 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.

        Comment


        • #5
          TechTip: Download Physical Files Using Microsoft Excel

          ** 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?

          Comment


          • #6
            TechTip: Download Physical Files Using Microsoft Excel

            ** 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.

            Comment


            • #7
              TechTip: Download Physical Files Using Microsoft Excel

              ** 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.

              Comment


              • #8
                TechTip: Download Physical Files Using Microsoft Excel

                ** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
                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

                Comment


                • #9
                  TechTip: Download Physical Files Using Microsoft Excel

                  ** 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

                  Comment


                  • #10
                    TechTip: Download Physical Files Using Microsoft Excel

                    ** 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

                    Comment


                    • #11
                      TechTip: Download Physical Files Using Microsoft Excel

                      ** 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 ?

                      Comment


                      • #12
                        TechTip: Download Physical Files Using Microsoft Excel

                        ** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
                        While in Excel, open TOOLS, ADD-Ins. When the list of Add-ins appears there should be an option to "Add" an Add-in, select that, and a file-open box should appear. Select the cwbrfxla file, and then the add-in should appear within Excel. Dave

                        Comment


                        • #13
                          TechTip: Download Physical Files Using Microsoft Excel

                          ** 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.

                          Comment


                          • #14
                            TechTip: Download Physical Files Using Microsoft Excel

                            ** This thread discusses the article: TechTip: Download Physical Files Using Microsoft Excel **
                            I have a VB program that opens an Excel workbook and then refresh the data. It works if I run the VB program, but when I create an .exe file and run it from the AS400 using STRPCCMMD, the refresh doesn't work. What can I do to make sure the refresh data is done before it executes the next line of code? Winnie

                            Comment

                            Working...
                            X