Solved

Problem in getting the data from excel to a dataset

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now