Problem with connection string and ISAM

Question

I have the following problem. Have little application which read some data from excel file. Here is my connection string:

            @"Provider=Microsoft.ACE.OLEDB.12.0;"+
            @"Data Source=" + m_excelFileName + ";" +
            @"Extended Properties=Excel 12.0;"

When I use it everything is working great but data from first rows in my excel file is read as column name. I don't want it so I must change my connection string and add this:

           @"Provider=Microsoft.ACE.OLEDB.12.0;"+
           @"Data Source=" + m_excelFileName + ";" +
           @"Extended Properties=Excel 12.0;"+
           @"HDR=NO;";

I don't know why but then my reading function doesn't work and i have communicate that:

Could not find installable ISAM

What's going wrong? Thanks for any advise.

Answer 1

Try surrounding the extended properties in quotes...

"Provider=Microsoft.ACE.OLEDB.12.0;"+
"Data Source=" + m_excelFileName + ";" +
"Extended Properties=\"Excel 12.0;"+
"HDR=NO;\"";

and remove the "@" since you need to escape the quotes

Answer 2

From Microsoft support:

the provider names your fields F1, F2, etc. Because the Extended Properties string now contains multiple values, it must be enclosed in double quotes itself, plus an additional pair of double quotes to tell Visual Basic to treat the first set of quotes as literal values, as in the following example (where extra spaces have been added for visual clarity).

Support pilotcam's answer.

Good luck!

Answer 3

I don't know if I understanding well. I try piloctam's code but something is not good. Some quotes is not good. Maybe in one line:

@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_excelFileName + ";Extended Properties=Excel 12.0; HDR=NO;";

That is ok?