How to read data from Excel Sheet in SQL Server?

Hi,

The agenda for today's session is to discuss how to read data from Excel Sheet from SQL Server.

We all must have faced a scenario where we receive Excel sheet data from user and need to either update the contents in database or do some calculations using that data. Most of the time user is unaware of the codes/IDs used as primary key, so they give only Textual data.

Scenario will be to read data from Excel Sheet, join with other existing tables to get proper data or do some calculations.

There are many ways to achieve this:

Let's do it without using SSIS / DTS. Let's use OpenRowset function to do this.

Prerequisites:

1. Excel sheet must be available on SQL Server.
2. 'Ad Hoc Distributed Queries' option must be enabled. By default OpenRowset and OpenQuery options are disabled. We need to enable it to read data from external sources.


There are 2 ways in which you can enable OpenRowset and OpenQuery functions.

One way is to do it using SQL Server Surface Area Configuration Tool/Utility.

This tool can also be accessed by typing SQLSAC.EXE from Command Line or
using menu:

Following window will appear:

Once clicked the "Surface Area Configuration for Features" link within SQL Server 2005 Surface Area Configuration screen, will open a new screen which has the list of SQL Server 2005 Database Engine, Analysis Services & Reporting Services features. Such features include CLR Integration, DAC, Database Email, Service Broker, xp_cmdshell, Linked Objects etc which are turned off by default within SQL Server 2005.

As you can see the option is unchecked, so once its checked, we will be able to use this feature.

Another way to do this is using T-SQL sp_configure command.

The OPENROWSET and OPENDATASOURCE functions support ad hoc connections to remote data sources without using linked or remote servers. Enable this feature either by using SQLSAC or by executing the below TSQL code.

USE master
GO
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
GO

As Distributed Queries are enabled, we can start to fetch data from Excel sheet.

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;DATABASE=C:\TestData.xls', 'Select * from [Sheet1$]')

Openrowset has 1st parameter as 'Provider'
2nd parameter is Data Source
User Id and Password can also be passed.
3rd parameter is the query.


This will display all records from the Excel sheet.


The following example accesses the Customers table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.

SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO

The following example selects all data from the Customers table from the local instance of SQL Server Northwind database and from the Orders table from the Access Northwind database stored on the same computer

USE Northwind  
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c 
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)    
AS o 
ON c.CustomerID = o.CustomerID 
GO

The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.

USE AdventureWorks
GO
CREATE TABLE myTable(FileName nvarchar(60), 
FileType nvarchar(60), Document varbinary(max))
GO

INSERT INTO myTable(FileName, FileType, Document) 
SELECT 'Text1.txt' AS FileName, 
'.txt' AS FileType, 
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
GO

How to do the same in SQL Server 2008?

You thought that you have by now become comfortable with the Surface Area Configuration Tool in SQL Server 2005 and are ready to do the configuration once you install SQL Server 2008. Well, the tool does not exist anymore in SQL Server 2008 and has been added to the deprecated feature list.

So, how can you go about making configuration changes via the GUI? It has been divided up into different tools now:

Right click on the instance and select Facets as shown in the image below:

In the next window, scroll down and select "Surface Area Configuration"

Now you can set the different permissions using SSMS, but we can still follow T-SQL to achieve by executing following command:

USE master
GO
sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
GO

The other way to read data from EXCEL is using DTS/SSIS.

Hope this information is useful, let me know your feedback / comments.

Regards,

Sudhir