Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to useSQL Server in Excel?

Posted on 2015-02-05
18
Medium Priority
?
186 Views
Last Modified: 2015-02-10
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
0
Comment
Question by:jfz2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 2
  • +3
18 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40591249
Why don't you create a view in SQL Server, and then you can pull that view into Excel?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40591250
It may also be good from a security point of view, rather than giving access to the underlying table.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40591254
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40591255
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.
0
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 40591443
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.xls;',
    '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.xls;HDR=YES',
    'SELECT * FROM [Sheet1$]')
0
 
LVL 18

Accepted Solution

by:
Simon earned 2000 total points
ID: 40591459
You can use the 'data from other sources'

1. choose SQL Server as the source
2. Pick any small table and let the connection wizard complete and return data
3. Go to the properties of the external data (Table Tools/Design/Properties)
4. click the 'connection properties' icon in the dialog (top right)
5. Switch to the definition tab
6. change 'command type' to SQL
7. Type your select statement into the 'command text' box.
8. Press OK.
0
 

Author Comment

by:jfz2004
ID: 40591917
Works!. Great Thank you!
0
 

Author Comment

by:jfz2004
ID: 40592014
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?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40592192
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...
0
 

Author Comment

by:jfz2004
ID: 40592272
Thank you so much. I can wait for tomorrow. Have a nice day.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40593179
Parameters are only available in ODBC queries and web queries. Redo your query as 'From Microsoft Query', using your SQL Server DSN.ConnectionProperties.pngType your SQL statement using question marks where you want parameters. When you OK the dialog it will prompt you for the cell value to use.
ParametersDialog.png
0
 

Author Comment

by:jfz2004
ID: 40593854
Thanks a lot. Let me try it out. Jennifer
0
 

Author Comment

by:jfz2004
ID: 40594331
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
0
 
LVL 18

Expert Comment

by:Simon
ID: 40594410
You need to create a new data source, pointing to your SQL Server. Use the ODBC administrator control panel.
0
 

Author Comment

by:jfz2004
ID: 40598320
Thanks. Let me try it. How to "use the ODBC" administrator control panel?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40598497
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.
0
 

Author Comment

by:jfz2004
ID: 40600783
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.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question