2011-12-12

Read excel to dataset

This sample show how to read data from excel file by using Microsoft.Jet.OLEDB.

More information about excel connectionstring see http://www.connectionstrings.com/excel

Let's sample code.

Private Function ReadExcelFile(ByVal filePath As String, ByVal fileName As String) As DataSet
'' Excel 2003
Dim sConn As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", filePath + fileName)
sConn += ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"""

'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Dim sSql As String = "SELECT * FROM [Sheet1$]"
'Select excel range command
' Dim sSql As String = "SELECT * FROM [Sheet1$A1:D3]"

Dim pFile As New FileInfo(fileName)
If (pFile.Extension.ToLower() = ".xlsx") Then
' Excel 2007
sConn = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", filePath + fileName)
sConn += ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
End If


Dim ds As New DataSet
Dim pConn As New OleDbConnection(sConn)
Try
pConn.Open()
Dim pCmd As New OleDbCommand()
pCmd.Connection = pConn
pCmd.CommandText = sSql

Dim pAdp As New OleDbDataAdapter()
pAdp.SelectCommand = pCmd

pAdp.Fill(ds)
Catch ex As Exception
Return Nothing
Finally
pConn.Close()
End Try

Return ds
End Function

Note
If found error Could not find installable ISAM that mean your connectionstring was wrong.


19 comments:

Anonymous said...

Excellent post! I think you've encapsulated the mission of this blog and our challenge.

Anonymous said...

Outstanding story there. What occurred after? Thanks!
Also visit my website : C#

Anonymous said...

Keep on working, great job!
Feel free to visit my site ; penalista napoli

Anonymous said...

Usually I do not learn post on blogs, however I wish to say that this write-up very pressured me to try and do so!

Anonymous said...

I'm impressed, I have to admit. Rarely do I come across a blog that's both equally educative and interesting, and let me tell you, you have hit
the nail on the head. The issue is something that not enough people are speaking intelligently about.
I am very happy I came across this during my hunt for something relating to this.
Feel free to visit my page ... scripting languages

Anonymous said...

I may certainly not have the ability to keep every details with regard to a while after bring all of this in.

Anonymous said...

long time no see kevin i got it from a friend so this is there web address
and some info , there there most competitive in the game , mention mrs nolsfod give out there number

Anonymous said...


I am searching for things to improve my website! I assume its ok to use some of the ideas!

Anonymous said...

Excellent goods from you, man. I have understand your stuff previous to and you are just extremely magnificent.
I really like what you have acquired here, certainly like what
you're stating and the way in which you say it. You make it enjoyable and you still care for to keep it wise. I can't wait to read much more from you.
This is actually a wonderful website.
My webpage email Address lists

Anonymous said...


If possible, as you gain expertise, would you mind updating your web page with more details?

Anonymous said...

Thank you for another great article. The place else could anyone get
that type of information in such an ideal method of writing?

I have a presentation subsequent week, and I am on the look for such information.
Stop by my webpage Asset Management Software

Anonymous said...


I've saved it within my google bookmarks.

morrison said...

This sample show how to read data from excel file by using Microsoft.Jet.OLEDB.Thanks for sharing it.

.net Programmer

Anonymous said...

What's Taking place i am new to this, I stumbled upon this I've found It absolutely
helpful and it has aided me out loads. I'm hoping to give a contribution & aid other users like its aided me. Good job.
Feel free to surf my webpage :: Costume idea

Anonymous said...

I believe what you posted was actually very logical.
But, think on this, suppose you were to create a awesome post title?
I ain't suggesting your content is not good, but suppose you added a post title that makes people desire more? I mean "Read excel to dataset" is kinda plain. You might glance at Yahoo's home page and see how they create article titles to
get viewers interested. You might add a video or
a picture or two to get readers excited about what you've written. Just my opinion, it could bring your posts a little bit more interesting.

my web page; www.sra411.com

Anonymous said...

Good day very cool web site!! Man .. Beautiful .. Wonderful .
. I will bookmark your website and take the feeds additionally?
I am happy to search out a lot of useful information here within the post,
we want work out extra strategies in this
regard, thanks for sharing. . . . . .

My web page :: http://www.goldenrat.com/an-introduction-to-essential-factors-of-modcloth-coupon/

Anonymous said...

Howdy, i read your blog occasionally and i own a similar one and i was just curious if you get a lot of spam comments?
If so how do you reduce it, any plugin or anything you can
advise? I get so much lately it's driving me crazy so any help is very much appreciated.

Review my homepage ... Modcloth Coupon

Anonymous said...

This information is worth everyone's attention. How can I find out more?

Also visit my website :: http://code6studio.com/?P=6

Anonymous said...

Pretty section of content. I just stumbled upon your website and in
accession capital to assert that I acquire in fact enjoyed account your
blog posts. Any way I'll be subscribing to your augment and even I achievement you access consistently quickly.

My web blog :: Connor