Import Excel worksheet in ASP.NET using OWC

Can anyone provide an explanation or example of importing an Excel worksheet to a dataset using OWC
in ASP.NET?

I'm successfully exporting to an Excel workbook (XML format).  On the import side I'd like to import from an Excel in native Excel format, not XML.

Thanks very much to anyone who can help.
dawesleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sachintana DissanayakeSenior Web DeveloperCommented:
You can use OleDbConnection with the connection string like this,
     Provider=Microsoft.Jet.OLEDB.4.0;Data Source=excelFile.xls;Extended Properties=Excel 10.0;


Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("excelFile.xls")  & ";Extended Properties=Excel 10.0;"
 
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
 
' Create new OleDbCommand to return data from worksheet.
Dim objCmdSelect As New OleDbCommand("SELECT * FROM [Sheet1$]", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
 
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "ExcelData")
 
objConn.Close()

Open in new window

0
dawesleyAuthor Commented:
Thanks for replying, but I'm getting the error "Could not find installable ISAM"
on the "objConn.Open()" line.

I've tried changing the connection string syntax a number of times as per
hits I found on this error, but I always get the same error.

My latest attempt is:

      Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                        Server.MapPath("excelFile.xls") & _
                                        ";Extended Properties=Excel 11.0;"

      Dim objConn As New OleDbConnection(sConnectionString)
      objConn.Open()
0
Sachintana DissanayakeSenior Web DeveloperCommented:
If this is a web application, IUSR_ should have access to the folder where the excel file located.
What;s the version of excel file that you are using?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

dawesleyAuthor Commented:
I'm developing on my PC, which has Excel 2003 (v.11).  I have administrative rights on my PC.
The commercial web server on which the application runs doesn't have MS Office installed
for obvious security reasons.  It uses only MS Office Web Components.

0
Sachintana DissanayakeSenior Web DeveloperCommented:
Not administrative rights.
You have to give permission for IUSR_ (ie. ASP.NET user).

You don't need to install MS office in the server.
First get this work on the development PC.
0
dawesleyAuthor Commented:
Sorry.  I didn't give you the whole picture.
In IIS, I have read and write permissions enabled and
anonymous access turned on for the directory.
0
Sachintana DissanayakeSenior Web DeveloperCommented:
Go to the directory that you have the excel file and grant permission for IUSR_ (ie. ASP.NET user).
0
dawesleyAuthor Commented:
In the file system, I pulled up the properties on the directory
and, in the SECURITY tab, gave every user and group in the list,
including all 'IUSR_' types, full access to the directory.

I'm still getting the same error.
0
dawesleyAuthor Commented:
I should also have mentioned that I've successfully imported a number
of comma separated text files from this folder and from subfolders
under it.
0
Sachintana DissanayakeSenior Web DeveloperCommented:
Please change connection string as below.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Where, "HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

http://www.connectionstrings.com/?carrier=excel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawesleyAuthor Commented:
Following your advice, I changed my connection string to:

      Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                        Server.MapPath("ExcelFile.xls") & _
                                        ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

It worked!

Obviously I have to add code so I can see the results and I have to test this on the
remote server, but I'm ready to accept the solution and award the points.

I have one more question, but I'm not holding up accepting your solution based on your response.

Is there a way to get the name of the first worksheet in the workbook or to import with the
first worksheet implied instead of specifying the name?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.