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.


Related Articles

Read Excel Sheet Data and Display in Gridview by Using C# Coding

In these Article i'm trying to explain about "How to Read Data from Excel Sheet and Display Excel Data into Asp.net Gridview by using C# Coding". And also show how to manipulate excel sheet data in gridview control by using RowEditing Event and RowUpdateing Event in Gridview.

More articles: SQL excel operations sql queries excel

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: