how to import an excel file into sqlserver 2008

How can i import an excel file into a new table in sqlserver2008 express edition using an sql query without using th开发者_高级运维e import wizard

Thanks Prady

There is a microsoft knowledge base article that lays out all the ways this is possible.

http://support.microsoft.com/kb/321686

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Use ExcelReaderFactory to read excel

You can use the below code

VB.net Code

Dim stream As FileStream = File.Open("YouExcelFilePath.xls", FileMode.Open, FileAccess.Read)
Dim excelReader As IExcelDataReader = ExcelReaderFactory.CreateBinaryReader(stream)
Dim result As DataSet = excelReader.AsDataSet()
excelReader.Close()
result.Dispose()

C# Code

FileStream stream = File.Open("YouExcelFilePath.xls", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
result.Dispose();

Now use can do bulk import using Bulkcopy class.

or

create xml and send to database

or

Use OPENROWSET to read the excel file in Stored Procedure and insert/update the data.

Please follow the below article to implement it.

Read excel in SQL stored Procedure

right click on the database name/go to task and then select import data

as a source select an excel file that you created before and choose it's path

on the next page select sql server as destination