Solved

Problem in getting the data from excel to a dataset

Posted on 2008-10-22
3
513 Views
Last Modified: 2013-12-17
hello experts,

                I am using the below code to export excel 2007 to dataset.
but i am getting the below error :
The Microsoft Jet database engine could not find the object 'Sheet1$'.  
Make sure the object exists and that you spell its name and the path name correctly.

where it is going wrong ??? please help

urgent.. thanks.
string Filetype = FileUpload1.PostedFile.ContentType.ToString();
            if (Filetype == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strFilename + ";Extended Properties=\"Excel 8.0;\"";
 
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
                DataSet ExcelDataSet = new DataSet();
                myOleDbDataAdapter.Fill(ExcelDataSet, "[Sheet1$]");
 
                myOleDbDataAdapter = null;
 
                return ExcelDataSet; 
            }

Open in new window

0
Comment
Question by:srk1982
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Expert Comment

by:SameerJagdale
ID: 22774281
try this way:
 Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
0
 

Author Comment

by:srk1982
ID: 22774374
hi SameerJagdale,

i tried that...
I am getting this error now....

{System.Data.OleDb.OleDbException: External table is not in the expected format.
   at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
   at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at MenuManager_OperationalInfo.getExcelData(String strFilename) in d:\Applications\IOIF\MenuManager\OperationalInfo.aspx.cs:line 33}
0
 

Accepted Solution

by:
srk1982 earned 0 total points
ID: 22775047
Hi,

 problem is fixed now.

I used this.

                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Server.MapPath(strFilename) + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question