Click here to Skip to main content
15,033,303 members
Articles / Productivity Apps and Services / Microsoft Office
Posted 30 Jul 2007


56 bookmarked

Fetching Data from Microsoft Excel using SQL

Rate me:
Please Sign up or sign in to vote.
4.34/5 (13 votes)
30 Jul 2007CPOL3 min read
How to fetch data from Microsoft Excel using SQL


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.


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.


  • 30th July, 2007: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rashutosh Paul
Web Developer
India India
Working with Logica Pvt. Ltd as a Tech Lead.

Comments and Discussions

QuestionCongratulations Pin
rom32319-Feb-14 4:14
Memberrom32319-Feb-14 4:14 
QuestionCan I read data fromn Opened Excel file continuously in a loop? Pin
smramesh14-Jan-08 8:20
Membersmramesh14-Jan-08 8:20 
GeneralRe: Can I read data fromn Opened Excel file continuously in a loop? Pin
smramesh15-Jan-08 12:11
Membersmramesh15-Jan-08 12:11 
GeneralFetching Data From MS Excel by SQL Pin
phillipw00728-Nov-07 21:20
Memberphillipw00728-Nov-07 21:20 class available to do all this & more Pin
m_priour7-Aug-07 17:45
Memberm_priour7-Aug-07 17:45 
GeneralArticle Font... Pin
Anastasiosyal7-Aug-07 10:49
MemberAnastasiosyal7-Aug-07 10:49 
GeneralSheet name unknown Pin
bitterbyte7-Aug-07 3:44
Memberbitterbyte7-Aug-07 3:44 
AnswerRe: Sheet name unknown Pin
Rashutosh Paul7-Aug-07 6:48
MemberRashutosh Paul7-Aug-07 6:48 
GeneralRe: Sheet name unknown Pin
bitterbyte7-Aug-07 22:04
Memberbitterbyte7-Aug-07 22:04 
GeneralRe: Sheet name unknown [modified] Pin
dvang30-Jan-08 9:41
Memberdvang30-Jan-08 9:41 
QuestionCan we do system programming usng visual Pin
world_worst_employee_WWE3-Aug-07 0:09
Memberworld_worst_employee_WWE3-Aug-07 0:09 
GeneralGreat Article Pin
Stefan Scholte30-Jul-07 21:26
MemberStefan Scholte30-Jul-07 21:26 
Questionneed more clearfication Pin
Rohit Mahajan30-Jul-07 21:10
MemberRohit Mahajan30-Jul-07 21:10 
QuestionRe: need more clearfication Pin
Rashutosh Paul31-Jul-07 3:40
MemberRashutosh Paul31-Jul-07 3:40 
AnswerRe: need more clearfication Pin
Rohit Mahajan31-Jul-07 3:44
MemberRohit Mahajan31-Jul-07 3:44 
AnswerRe: need more clearfication Pin
Rashutosh Paul2-Aug-07 7:45
MemberRashutosh Paul2-Aug-07 7:45 
AnswerRe: need more clearfication Pin
Rashutosh Paul2-Aug-07 7:50
MemberRashutosh Paul2-Aug-07 7:50 
GeneralRe: need more clearfication Pin
phillipw00728-Nov-07 21:22
Memberphillipw00728-Nov-07 21:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.