How to import excel file in sqlserver 2008

Question

How can I import an excel file into a new table in sqlserver2008 express editor using SQL query without using the import wizard

Thanks Pradi

Answer 1

There is a Microsoft Knowledge Base article that outlines all the possible ways.

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

I think using OPENROWSET or OPENDATASOURCE would be the easiest way without a wizard. (see Common Queries)

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

See the OPENROWSET documentation, with examples below the page.

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

Answer 2

Use ExcelReaderFactory to read excel

You can use the code below

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 perform bulk import using the Bulkcopy class.

or

create xml and send to database

or

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

Please follow the article below to implement it.

Read Excel in SQL Stored Procedure

Answer 3

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

as the source, select the excel file you created earlier and select its path

on the next page select sql server as destination