Re: Converting Excel File to XML - C# Discussion Boards

13-Aug-07 9:47

I am trying to convert Excel file to XML using C#. And the name of the Excel file is Contacts.xls and the SheetName is Contacts.

OleDbcmdSource.CommandText = "SELECT * FROM [" + Contacts + "$]";

The above code is where it gives me an error. I get an error that the name Contacts does not exist in the current context.

This is my Web.Config code: <connectionstrings>
<add name="ExcelConnectionString" connectionstring="Data Source=C:\Contacts.xls;Extended Properties=Excel 8.0;HDR=No;IMEX=1;Provider=Microsoft.Jet.OLEDB.4.0;">

pmarfleet13-Aug-07 10:09

13-Aug-07 10:09

Your code should read:

OleDbcmdSource.CommandText = "SELECT * FROM [Contacts$]";

'Contacts' is being used like a variable. If you have not declared a variable called 'Contacts' in your code, the compiler is going to throw an error.

Paul

T4AMD13-Aug-07 10:24

13-Aug-07 10:24

Hi Paul,
Thank You so much for replying. Now the Contacts error does not show up but
I get an error on the following code:
OleDbadpSource.Fill(dsSource);//The error says OleDbException was unhandled by user code. Don't know what that means

My full code is as follows:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
BindData();
}

private void BindData()
{

DataSet dsLoginSourceGrid = GetLoginDataSet();

GridView1.DataSource = dsLoginSourceGrid.Tables[0];
GridView1.DataBind();

}

private string GetConnectionString()
{
string strConnectionString = ConfigurationManager.ConnectionStrings["ExcelConnectionString"].ToString();
return strConnectionString;

}

private DataSet GetLoginDataSet()
{
string ConnectionString = GetConnectionString();
OleDbConnection sqlconSource = new OleDbConnection(ConnectionString);

OleDbCommand OleDbcmdSource = new OleDbCommand();
OleDbcmdSource.Connection = sqlconSource;
OleDbcmdSource.CommandType = CommandType.Text;

OleDbcmdSource.CommandText = "SELECT * FROM [Contacts$]";

OleDbDataAdapter OleDbadpSource = new OleDbDataAdapter(OleDbcmdSource);
OleDbadpSource.SelectCommand = OleDbcmdSource;

DataSet dsSource = new DataSet();
OleDbadpSource.Fill(dsSource);

return dsSource;

}

}

pmarfleet13-Aug-07 10:37

13-Aug-07 10:37

An exception has been raised when you try to fill the DataSet.

What are the details of the error?

The error may be caused because you don't appear to be opening your connection. Add a line 'sqlconSource.Open();' after the line where you create an instance of your connection object. You may also want to consider wrapping your connection in a 'using' block so that it is closed and disposed automatically when it goes out of scope.

Paul

T4AMD13-Aug-07 10:42

13-Aug-07 10:42

It tells me "Could not find installable ISAM" Further "OleDbException was unhandled by user code"

View Detail tells me System.Data.OleDb.OleDbException {"Could not find installable ISAM."}

pmarfleet13-Aug-07 10:46

13-Aug-07 10:46

What is the connection string you are using? I think the information in your original post got truncated...

Paul

T4AMD13-Aug-07 10:49

13-Aug-07 10:49

<connectionstrings>
<add name="ExcelConnectionString" connectionstring="Data Source=C:\Contacts.xls;Extended Properties=Excel 8.0;HDR=No;IMEX=1;Provider=Microsoft.Jet.OLEDB.4.0;">

This is my Connection String from Web.Config file

pmarfleet13-Aug-07 10:57

13-Aug-07 10:57

This doesn't look right. Your connection string should look something like this:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

Have a look at the following link to get more help with creating connection strings to Excel files:

http://www.connectionstrings.com/?carrier=excel

Paul

T4AMD13-Aug-07 11:03

13-Aug-07 11:03

<connectionstrings>
<add name="ExcelConnectionString" connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Contacts.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=2"
="">

I changed it as it shows above. But the error is still there.

pmarfleet13-Aug-07 11:15

13-Aug-07 11:15

There still must be something wrong with your connection string.

Have you definitely got the syntax right?

Paul

T4AMD13-Aug-07 11:26

13-Aug-07 11:26

What is this thing called DSN. Do I need to enter the excel spreadsheet in a DSN to make this thing work?

T4AMD13-Aug-07 11:39

13-Aug-07 11:39

I cannot find the syntax error though.

T4AMD13-Aug-07 13:15

13-Aug-07 13:15

http://www.negia.net/~vbbook/faq/isam.htm[^]

Do you think I should download a DLL file from this link??

pmarfleet13-Aug-07 21:52