Monday, January 30, 2012

SQL Server to Excel Import

How to Import data from SQL Server database to an Excel file?

If you have Excel 2007 or higher then in the Menu options go to 'Data' and there should be an option to import data from SQL Server.

If the 'Microsoft SQL Server' does not show up in the data sources, then go to Control Panel > Admin tools > Data sources and add MS SQL Server ODBC driver.

For Microsoft Office 2003

1) In the menu click on 'Data' > Import External Data > Import Data.

2) In the 'SELECT DATA SOURCE' window click on 'New Source'

3) In the 'Data Connection Wizard' pick 'Microsoft SQL Server'

4) Next enter the hostname or IP Address of the server where the SQL Server instance is running.
Also pick whether you want to connect using Windows authentication or SQL Server. And enter the appropriate login information.

5) Next select the database name and the table name you want to SELECT the data from.

6) Give an appropriate Name and description for this connection.

7) You should see the 'Select Data source' Window now. If not then go to 'Import external Data' in the menu and pick the connection you just created.

8) In the 'Import data' select the worksheet and the location where you want this data to be displayed.

9) If you want to import the entire table then click 'Edit Query' and Select 'Table' in 'Command Type' drop down.

If the table is too large and you want to restrict the number of records selected then pick 'SQL' in 'Command Type' drop down and enter the SELECT query in the 'Command Text' window.

No comments:

Post a Comment