Data from SQL with server-side filters

[neFAST]

Dear all,
I'm trying to use Excel to extract data from a MSSQL server and then filter rows that contain specific values in some columns.
This is very basic and easy, even for a total newbie like me!

But now I'm querying a database with 100k+ rows and I only need to select a few dozen of rows. Dumping those 100k rows in excel and then filtering 99% away does not seem a smart and efficient way to do that.

• So I was wondering if there's a better way to achieve this, I was thinking excel could build a SELECT query with parameters taken from specific cells.
• I was also wondering if PowerPivot can help.
• any other option is welcome!

 

[Hui]

neFast

Firstly, Welcome to the Chandoo.org Forums

If you only want a few dozen rows from 100k+ rows, then Excel uis ideal tool for this. Power Pivot etc is an overkill.

You can access the SQL DB directly and include a selection string as part of the process

Open Excel
Goto the Data, From Other Sources, From SQL Server tab
Complete the form and subsequent dialogs

 

[neFAST]

Thanks for your answer

If you only want a few dozen rows from 100k+ rows, then Excel uis ideal tool for this. Power Pivot etc is an overkill.

Keep in mind I want only a dew dozen rows but not always the same ones.
The SELECT filters have to be dynamic.

You can access the SQL DB directly and include a selection string as part of the process

Open Excel
Goto the Data, From Other Sources, From SQL Server tab
Complete the form and subsequent dialogs

This is what I'm doing except that I did not customized the SQL query. Can I make the selection depends on the content of specific excel cells?

 

[NARAYANK991]

Hi ,

See if this helps.

http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

Narayan

 

[neFAST]

Do I really have to go back to MSQuery?

 

[neFAST]

I followed this guide:
http://superuser.com/questions/197453/run-an-sql-query-with-a-parameter-from-excel-2007

But now I've a strange problem.
When typing my search string the query works fine:

When using a parameter, I get zero result:


What is wrong?

 

[dan_l]

you don't need the percents when you fill in the parameters I think.