Description
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 Data Tab and 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.
Select New SQL Server Connection - Enter Server Name (or IP address) and log on credentials.
Set up Database Server Connection Parameters - 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.
Select Database and Table to Import - Click on “Existing Connections” on Data tab.
Select Existing Connections - Select the data connection you created earlier.
Select Connection Created - Select which sheet and starting cell you want to insert the data into.
Enter Data Location - Now you have your data into Excel!
Data Imported
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.