Error when reading excel spreadsheet

Posted by Mongz under C# on 9/16/2009 | Views : 2989 | Status : [Member] | Replies : 6
Hi guys, here im trying to read excel 2007 spreadsheet file. I have this code which is similar to a piece of code i picked here, i just changed few things to accommodate my needs. When im running the program i recieve these 2 errors :
1. could not find Installable ISAM.
2. and 2nd error is it cant find table0.

Please review this code then tell me where do i went wrong.

//I declare the connection string
public string GetConnection()
{
string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data\GraduatesList.xlsx;Extended Properties="+"Excel 12.0 Xml;HDR=YES";
return constr;
}

public DataTable ReadRecords()
{
DataSet dsUserData = new DataSet();
try
{
string query = "Select ID,Surname From [Sheet1$]";

using (OleDbConnection Connection = new OleDbConnection(GetConnection()))
{
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dsUserData, "UserData");
DataAdapter.AcceptChangesDuringFill = false;
DataAdapter.Dispose();
Connection.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return dsUserData.Tables[0];
}




Responses

Posted by: Rtpharry on: 9/16/2009 [Member] [MVP] Bronze

Up
0
Down
I think the first error is causing the second error so I will concentrate on that.

This thread says that the error is caused by a syntax error in your connection string:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/0c8191b5-200d-4f54-ba30-fc3e93432b7a

From looking at their code and your code I noticed that you have "xml" in the extended properties.

Perhaps try changing the line to:

string constr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Data\GraduatesList.xlsx;Extended Properties=Excel 12.0;HDR=YES";

Mongz, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 9/16/2009 [Member] [Moderator] Silver

Up
0
Down
Hi Mongz-

For Excel Opertaion like Read Insert Update read my post on :

http://lakhangarg.blogspot.com/2009/07/read-insert-and-update-data-into-excel.html

Thanks & Regards
Lakhan pal Garg
http://lakhangarg.blogspot.com

Mongz, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rtpharry on: 9/17/2009 [Member] [MVP] Bronze

Up
0
Down
Lakhan, thats a good blog post, is it the same commands to read excel in the new xml formats (xlsx)?

Mongz, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lakhangarg on: 9/17/2009 [Member] [Moderator] Silver

Up
0
Down
Yes-

Same will work in case of xlsx format too...

thanks for your appreciation... you can make that post more good by posting your queries or by posting some useful information there so can user can easily find info at one place...

Thanks & Regards
Lakhan Pal Garg

Mongz, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rtpharry on: 9/17/2009 [Member] [MVP] Bronze

Up
0
Down
I haven't ever used this in code but I am curious now that this has come up twice in two days.

Do you know what the HDR=Yes signifies?

Mongz, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mongz on: 9/19/2009 [Member] Starter

Up
0
Down
Thanks to all who take part, the problem has been resolved, i'll paste the solution. Infact even the above code is correct, it was a logic error

Mongz, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response