Introduction
MS Excel is the most popular product used by management-level people in every organization. You can do the analysis of data very easily in Excel and apply formulas, create charts etc. What if your data source is very big, stored in SQL DB, and you need to pull dynamic filtered data into the Excel file?
In this article, we will see how to get data from SQL DB into Excel file on demand.
Detailed steps
First, we need to add the connection to the Excel file and choose the correct driver for the connection.
Then, configure this connection with a connection string; write the required command to execute; configure dynamic parameter values. And at last, use this connection on particular cells in Excel file so that the fetched data can be shown there.
Let's jump into the detailed steps to follow to achieve this connection.
Step 1
Open your Excel file.
Click on the "Data" tab.
Select "Get Data" menu >> “From Other Sources” >> “From Microsoft Query”.
Step 2
Click on "<New Data Source>", (you can use existing data source as well if you already have added it).
Step 3
Give a proper name to the data source connection. Select driver for connection; in my case, I am choosing SQL Server Native Client 11.0.
Step 4
Click on "Connect".
Step 5
Now, the below window will be shown.
- Select the SQL Server to which you want to connect (This list will be auto-populated from your network, all available SQL servers will be shown here).
- Provide login ID and password to connect to it.
Step 6
Once done, let us go back to the main Excel file. Again go to "Data" tab >> Queries and Connections and make the below configuration on the newly created connection.
In the "Usage" tab, as shown below, make sure "Enable background refresh" and "Refresh this connection on refresh All" are checked.
Step 7
Continuing on the same window in the "Definition" tab, make sure that the connection string is added/generated correctly. Click on "Save Password" - Add "{call storedprocname(?)}" in the Command Text section if stored proc has one input parameter.
Step 8
Click the "Parameters" button to map the input parameter to a cell in the sheet (dynamic input), Check "Refresh automatically when cell value changes" so that whenever the user changes data in that cell, the command in connection settings will get triggered.
Step 9
Make the below configuration to the connected table so that when a user deletes data from the connected parameter cell, then the result table also should get cleaned.
Select the table >> Right click on it >> select Table >> External Data Properties.
Step 10
Make sure to select these options and click OK. Select "Insert entire rows for new data, clear unused cells" option.
That’s it. Now, enter the value in the cell which is configured as an input parameter in the connection. Hit the tab button and you will see that the connector executes the stored procedure and shows data in the selected result table.
Share this Excel file with your end-users so they can see the magic of getting dynamic data filled automatically from the database.
I hope this helps.