OLE CALL in Excel with a WHERE clause

Question

I am working on a fix for a C # project that uses an Excel worksheet with data definition to create the .fmt file used by bulk insert in SQL Server.

My problem is that Excel files sometimes have empty lines at the bottom, and the C # handler I'm working on finds a series of lines that exceed the actual number of lines containing the data definition.

Therefore, the fmt file has a larger number of lines on its second line, and the bulk insert throws an exception when it reaches the bottom and tries to read.

For example, there are only 50 rows of data and 50 blank lines. The fmt file will have 100 on the second line (first line for the version of SQL Server). Lines 3 through 52 are 50 lines of data definition. When a massive insert tries to reach row 53, it returns the number of column exceptions.

The C # parser uses Ace OleDB 12 to connect to an Excel 97 file.

SQL:

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

I tried adding a WHERE clause to the SQL code to select only rows with a non-empty column "A", but this will not work.

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

Is there a way that command text can be improved with some SQL code to retrieve only rows of data from Excel where a certain condition is met?

Answer 1

If your table has headers (I do not mean the headers of the Excel headers "A", "B", etc.), you can use them in conditions. You need to specify it in the connection string when you create the OleDbConnection instance.

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

Then you can execute SQL row by row

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

If your table does not have headers, you can still use the WHERE clause, but you must reference the columns with "F1", "F2", etc. and set the connection string so as not to 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] <> ''"