Click here to Skip to main content
Click here to Skip to main content
Go to top

Fetching Data from Microsoft Excel using SQL

, 30 Jul 2007
Rate this:
Please Sign up or sign in to vote.
How to fetch 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 (Smile | :) . 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

  • 30th July, 2007: Initial post

License

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

Share

About the Author

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

Comments and Discussions

 
QuestionCongratulations Pinmemberrom32319-Feb-14 4:14 
QuestionCan I read data fromn Opened Excel file continuously in a loop? PinmemberMember 458580414-Jan-08 8:20 
GeneralRe: Can I read data fromn Opened Excel file continuously in a loop? Pinmembersmramesh15-Jan-08 12:11 
AnswerRe: Can I read data fromn Opened Excel file continuously in a loop? Pinmembergg423713-Nov-09 1:01 
GeneralFetching Data From MS Excel by SQL Pinmemberphillipw00728-Nov-07 21:20 
GeneralVB.net class available to do all this & more Pinmemberm_priour7-Aug-07 17:45 
GeneralArticle Font... PinmemberAnastasiosyal7-Aug-07 10:49 
GeneralSheet name unknown Pinmemberbitterbyte7-Aug-07 3:44 
AnswerRe: Sheet name unknown PinmemberRashutosh Paul7-Aug-07 6:48 
GeneralRe: Sheet name unknown Pinmemberbitterbyte7-Aug-07 22:04 
GeneralRe: Sheet name unknown [modified] Pinmemberdvang30-Jan-08 9:41 
Very helpful info! Thanks. I'm working in C#, and although I've got programming knowledge my db skills are pretty limited. I've got a simple working program that reads in an Excel file and (for now) just writes the info back out to console. However, I need to be able to handle a variety of Excel workbooks, which includes unknown sheet names. I'm not sure how your data structure is set up, and therefore how your code would integrate into mine.
 
How would I go about inserting your sheet collection? Thanks!
 
[edit: I figured out that I hadn't added Microsoft Excel to my references of my project, hence I was getting errors when calling Excel.Xxxx . Of course, I hadn't realized that the OleDb does not support Excel files (or any database) with multiple data types (ie strings and numbers) in the same column. Unfortunately, the .xls files I need to work with have mixed data in the columns, so I have had to switch to a different method to access the Excel files. Thanks though, your information is still useful.]
 
modified on Thursday, January 31, 2008 7:15:36 PM

QuestionCan we do system programming usng visual studio.net Pinmemberworld_worst_employee_WWE3-Aug-07 0:09 
GeneralGreat Article PinmemberStefan Scholte30-Jul-07 21:26 
Questionneed more clearfication PinmemberRohit Mahajan30-Jul-07 21:10 
QuestionRe: need more clearfication PinmemberRashutosh Paul31-Jul-07 3:40 
AnswerRe: need more clearfication PinmemberRohit Mahajan31-Jul-07 3:44 
AnswerRe: need more clearfication PinmemberRashutosh Paul2-Aug-07 7:45 
AnswerRe: need more clearfication PinmemberRashutosh Paul2-Aug-07 7:50 
GeneralRe: need more clearfication Pinmemberphillipw00728-Nov-07 21:22 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 30 Jul 2007
Article Copyright 2007 by Rashutosh Paul
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid