Using a worksheet as a table in an SQL query

[steveh]

In Access, it is easy to create a table, let a user add data to it, and
include that table in a query joined to one or more tables in a SQL
Server database.

Is it possible to do something similar with Excel, using a separate
worksheet in the same workbook instead of an Access table? If so, what
would be the basic steps? (I have no problems querying the SQL Server
database.)

I would like to create an outer join to the worksheet data. The results
would be used in a pivot table. If it matters, I'm using Excel 2003.

Thanks for looking!

Steve

 

[MH]

Is there any reason you are not storing this data in a database?

MH

 

[steveh]

Yes. For one, it is a proprietary database that does not allow any data
to be written to it outside of the application. If I were to use
another database, it seems like I would run into the same issue of
essentially not being able to connect to two different databases. Or am
I perhaps missing something (which is what I hope)?

There are ways to enter the data into their database via the
application, but it would require them to go to many different windows
instead of being able to enter the data all on one screen.

There are other workarounds I could use. As a last resort, I could scrap
the pivot table and just subtotal the data instead. But I'm leaning
towards using a select/case function in the query. There is not a great
deal of data they would enter on the spreadsheet (no more than 500 rows
and three columns), but I'm not sure how slow that would make the query.

Steve

 

[MH]

So what you have is a spreadsheet containing a dataset of 500 rows and 3
columns, and you want to join that to a table in SQL Server and create a
pivot table?

You could use Access to link to both sources (Excel dataset and SQL Server
table) and create a crosstab query. You will have to be careful of datatype
errors though, spreadsheets can be a bad choice for storing data sometimes.

MH

 

[steveh]

If Access was an option, I would put the data in a table. They don't
have Access and don't want to buy it.

I don't think my original idea is possible. I'll probably just dump the
SQL Server data to a spreadsheet and join the two spreadsheets. I think
that should work.

Thanks for your time, MH.

 

[MH]

If you want to do the whole thing in SQL Server you have a couple of
options:

1. Openrowset / Openquery (see BOL)
2. Use a "Linked Server"
3. Use OpenDataSource command:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$

If you have any questions about the above, you should create a new post in:

microsoft.public.sqlserver.programming

MH