Import Data from EXcel Sheet to SQL Server

To get the data from Excel sheet to SQL Server we can use many of SQL server services.
Before we import data using Excel Sheet ..We have to reconfigure our service with below queries..

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Below query will return data from your Excel file and sheet1.
some of the attributes we have used "Microsoft.Jet.OLEDB.4.0" it is driver name. and spcify Excel 8.0.

In database we have specified our file path.

In that HDR specifies that whether our excel sheet can treat first row as Header or not. If we have set it to NO then OLEDB provider assign Column Names F1 to Fn. Same thing will happen if we have not included HDR in our connection string.

In Excel sheet it is possible that column have mixed data type. By default OLEDB provider see the column's value and then depending upon it defined datatype on that column.

If column have numeric value then it will omit anyother value in column and set null for other values.To over come this problem we can use IMEX attribute which brings all data regardless of its type.

If we have set IMEX we will have all data as we have in Excel sheet.


SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testImport.xls;HDR=No;IMEX=1',
'SELECT * FROM [Sheet1$]')