Reader Level:
ARTICLE

Import Data from SQL Server 2005 to Excel Sheet and Viceversa

Posted by Srinivas Articles | SQL Server May 11, 2009
This Article Shows How to Use SQL Server to Export Data from SQL Server to Excel Sheets and Viceversa.
  • 0
  • 0
  • 29171


Import and Export data from SQL Server 2005 to XL Sheet:

In this article, I will explain how to upload the data from an Excel Sheet to a SQL Server 2005 database and also export data from a SQL Server database to an Excel sheet.

For uploading the data from Excel Sheet to SQL Server and viceversa, we need to create a linked server in SQL Server.

Procedure for creating a linked server in SQL Server 2005.

Expample linked server creation:

Before you executing the below command the excel sheet should be created in the specified path and it should contain the name of the columns.
 
EXEC sp_addlinkedserver 'ExcelSource2',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'C:\Srinivas\Vdirectory\Testing\Marks.xls',
   NULL,
   'Excel 5.0'

Once you executed above query it will crate linked server in SQL Server 2005.

The following are the Query from sending the data from Excel sheet to SQL Server 2005.

INSERT INTO emp SELECT * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\text.xls','SELECT * FROM [sheet1$]')

The following query is for sending the data from SQL Server 2005 to Excel Sheet.

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\text.xls;',
'SELECT * FROM [sheet1$]') select * from emp

These two queries are working fine.

Article Extensions
Contents added by nagababu s on Jul 08, 2013
COMMENT USING

Trending up