Fetching Data from Microsoft Excel using SQL

Introduction

In this article, I am going to show you how to use an Excel workbook as your data source and fetch data based on your SQL query. In my first step, I will show you how to write a query using SQL syntax and next, I will show you how to fetch and bind data in your DataGrid.

Background

When I started reading an Excel workbook by writing traditional VBA code I found it's complicated to read even one cell value. After writing successful code too, you may be trapped under the COM memory leakage issue. If your Excel file has a huge amount of data to read and quite a few number of sheets, then you can expect a nice popup window showing up with, "You are running out of virtual memory." Even after killing several objects during this process, you cannot make sure that your object will be released immediately. Finally I found that SQL can reduce code complexity and I can gain performance and there is no memory leakage issue. I am assuming that those of you who are reading this article have basic knowledge of ADO.NET and Microsoft Excel.

Using the Code

Getting data from any data storage using ADO.NET is very simple. Before writing real code, let's create a Windows application (though you can use Web application as well), and add the following line at the top of the target form. I am going to use OLEDB API for accessing Excel data. OLEDB will take help of the JET engine to execute a query and fetch data from Excel.

using System.Data.OleDb; 

Please make a note that I am using Office 2003 and Visual Studio 2003.

1. Connection String

First, we will establish connection to our data source. It is very similar to connecting to the SQL Server or Oracle.

OleDbConnection con = new OleDbConnection(
    "provider=Microsoft.Jet.OLEDB.4.0;data source=" + 
    "File Name with Complete Path" +";Extended Properties=Excel 8.0;"); 

2. Writing Query

Writing an Excel query is as similar as writing a query in any other traditional data storage like SQL Server, Oracle, etc. However there are a few differences. First, you have to specify your sheet name instead of your table name. Next, you have to give starting and end cell references. Watch my following code carefully:

SELECT * FROM [42560035$A1:F500]

• In the above query, 42560035 is my sheet name (consider this to be your table name). In your case, this name may be different. Please do not forget to write your sheet name inside [] bracket.
• Next to the sheet name is my start cell and end cell reference. Your Excel file may contain different cell references. Please make sure that after your sheet name, you are using the dollar sign ($) then your start cell reference and end cell reference separated by colon (:). It is an error if you do not mention your start cell and end cell references in your query.

This was a very simple query. Here is the more complicated one:

SELECT * FROM [42560030$A21:F500] 
    WHERE [Period_End Date] = #3/2/2007# 
    ORDER BY [Date_Incurred] 

In place of *, which returns all columns, you can specify exact column(s) name you are looking for. Here is an example:

SELECT [Associate Name] as Associate,[Amount] as Amount 
    FROM [42560030$A21:F500] 
    WHERE [Period_End Date] = #3/2/2007# ORDER BY [Date_Incurred] 

Please make sure that you are placing your column name inside [] bracket if your column name contains a white space. Otherwise, your JET engine will throw an exception. For consistency, place all column names inside [] bracket. One more interesting point: if you observe the [Period_End Date] column name carefully, there is an underscore (_) between Period and End. This is because in my Excel sheet I wrote Period in one line and End Date in the next line. Please note that when I wrote the next line, that meant Next Line not next cell. The next step is to build our data adapter.

3. DataAdapter

StringBuilder stbQuery = new StringBuilder();
stbQuery.Append("SELECT * FROM [42560035$A1:F500]");
OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.Tostring(),con);

4. Filling DataSet

DataSet dsXLS = new DataSet() adp.Fill(dsXLS);

5. Binding Grid

After filling the data, now is the time is to see the data. For that, we can use DataGrid or you can write your dataset into an XML file by using the WriteXml method. In this example, I will be using DataGrid to display my data.

DataView dvEmp = new DataView(ds.Tables[0]); 
dataGrid1.DataSource = dvEml; 

That is all.

Closing Note

In my next article, I am going to show you how to post data in an Excel sheet.

History

• 30^th July, 2007: Initial post