Solved

Problem in getting the data from excel to a dataset

Posted on 2008-10-22
3
509 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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