OLE CALL to Excel with WHERE clause

Question

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.

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.

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.

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.

The C# parser uses Ace OleDB 12 to connection to the Excel 97 format file.

The SQL is:

var commandText = string.Format("SELECT * FROM [{0}$]", SpreadSheetName);

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.

SELECT * FROM [{0}$] WHERE [A] <> ''

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?

Subtract

Can you show some code? What object do you read the data in too? You could use a DataReader to read line by line and ignre those that are null. – Southey

Answer 1

If your spreadsheet has headers (I don't mean Excel's "A", "B", etc. column headers), then you can use those in the conditions. You'll need to specify it in the connection string when you instantiate your OleDbConnection.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";

Then you can execute SQL along the lines of

string SQL = "SELECT * FROM [Sheet1$] WHERE [Name] <> ''";

If your spreadsheet does not have headers you can still use a WHERE clause, but you must reference the columns by "F1", "F2", etc. and set your connection string to not use headers

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=\"Excel 8.0;HDR=NO;\"";
string SQL = "SELECT * FROM [Sheet1$] WHERE [F1] <> ''"

Typewriter

Unfortunately, I did try this exact syntax already before posting and it does not work. – Subtract

@Subtract Perhaps you could try reading in all the data and manipulating it in a DataTable, as Derek suggested. – Typewriter

I managed to make it work by making sure the spreadsheet had headers. It will not take the generic column name, unfortunately. – Subtract