MS SQL server is a powerful database engine. However, it is hard to extract all data from a table out for backup or for analysis. MS SQL Management Studio does offer tools to export data, even one to export data to Excel. However, not every one has Microsoft SQL Server Management Studio. And if there lots of data in the table, built in export tool may error out. However, it is easy to pull data into Excel spread sheet with Excel built in Data Connections.
Steps to Extract Data to Excel with Data Connections
- Click on Data Tab and select Connections
- Click on “Add…” button. Then select “Browse for More…” button on the next window. Select built in “NewSQLServerConnection.odc”. If it does not show up automatically, you can browse to “C:\Documents and Settings\Your_User_Name\My Documents\My Data Sources\” to find it.
- Enter Server Name (or IP address) and log on credentials.
- Select database and table (high lined) you want to extract data from. Click on “Open” button to finish it. Then click on “Close” button to complete this step.
- Click on “Existing Connections” on Data tab.
- Select the data connection you created earlier.
- Select which sheet and starting cell you want to insert the data into.
- Now you have your data into Excel!
This example uses Excel 2010. Earlier versions of Excel has similar function.
With similar steps, you can pull stock data, foreign exchange data and major indices from MSN Money Central.