Saving Query's Result Set as a Table

[windsurferLA]

Included with MS-Excel is MS-Query. I use MS-Query to select records
from a dbaseIII formatted data base. The Query produces the desired
results, but I've not been able to save the results of the query as a
"table," i.e. as a dbaseIII tab deliminated file

In the version of original Query that came with Excel95, one merely went
to SAVEAS and dbaseIII was an option. That version no longer works
because it depends on ODBC files that are updated by the Excel97
application.

According to the manual provided by Microsoft for Query, to save the
result of a query as a new table, from the File menu, choose SaveAs. The
"select data source" dialog box is supposed to come up, and when you
select a data source, you're telling Microsoft Query: "Where to create
the new table." and "what format the data in the table will have."
Obviously, there have been changes to Query since my manual was printed
as I'm not given those options.

When I go to File menu, SaveAs, I'm only given the option of saving the
Query itself as a .qry file or a .dqy file.

When I go to File menu, Table Definition, I get a "chose data source
dialog box," but all of the alternatives are for external data bases
(Oracle, Sybase etc.)

How do I save the results of a MS-Query97 query as a simple CSV or
dbaseIII formated table? Would it be simpler to use MS-Access to do
the same function?

 

[onedaywhen]

I'd do it in one query, while connected to a MS OLEDB Jet source e.g.
ADO connection to a a closed Excel workbook but could be in the SQL
window of in the MS Access GUI. I don't have dBaseIII so here's an
example that uses SQL Server via ODBC:

SELECT emp_id, lname, fname INTO
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMPO\;].[OutFile#txt] FROM
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=;Pwd=;].employee

The resulting text file is comma delimited. You can easily make this
tab delimited by creating a schema.ini file. Using the text editor of
your choice, create a text file containing the following plain text:

[OutFile.txt]
Format=TabDelimited

and save it in the folder in which OutFile will appear e.g.
C:\TEMPO\schema.ini for my example.

 

[windsurferLA]

Thank you for your suggestion even though I do not as yet fully
understand it. For others that may be following this thread, I have
since found out two things.

(1) Microsoft appears to have configured Query97 so the results cannot
be saved as a dbase file. Query HELP states, "Microsoft Query 97 does
not allow you to save the result set as a table in the database."
However, there are hints that it can be done with "third party" drivers.
If you know of any, I'd like to hear about them.

(2) If you save a Query97 query as a .dqy file, you can then open that
Query97 from within Excel97 by going to the menu
TOOLS\DATA\GET_EXTERNAL_DATA. When one is satisfied with the results of
the query, one can transfer the data into Excel by "exiting" Query. In
place of the normal "exit" option under FILE are words like "transfer
data." Once the selected data is in a standard Excel spread sheet, it
supposedly can be saved in dbaseIII and other formats merely using the
SAVE-AS option.

Currently I'm using process (2) to solve my overall problem of how to
select data from a dbaseIII data file for an MS-Word97 mail merge. I
first open Query Via Excel and save results as an Excel file. Then I run
Word using the Excel file as the data source for the mail merge. It adds
one step to my process, but it seems to work.

I'd do it in one query, while connected to a MS OLEDB Jet source e.g.
ADO connection to a a closed Excel workbook but could be in the SQL
window of in the MS Access GUI. I don't have dBaseIII so here's an
example that uses SQL Server via ODBC:

SELECT emp_id, lname, fname INTO
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMPO\;].[OutFile#txt] FROM
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=;Pwd=;].employee

The resulting text file is comma delimited. You can easily make this
tab delimited by creating a schema.ini file. Using the text editor of
your choice, create a text file containing the following plain text:

[OutFile.txt]
Format=TabDelimited

and save it in the folder in which OutFile will appear e.g.
C:\TEMPO\schema.ini for my example.

--

Included with MS-Excel is MS-Query. I use MS-Query to select records
from a dbaseIII formatted data base. The Query produces the desired
results, but I've not been able to save the results of the query as a
"table," i.e. as a dbaseIII tab deliminated file

In the version of original Query that came with Excel95, one merely went
to SAVEAS and dbaseIII was an option. That version no longer works
because it depends on ODBC files that are updated by the Excel97
application.

According to the manual provided by Microsoft for Query, to save the
result of a query as a new table, from the File menu, choose SaveAs. The
"select data source" dialog box is supposed to come up, and when you
select a data source, you're telling Microsoft Query: "Where to create
the new table." and "what format the data in the table will have."
Obviously, there have been changes to Query since my manual was printed
as I'm not given those options.

When I go to File menu, SaveAs, I'm only given the option of saving the
Query itself as a .qry file or a .dqy file.

When I go to File menu, Table Definition, I get a "chose data source
dialog box," but all of the alternatives are for external data bases
(Oracle, Sybase etc.)

How do I save the results of a MS-Query97 query as a simple CSV or
dbaseIII formated table? Would it be simpler to use MS-Access to do
the same function?

 

[jdbcguy]

Thank you for your suggestion even though I do not as yet fully
understand it. For others that may be following this thread, I have
since found out two things.

(1) Microsoft appears to have configured Query97 so the results cannot
be saved as a dbase file. Query HELP states, "Microsoft Query 97 does
not allow you to save the result set as a table in the database."
However, there are hints that it can be done with "third party" drivers.
If you know of any, I'd like to hear about them.

(2) If you save a Query97 query as a .dqy file, you can then open that
Query97 from within Excel97 by going to the menu
TOOLS\DATA\GET_EXTERNAL_DATA. When one is satisfied with the results of
the query, one can transfer the data into Excel by "exiting" Query. In
place of the normal "exit" option under FILE are words like "transfer
data." Once the selected data is in a standard Excel spread sheet, it
supposedly can be saved in dbaseIII and other formats merely using the
SAVE-AS option.

Currently I'm using process (2) to solve my overall problem of how to
select data from a dbaseIII data file for an MS-Word97 mail merge. I
first open Query Via Excel and save results as an Excel file. Then I run
Word using the Excel file as the data source for the mail merge. It adds
one step to my process, but it seems to work.

I'd do it in one query, while connected to a MS OLEDB Jet source e.g.
ADO connection to a a closed Excel workbook but could be in the SQL
window of in the MS Access GUI. I don't have dBaseIII so here's an
example that uses SQL Server via ODBC:

SELECT emp_id, lname, fname INTO
[Text;FMT=Delimited;HDR=Yes;DATABASE=C:\TEMPO\;].[OutFile#txt] FROM
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=pubs;UID=;Pwd=;].employee

The resulting text file is comma delimited. You can easily make this
tab delimited by creating a schema.ini file. Using the text editor of
your choice, create a text file containing the following plain text:

[OutFile.txt]
Format=TabDelimited

and save it in the folder in which OutFile will appear e.g.
C:\TEMPO\schema.ini for my example.

--

Included with MS-Excel is MS-Query. I use MS-Query to select records
from a dbaseIII formatted data base. The Query produces the desired
results, but I've not been able to save the results of the query as a
"table," i.e. as a dbaseIII tab deliminated file

In the version of original Query that came with Excel95, one merely went
to SAVEAS and dbaseIII was an option. That version no longer works
because it depends on ODBC files that are updated by the Excel97
application.

According to the manual provided by Microsoft for Query, to save the
result of a query as a new table, from the File menu, choose SaveAs. The
"select data source" dialog box is supposed to come up, and when you
select a data source, you're telling Microsoft Query: "Where to create
the new table." and "what format the data in the table will have."
Obviously, there have been changes to Query since my manual was printed
as I'm not given those options.

When I go to File menu, SaveAs, I'm only given the option of saving the
Query itself as a .qry file or a .dqy file.

When I go to File menu, Table Definition, I get a "chose data source
dialog box," but all of the alternatives are for external data bases
(Oracle, Sybase etc.)

How do I save the results of a MS-Query97 query as a simple CSV or
dbaseIII formated table? Would it be simpler to use MS-Access to do
the same function?

The utility Super*SQL will allow you to query any database (if you
have an ODBC connection or the correct JDBC drivers available) and
save the results as an ASCII file delimited with |. You can then
import this file into Excel and save it as a DBF file. A little more
complex but it should work. I found the Beta version at
www.sqlmagic.com