Fetching Excel Sheet Data in SQL using SQL Query

Fetching Excel Sheet Data in SQL using SQL Query

In order to Select, fetch or Read  the Excel Sheet Data using SQL Query or Creating Table in SQL  using Excel query first configure the advanced option through below scripts

step 1
sp_configure 'show advanced options', 1;
RECONFIGURE;

step 2
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 3


for MSExcel 2007 and 2010 --Database is the path of your excel sheet ,sheet1 is the name of sheet1

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=E:\book2.xlsx', 'SELECT * FROM [Sheet1$]');


Step 4 if you want to create a new table in sql based on excel data
for MSExcel 2007 and 2010 --Database is the path of your excel sheet ,sheet1 is the name of sheet1

SELECT * into test FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=E:\book2.xlsx', 'SELECT * FROM [Sheet1$]');


Case if you are using Excel 2003

SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.4.0', 'Excel 4.0;Database=E:\book2.xls;HDR=YES', 'SELECT * FROM [Sheet1$]');


 In case if you will get error like

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Run the below script to fix the Error

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Note: You can use import export wizard ,SSIS also to import Data from excel sheet to sql


 --Server Bit Check

SELECT SERVERPROPERTY('Edition')

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