jfz2004
asked on
How to useSQL Server in Excel?
Hi,
I need to perform a select statement in a SQL Server and pull the data into an Excel.
I can do it and pull the whole table into Excel by using some function in the "From Other Data Sources" But I don't know how to put the select statement in the "Command Text" in the "Connection Properties". Can anyone tell me how to do that?
Thanks!
Jennifer
I need to perform a select statement in a SQL Server and pull the data into an Excel.
I can do it and pull the whole table into Excel by using some function in the "From Other Data Sources" But I don't know how to put the select statement in the "Command Text" in the "Connection Properties". Can anyone tell me how to do that?
Thanks!
Jennifer
Why don't you create a view in SQL Server, and then you can pull that view into Excel?
It may also be good from a security point of view, rather than giving access to the underlying table.
I would create a view in SQL and select from that. If you don't have access to the database, then use Microsoft Query.
https://support.office.com/en-ca/article/Use-Microsoft-Query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e
https://support.office.com/en-ca/article/Use-Microsoft-Query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e
Not a direct answer, but it's pretty close: Check out my article on Microsoft Excel & SQL Server: Self service BI to give users the data they want This calls a SQL Stored Procedure to pump data into Excel, which you can either code your own, or replace with a SELECT statement.
Create an Excel file named testing having the headers same as that of table columns and use these queries
1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet. OLEDB.4.0' ,
'Excel 8.0;Database=D:\testing.xl s;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
2 Export data from Excel to new SQL Server table
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet. OLEDB.4.0' ,
'Excel 8.0;Database=D:\testing.xl s;HDR=YES' ,
'SELECT * FROM [Sheet1$]')
1 Export data to existing EXCEL file from SQL Server table
insert into OPENROWSET('Microsoft.Jet.
'Excel 8.0;Database=D:\testing.xl
'SELECT * FROM [SheetName$]') select * from SQLServerTable
2 Export data from Excel to new SQL Server table
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.
'Excel 8.0;Database=D:\testing.xl
'SELECT * FROM [Sheet1$]')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works!. Great Thank you!
ASKER
One more thing, what if I have to use a where clause in the SQL and there is a "?" in the
where clause? How to link the "?" to a cell value?
where clause? How to link the "?" to a cell value?
I have a working example of a parameterised query that auto-refreshes when I change the value of the cell that supplies the parameter, but can't get to it until tomorrow. Sorry. Someone else may be able to help sooner...
ASKER
Thank you so much. I can wait for tomorrow. Have a nice day.
ASKER
Thanks a lot. Let me try it out. Jennifer
ASKER
Hi,
I am trying to use 'From Microsoft Query'. But then, it requires me to 'choose Data Source". My database (a SQL Server DB) is not listed in it. What can I do now?
Thanks.
Jennifer
I am trying to use 'From Microsoft Query'. But then, it requires me to 'choose Data Source". My database (a SQL Server DB) is not listed in it. What can I do now?
Thanks.
Jennifer
You need to create a new data source, pointing to your SQL Server. Use the ODBC administrator control panel.
ASKER
Thanks. Let me try it. How to "use the ODBC" administrator control panel?
In Windows 7, you'll find it in Start\Control Panel\Administrative Tools\Data Sources (ODBC). Create a user or system Data Source Name (DSN) that points to your database.
1. First choose the SQL Server driver on the first page,
2. then the name of the SQL Server instance on the next page.
3. On the next tab, choose Windows NT authentication (if you login to SQL using you network login ID) or SQL server authentication if you have a separate login and password for your SQL access.
Once you have defined the DSN, you use that when creating a new MS Query against the database from Excel.
1. First choose the SQL Server driver on the first page,
2. then the name of the SQL Server instance on the next page.
3. On the next tab, choose Windows NT authentication (if you login to SQL using you network login ID) or SQL server authentication if you have a separate login and password for your SQL access.
Once you have defined the DSN, you use that when creating a new MS Query against the database from Excel.
ASKER
Thanks a lot! I created the DSN!. But I still get teh "EDIT" button grayed out. So can you guide me step by step? Since this thread has already completed, I created another thread. If you could help me on the other thread, that will be great. Thank you so much.