In this blog post we will see how we could use Excel to connect to SQL Azure and retrieve data. Before we begin with excel we need to have the DNS address of our SQL Azure which we could get from management portal then we will see how we could create connection and retrieve our data from excel built-in connection management. Let us see how we could achieve this in action:
Step 1: Go to SQL Azure management portal and grab your database connection address. If you already have this address you could skip this step
Step 2: Open Excel then click on Data menu. On Data menu click on Connections on the ribbon and select SQL Server from the drop down menu. On the dialog box paste or type your SQL Azure address for Server Name, then type your existing user name and password. Click on Next
Step 3: On the next dialog box select the database name and click on the table name you want to download of if you want to get work on all tables unselect the check box for connect to a specific table
Step 4: In the next dialog box verify everything is done properly then click on Finish.
Step 5: Click on connections then on the dialog box click on Add…
Step 6: Click on the new connection you created then click on Open.
Step 7: On the next dialog box select the table you want to query then click on OK
Step 8: In the next dialog box if you prefer to query a combination of tables or part of the table you can click on the definition tab then select SQL as a command type. In the command text box type you SQL Script then click on OK
Step 9: On the bottom left corner of you Excel screen you will see the status of your data refresh if takes lot of time you can also click on Click here to cancel button to cancel it.
After it finishes fetching your data will be displayed on Excel sheet.
