C# ADO.NET and Reading from Excel

  • Thread starter Thread starter morten
  • Start date Start date
M

morten

String sConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=c:\\test.xls;" +
@"Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection
(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand("SELECT *
FROM [sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

objConn.Close();

Jet can't recognize [sheet1$]!!
what's wrong?
 
Hi Morten,

In your connnection string, the Data Source is set as @"Data Source =
c:\\text.xls". This syntax is incorrect, because if you put a '@' before a
quotation mark, the C# compiler will consider everything in the quotation
mark to be the content of the string. So the path will be c:\\text.xls.
Please try to use "Data Source = c:\\test.xls" or @"DataSource =
c:\test.xls" instead. I think this might solve the problem.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------
| Content-Class: urn:content-classes:message
| From: "morten" <[email protected]>
| Sender: "morten" <[email protected]>
| Subject: C# ADO.NET and Reading from Excel
| Date: Thu, 25 Sep 2003 01:09:19 -0700
| Lines: 27
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcODPFJiSPWfco8BQCCAZIdP+R7wCw==
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62088
| NNTP-Posting-Host: TK2MSFTNGXA09 10.40.1.161
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| String sConnectionString =
| @"Provider=Microsoft.Jet.OLEDB.4.0;" +
| @"Data Source=c:\\test.xls;" +
| @"Extended Properties=Excel 8.0;";
|
| OleDbConnection objConn = new OleDbConnection
| (sConnectionString);
|
| objConn.Open();
|
| OleDbCommand objCmdSelect =new OleDbCommand("SELECT *
| FROM [sheet1$]", objConn);
|
| OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
|
| objAdapter1.SelectCommand = objCmdSelect;
|
| DataSet objDataset1 = new DataSet();
|
| objAdapter1.Fill(objDataset1, "XLData");
|
| objConn.Close();
|
| Jet can't recognize [sheet1$]!!
| what's wrong?
|
|
|
 
Hello again

They only way it can get this to work is if the excel
worksheet is named Sheet1 - then select * from [Sheet1$]
works fine. But if i rename the worksheet to e.g. Work
then select * from [Work$] results in an exception.

/Morten
-----Original Message-----
Hi

Please check the following url
http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodata
set.asp

HTH
Ravikanth[MVP]

-----Original Message-----
String sConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=c:\\test.xls;" +
@"Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection
(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand("SELECT *
FROM [sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

objConn.Close();

Jet can't recognize [sheet1$]!!
what's wrong?


.
Click to expand...
.
Click to expand...
 
My mistake :-(

That actually works fine.

The question is now: how to always read from the first
worksheet diregarding the worksheets name.

/Morten
-----Original Message-----
Hello again

They only way it can get this to work is if the excel
worksheet is named Sheet1 - then select * from [Sheet1$]
works fine. But if i rename the worksheet to e.g. Work
then select * from [Work$] results in an exception.

/Morten
-----Original Message-----
Hi

Please check the following url
http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/dv_vbcode/html/vbtskcodeexamplereadingexceldataintodat a
set.asp

HTH
Ravikanth[MVP]

-----Original Message-----
String sConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=c:\\test.xls;" +
@"Extended Properties=Excel 8.0;";

OleDbConnection objConn = new OleDbConnection
(sConnectionString);

objConn.Open();

OleDbCommand objCmdSelect =new OleDbCommand("SELECT *
FROM [sheet1$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = objCmdSelect;

DataSet objDataset1 = new DataSet();

objAdapter1.Fill(objDataset1, "XLData");

objConn.Close();

Jet can't recognize [sheet1$]!!
what's wrong?


.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top