|
|
Platform: Excel 2003 on Win XP Pro, sp2
I'm an Oracle DBA needing some help with importing external data into
Excel.
Reporting requirement dictated a rather complex query against the
Oracle db, joining 6 tables. I wrote the query and have it working
fine. Now I need to package it into an Excel worksheet so that the
end-user can easily import the results on demand to perform some what-
if scenarios.
Steps taken:
Save the developed query as c:\temp\myquery.sql
Data --> Import External Data --> New Database Query
At the "Choose Data Source" dialog, I select the Queries tab, then
select "Browse"
Drop-down list for "Files of type:" only lists "Data Sources"
Browse to c:\temp\ and begin entering file name of myquery.sql; the
file shows in the filtered list, so I select it and click "open"
Returning to the "Choose Data Source" dialog, I leave "Use the query
wizard to create ...." checked, and click "open". Get "Microsoft
Query could not read this file"
Hmm. searched help, googled a lot of KB articles. I gather that
eventually I'm supposed to end up with my query in a .qry file in C:
\Documents and Settings\<userid>\Application Data\Microsoft\Queries,
and that file will have not only my big SELECT statement, but also
information about which ODBC dsn to use, etc. But how do I get to
that point? I actually got to a point where I was able to do a 'save
as' and saved such a file, but can't seem to reproduce the steps, plus
whenever I select that file:
Data --> Import External Data --> New Database Query
At the "Choose Data Source" dialog, I select the Queries tab, select
the saved query from the list, I get a pop-up saying the query cannot
be edited. Click ok, and get my result set in Microsoft Query, from
where I can
File ==> return data to Microsoft Excel
All of which seems rather clumsy, and now that I need to make a
change to the original SELECT statement, I don't seem to be able to
get the new version in.
|
|
On Mon, 11 Jun 2007 13:05:54 -0700, EdStevens <quetico_man[ at ]yahoo.com> wrote:
[Quoted Text] >Platform: Excel 2003 on Win XP Pro, sp2
>
>I'm an Oracle DBA needing some help with importing external data into
>Excel.
>
I don't use Oracle, so some of what I write may not be quite right.
>
>Save the developed query as c:\temp\myquery.sql
>wizard to create ...." checked, and click "open". Get "Microsoft
>Query could not read this file"
Opening a saved query means the query was saved from MsQuery. I'm pretty
sure you can't open queries saved from other appliations.
>
>Hmm. searched help, googled a lot of KB articles. I gather that
>eventually I'm supposed to end up with my query in a .qry file in C:
>\Documents and Settings\<userid>\Application Data\Microsoft\Queries,
>and that file will have not only my big SELECT statement, but also
>information about which ODBC dsn to use, etc. But how do I get to
>that point? I actually got to a point where I was able to do a 'save
>as' and saved such a file, but can't seem to reproduce the steps, plus
>whenever I select that file:
The query can live anywhere. You are correct that the connection
information is saved in the qry file (and in Excel ultimately).
>
>All of which seems rather clumsy, and now that I need to make a
>change to the original SELECT statement, I don't seem to be able to
>get the new version in.
Yes, MsQuery is clumsy. It hasn't been updated in over 10 years and
probably never will be. Here are the steps I would take:
Create the query in Oracle and copy the SQL
In Excel, Data > Import External Data > New Database Query
On the Databases tab, select the Oracle ODBC DataSource - see
http://www.databasejournal.com/features/oracle/article.php/3358411 if you
don't see one.
UNCHECK the 'use the wizard' box. Your queries will probably always be too
complicated for the wizard, so just bypass it. I never use the wizard, even
for simple queries.
The MsQuery window will launch and may present you with a dialog to identify
your database (without Oracle, I'm not sure what you'll see here - post back
if it's not obvious).
At this point, MsQ should present you with a list of tables - Click Close
without selecting any tables.
Now you'll have a blank MsQ screen - the connection information is there,
but no SQL information.
Click on the SQL button on the toolbar and paste your SQL into the resulting
window.
When you click OK, MsQ should put your tables and relationships in the top
part of the window and possibly your recordset in the bottom part.
File > Return Data to Microsoft Excel
To edit the query:
Open the Excel file with the external data table.
Select a cell somewhere in the table
On the External Data Toolbar, click the Edit button
This should put you back into MsQ. Since you unchecked the wizard button,
it shouldn't tell you that it can't parse your query, but if it does, just
say Yes to editing in MsQ.
Now you can click the SQL button and edit your SQL statement.
File > Return Data to Microsoft Excel when you're done editing.
Rob van Gelder has a query editor you may find easier to use.
http://vangelder.orcon.net.nz/excel/queryeditor.html
Jan Karel Pieterse and I wrote a QueryManager - find it here
http://www.jkp-ads.com/download.asp
Neither Rob's nor Jan's does everything I would want in a query editor (even
though I helped write the latter), but you may find one of them easier than
MsQuery.
Good luck and post back if you have some follow-up questions.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com
|
|
|