Client Access Download To Excel - Excel Format? - IBM AS400

This is a discussion on Client Access Download To Excel - Excel Format? - IBM AS400 ; We have some files that are downloaded using Client Access (actually using RTOPCB) onto a network folder. In another Excel workbook we need to import the data from these (Data / Import in Excel). This works fine for some of ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: Client Access Download To Excel - Excel Format?

  1. Client Access Download To Excel - Excel Format?

    We have some files that are downloaded using Client Access (actually
    using RTOPCB) onto a network folder.
    In another Excel workbook we need to import the data from these
    (Data / Import in Excel).

    This works fine for some of the downloaded spreadsheets, Excel brings
    up the Select Table and then Import data windows, but for some of the
    other files it brings up Data Link Properties and thinks I am trying
    to link to an Access Database.

    I have no idea why this is happening, all of the files have been
    created in the same way using RTOPCB.

    Now if I open one of the files that I have problems with and Save it
    in Excel, I can import it ok, so I think it might have something to do
    with the version of Excel that the file is being crearted as (BIF8 in
    RTOPCB).

    I'm hoping someone out there might know the answer or what I can do to
    eliminate these problems.

    Note: this is being posted to both AS400 and Excel groups.

    Thanks in advance.


  2. Re: Client Access Download To Excel - Excel Format?

    I'd suggest you get the latest service pack for you version of iSeries
    Access from IBM site

    http://www-03.ibm.com/servers/eserve...cess/casp.html

    Have you considers simplifying the files transfer to CSV format? Then
    there's a lot less for Excel to get confused about.

    Best of Luck, Brad




  3. Re: Client Access Download To Excel - Excel Format?

    Thanks Brad,

    I will check the release level that we are on - it could be we are not
    on the latest.

    I considered CSV instead - I'm not sure what other issues this may
    bring though, apart from not having the column headings this is a good
    solution though.

    We have V5R2M0 SI07675 of Client Access loaded - I see the latest is
    SI23978 - it does look like there have been significant changes made.
    I will see if we can upgrade to a later service pack.

    Thanks again.


  4. Re: Client Access Download To Excel - Excel Format?

    poddys wrote:
    > We have some files that are downloaded using Client Access (actually
    > using RTOPCB) onto a network folder.
    > In another Excel workbook we need to import the data from these
    > (Data / Import in Excel).
    >
    > This works fine for some of the downloaded spreadsheets, Excel brings
    > up the Select Table and then Import data windows, but for some of the
    > other files it brings up Data Link Properties and thinks I am trying
    > to link to an Access Database.
    >
    > I have no idea why this is happening, all of the files have been
    > created in the same way using RTOPCB.
    >
    > Now if I open one of the files that I have problems with and Save it
    > in Excel, I can import it ok, so I think it might have something to do
    > with the version of Excel that the file is being crearted as (BIF8 in
    > RTOPCB).


    One of the formats for iSeries data transfer is
    directly to Excel spreadsheet. Why not use that
    instead of BIF8?

    I rarely use the iSeries Access file transfer any more.
    Instead, I use the Excel Import External Data
    function and create a database query (using MS Query)
    that is stored with the spreadsheet. I can then
    refresh the data in the spreadsheet by clicking Data +
    Refresh Data.


    >
    > I'm hoping someone out there might know the answer or what I can do to
    > eliminate these problems.
    >
    > Note: this is being posted to both AS400 and Excel groups.
    >
    > Thanks in advance.
    >


  5. Re: Client Access Download To Excel - Excel Format?

    I've never seen the option on the file transfer to output directly to
    Excel spreadsheet - must be a recent (well last 5 years!) enhancement.
    I haven't created MS Query's in Excel, seen the option though.
    You know given the number of files and what they are trying to achieve
    this just might be the better approach.

    The user wants to take the downloaded files and to pull them into one
    worksheet, 1 file per tab, and for this to be used as the source to
    link to for their reporting spreadsheets. They would then refresh
    the data in the master spreadsheet when necessary.

    Thanks for the idea - it has to be better!


  6. Re: Client Access Download To Excel - Excel Format?

    On Jun 7, 6:00 am, poddys wrote:
    > I've never seen the option on the file transfer to output directly to
    > Excel spreadsheet - must be a recent (well last 5 years!) enhancement.
    > I haven't created MS Query's in Excel, seen the option though.
    > You know given the number of files and what they are trying to achieve
    > this just might be the better approach.
    >
    > The user wants to take the downloaded files and to pull them into one
    > worksheet, 1 file per tab, and for this to be used as the source to
    > link to for their reporting spreadsheets. They would then refresh
    > the data in the master spreadsheet when necessary.
    >
    > Thanks for the idea - it has to be better!


    It's pretty straightforward. It is predicated on having the iSeries
    Access ODBC driver (or some other ODBC driver) configured to point to
    your iSeries.

    In Excel:

    1. Drop down the Data menu; click on Import External Data + New
    Database Query

    2. In the 'Choose Data Source' dialog, find your ODBC database
    definition, click on it and click OK.
    (or double-click on the connection definition); sign onto the
    iSeries
    if prompted.

    3. In the Query Wizard dialog, click Cancel; click Yes to the
    question "Do you
    want to continue..."

    4. In the Add Tables dialog, click Close

    5. Up on the MS Query menu button area, click SQL

    6. In SQL window, key in your SQL statement. Note: if your ODBC
    connection is configured to use *SYS naming, use '/' as the
    separator
    character; if you're using *SQL, use '.'

    7. Click the OK button. Click OK on the 'SQL query can't be
    represented graphically' warning.

    8. A preview of the result set will show in a MS Query grid. Click
    the 'X' to
    close the MS query and return the data to Excel (or, click File +
    Return
    Data to Excel).

    9. In the Import Data dialog, you can click Properties to deselect
    the
    field names as column headings (selected by default). There are
    some
    other useful options there as well. Also on the dialog, you can
    specify
    the row and column in which to begin placing the data. Click OK.

    10. Depending on the how long it takes your query to execute and how
    many rows it returns, you'll see the data in the spreadsheet in
    short order.

    You can specify a different query for different tabs. In fact, you
    can even specify multiple queries for the same tab, as long as they're
    not going to overlay each other.

    The real beauty of this is, once set up, your user can refresh the
    data himself. To do so, open the workbook, select the appropriate
    tab, position the cursor in one of the cells returned from a prior
    download, then click Data + Refresh Data. The data in the sheet will
    be replaced by the most current.


+ Reply to Thread