title: OLE CALL to Excel with WHERE clause
body: <p>I am working on fixing a C# project which uses an Excel sheet with data definition to create a .fmt file used by a bulk insert in SQL Server.</p>
  
  <p>My issue is that the Excel files sometimes have blank rows at the bottom and the C# parser I am working on will detect a number of rows superior to the actual number of rows containint data definition.</p>
  
  <p>The fmt file therefore has a larger number of rows on its second line and bulk insert throws an exception when it reaches the bottom and tries to read on.</p>
  
  <p>For instance there are only 50 rows with data and 50 blank rows. The fmt file will have a 100 on its second line (the first line is for the SQL Server version). Line 3 to 52 are the 50 lines of data definition. When bulk insert tries to reach line 53, it returns a number of column exception.</p>
  
  <p>The C# parser uses Ace OleDB 12 to connection to the Excel 97 format file.</p>
  
  <p>The SQL is:</p>
  
  <pre><code>var commandText = string.Format("SELECT * FROM [{0}$]", SpreadSheetName);
  </code></pre>
  
  <p>I have tried to add a WHERE clause to the SQL code to only select rows with a non-empty "A" column, but this does  not work.</p>
  
  <pre><code>SELECT * FROM [{0}$] WHERE [A] &lt;&gt; ''
  </code></pre>
  
  <p>Is there a way the command text can be enhanced with some SQL code to only extract rows of data out of Excel where a certain condition is met?</p>