Read excel via ADO.NET with SQL Server commands?

Question

I can read XLS file with this code :

string path =@"c:\r\1.xlsx";
OleDbConnection MyConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + @"';HDR=Yes;Jet OLEDB:Engine Type=37");
OleDbDataAdapter MyCommand = new OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
DataSet DtSet = new DataSet();
MyCommand.Fill(DtSet);
...
...

However - when I enhance the query to include some SQL Server commands like

 select *,case when 1=1 then 'a' else 'b' end as rr  from [Sheet1$]

it goes BANG

I know that OLEDB is using access jet/ace behind the scenes.

How can use pure T-SQL query here?

Answer 1

You have to use IIF in querying excel

select *,
    IIF(1 = 1, 'a', 'b') as rr  
from [Sheet1$]

And, to create a multiple case statement, just nest them, like this:

select *,
    IIF(1 = 1, 'a', IIF( 2 = 2, 'c', 'b')) as rr  
from [Sheet1$]

As to whether you can use a pure MSSQL query, I do not believe that any connection that you can use with excel supports the CASE statement. So, you will have to use the above solution