[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Problem in getting the data from excel to a dataset

Posted on 2008-10-22
3
Medium Priority
?
516 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
  • 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

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!
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

591 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