我的工作确定一个C＃项目，它使用一个Excel工作表数据定义创建在SQL Server中使用的批量插入一个.FMT文件。

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.

我的问题是，Excel文件，有时有空白行底部和C＃语法分析器我正在将检测到的行数高于实际的行数containint数据定义。

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.

FMT文件因此具有在其第二行的行的数量较多和批量插入抛出当它到达底部，并尝试读取上的异常。

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.

比如有50只与数据行和50个空行。 FMT文件会对其第二线100（第一线是用于SQL Server版本）。 3号线到52是50行数据定义的。当批量插入试图达到53行，它返回一个数列的异常。

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.

C＃的解析器使用王牌OLEDB 12连接到Excel 97格式的文件。

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

在SQL是：

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

我试图添加一个WHERE子句的SQL code，只选择一个非空A柱行，但这是行不通的。

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] <> ''

有没有一种方法，命令文本可以与一些SQL code只提取行数据导出Excel，其中满足一定的条件？

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?

推荐答案

如果您的S preadsheet有头（我的意思不是Excel中的A，B等列标题），那么你就可以使用这些中的条件。你需要的时候你实例在连接字符串中指定它的的OleDbConnection 。

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;\"";

然后就可以沿行执行SQL

Then you can execute SQL along the lines of

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

如果您的S preadsheet没有头，你仍然可以使用WHERE子句，但你必须引用的列由F1，F2等，并设置连接字符串不使用头文件

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] <> ''"