ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

AS400 - Data downloading to Excel from AS400 table with multiple members

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

  • AS400 - Data downloading to Excel from AS400 table with multiple members

    I recently started a job at a company that still uses AS400 and need some help!

    So currently we do not have any BI tool to access system data files directly, so I'm trying to connect AS400 to excel using ODBC and download/display AS400 data directly to excel.

    So I accomplished to make the connection and I am able to access schema, table, views from Excel using data connection. But the problem is that some tables have multiple members in it and the excel's pulling data from what I am guessing as the first member that excel sees when all the important data are in other member files...

    Is there anyway I could select specific members and download data of that specific member directly to excel?

    I can do this when I use IBM's data transfer program, but I would like to find out if it's possible to do directly with excel..

    Thanks!

    Matt

  • #2
    I haven't used Excel for a number of years, and it's been even longer since I've used multi-member database files; but no one else has commented yet. The two common methods that I'm aware of for accessing specific members are ALIASes and OVRDBFs.

    Executing a SQL CREATE ALIAS statement results in an ALIAS database object that you can query like you'd query the table. It's a permanent database ob ject with ownership and permissions. Any authorized user can query it. A SQL DROP ALIAS statement deletes the object.

    Executing OVRDBF (before querying the table) can point a query to a particular member. The override exists only within the job or activation group to which it's scoped. It's temporary, so it goes away automatically when the job or activation group ends. Otherwise, a DLTOVR can remove it.

    Either way, a user (or some function) needs to know which member and needs to select it. I suppose that a stored proc could accept a member name and set an override.
    Tom

    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

    Comment

    Working...
    X