Excel operations in SQL Server
This code snippets explain about the export and import the excel sheet using pure sql query.
Description:
We can easily export our query result to excel and import the datas from excel sheet using pure sql query. These queries are called "Ad hoc distributed Queries". In all sql basically this "Ad hoc distributed queries" options is off. So if we want to perform export or import operations in sql, we must enable or on this options.
Use the following queries to enable,
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
You must prepare the excel sheet for what are all the columns are you want to export. Your excel sheet also must contains the same column name and the excel should be in c drive.
For example,
If i want to export the Username,Emailid from userdetails table then we must create the excel sheet with same column name. Refer the sample excel sheet.
Following query export the sql query result to excel sheet.
Syntax:
-------
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=Excel sheet location;',
'SELECT * FROM [ExcelSheetName$]')
YOUR SQL QUERY
Example:
--------
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Userlists.xls;',
'SELECT * FROM [Sheet1$]')
SELECT * FROM UserDetails
Following query is used to read the excel data,
Syntax:
--------
select * from OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=Excel sheet location;',
'SELECT * FROM [SheetName$]')
Example:
--------
select * from OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Userlists.xls;',
'SELECT * FROM [Sheet1$]')
Here the Userlists.xls is the excel sheet name and Sheet1 is the excel sheet name.
Refer the sample excel sheet.
The sample sheet contains a sheet name with "Userdetails".
********************Note***********************
Your excel sheet must be reside on "C:\" drive
********************Note***********************
If any body works with different location please post as a response.