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

  1. Click on Data Tab and select Connections

    Click on Data Tab and Connections
    Click on Data Tab and Connections
  2. 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
    Select New SQL Server Connection
  3. Enter Server Name (or IP address) and log on credentials.

    Set up Database Server Connection Parameters
    Set up Database Server Connection Parameters
  4. 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
    Select Database and Table to Import
  5. Click on “Existing Connections” on Data tab.

    Select Existing Connections
    Select Existing Connections
  6. Select the data connection you created earlier.

    Select Connection Created
    Select Connection Created
  7. Select which sheet and starting cell you want to insert the data into.

    Enter Data Location
    Enter Data Location
  8. Now you have your data into Excel!

    Data Imported
    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.