Call SQL Procedure in EXCEL

How to call a SQL Server Stored Procedure from EXCEL

Step 1:
Open the EXCEL work book and Click on "From Other Sources" and choose "From Microsoft Query".

A pop up will open to choose a Data Source.
Step 2:
If you are doing in first time, Select "New Data Source" then Click "OK".

Step 3:
A Data Source Name is our choice as per the naming convention.
Select a Driver as "SQL Server" from the Drop-down for point 2 in the Image.

Step 4:
Click on Connect (Point 3), and provide the details as mentioned below Image.

Once the credentials provided, click on "Options", so that we can choose the Database Name.

Now, click "OK".
Step 5:
Choose the Point 4 if you want all the columns. It's a optional Drop down. Then click OK.

Now, we can see the Data Source which we created. Select it and click "OK".

Step 6:
Choose Cancel, so that it will take us to SQL Query Window.

Click on "Yes", to close Microsoft Query Window.

Step 7:
Choose "Close" and click on SQL where we can write the SQL Queries.

I have created a procedure already with single Input Parameter.

Step 7:
//{CALL dbo.StoredProcedureName( Keep the Question marks each for one Input Parameter)}
{CALL MyOrg.dbo.GetAllEmpByDid (?)}
Then, click "OK".

Step 8:
Once click on "OK", It will asks for the Input.

We can find the procedure results in below Image. 

Close the "Microsoft Query" window.
Once it is closed, A " Import Data" wizard will be open and then Procedure results will be populated into Excel. It would be started from the cell which we selected.

Now, compare both the result sets from SQL Server and Excel( Just for unit testing).
















We can refresh the data and do the filters as per our requirements.

We also can see the connection properties and connection string settings here.

There are many features to filter and Import the data. I just explained how to call a procedure with parameter.

Get data from Online to Excel

How to get data from website to Excel2013

Step 1: Open Your Excel sheet

Step 2: Click on Online Search  or FromWeb

If you know the exact address of a particular website then choose FromWeb

Step 3: In  search what data you want choose it by moving the cursor on tabs.
Then click on it.
At the end of page you can choose data where to load like Work sheet or data model

above and below images showing that while we drag cursor on searched items, the data should appear.
From this we can choose what data we want to analyze?

After double click, the data had loaded into work sheet or data model or both based on what option you checked.

Enable Power Query and PoerPivot in Excel 2013

How to enable Power Query and PowerPivot

Step1: Open Excel 2013

 if those are not available then

Step2: Click On file-->Select Options-->Click on Add-In

next 

next

If Microsoft Office PowerPivot or PowerQuery or PowerView not appear then download from microsoft website and install them

Again follow from step 2.