ADO.NET connection string for Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need a good working example of reading Excel using ADO.NET. Here is what I
have but it does not work. The problem seems to be how to escape the quotes
in the Extended properties. Any suggestions appreciated.

StringBuilder sb = new StringBuilder();
string strConn = "";
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
sb.Append("DataSource=").Append(strFileName).Append(";").Append("Extended
Properties=");
sb.Append("\"").Append("\"").Append("Excel
8.0;").Append("HDR=Yes").Append("\"").Append("\"").Append("\"");
strConn = sb.ToString();
 
This is the error I get:

Format of the initialization string does not conform to specification
starting at index 93." here is my connection string:

string strConn = "";
StringBuilder sb = new StringBuilder();
string strFileName = @"C:\FolderToWatch\xData.xls";
string qt = "\"\"";
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
sb.Append("DataSource=").Append(strFileName).Append(";");
sb.Append("Extended Properties=").Append(qt);
sb.Append("Excel 8.0;HDR=Yes;").Append(qt);


The string looks like this when I do console.writeline:
Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\FolderToWatch\xData.xls;Extended
Properties=""Excel 8.0;HDR=Yes;""


Why is not working then?




Miha Markic said:
Hi,

As usual, you should check out this fine page:
http://www.connectionstrings.com/

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Mori said:
Need a good working example of reading Excel using ADO.NET. Here is what
I
have but it does not work. The problem seems to be how to escape the
quotes
in the Extended properties. Any suggestions appreciated.

StringBuilder sb = new StringBuilder();
string strConn = "";
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
sb.Append("DataSource=").Append(strFileName).Append(";").Append("Extended
Properties=");
sb.Append("\"").Append("\"").Append("Excel
8.0;").Append("HDR=Yes").Append("\"").Append("\"").Append("\"");
strConn = sb.ToString();
Click to expand...
Click to expand...
 
Hi Mori,

I guess the connection string listed at www.connectionstrings.com is for use
with VB.NET (thus the double quotes).
Use single quotes instead.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Mori said:
This is the error I get:

Format of the initialization string does not conform to specification
starting at index 93." here is my connection string:

string strConn = "";
StringBuilder sb = new StringBuilder();
string strFileName = @"C:\FolderToWatch\xData.xls";
string qt = "\"\"";
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
sb.Append("DataSource=").Append(strFileName).Append(";");
sb.Append("Extended Properties=").Append(qt);
sb.Append("Excel 8.0;HDR=Yes;").Append(qt);


The string looks like this when I do console.writeline:
Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\FolderToWatch\xData.xls;Extended
Properties=""Excel 8.0;HDR=Yes;""


Why is not working then?




Miha Markic said:
Hi,

As usual, you should check out this fine page:
http://www.connectionstrings.com/

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Mori said:
Need a good working example of reading Excel using ADO.NET. Here is
what
I
have but it does not work. The problem seems to be how to escape the
quotes
in the Extended properties. Any suggestions appreciated.

StringBuilder sb = new StringBuilder();
string strConn = "";
sb.Append("Provider=Microsoft.Jet.OLEDB.4.0;");
sb.Append("DataSource=").Append(strFileName).Append(";").Append("Extended
Properties=");
sb.Append("\"").Append("\"").Append("Excel
8.0;").Append("HDR=Yes").Append("\"").Append("\"").Append("\"");
strConn = sb.ToString();
Click to expand...
Click to expand...
Click to expand...
 
Back
Top