将 Excel 文件逐行导入到 SQL Server

Question

我正在将 Excel 文件（仅大约 1000 条记录）导入到专用 SQL Server 数据库。由于我需要处理来自 Excel 的传入数据（每行添加 GUID，一些数据转换），因此我想逐行进行操作，并且不想批量导入（不过，我对事务没有任何反对意见）。

我对如何正确地做到这一点感到困惑。我可以使用带有参数的 SQLCommand，如下所示：

SqlCommand sqlCommand = new SqlCommand("insert into TestTable(GUID,Name,Pricing) values(@GUID,@Name,@Pricing)", sqlConn);
foreach (DataRow dr in ds.Tables[0].Rows) //<-- this is my Excel file to iterate through
{
 sqlCommander.Parameters.Clear();
 String refGUID = Guid.NewGuid().ToString();
 sqlCommander.Parameters.AddWithValue("GUID", refGUID);
 sqlCommander.Parameters.AddWithValue("Name", dr.ItemArray[0]);
 sqlCommander.Parameters.AddWithValue("Pricing", dr.ItemArray[1]);
 sqlCommander.ExecuteNonQuery();
}

或者我可以使用“连接”模式，如下所示：

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT GUID, Name, Pricing FROM TestTable", sqlConn);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

DataSet myDataSet = new DataSet();
dataAdapter.Fill(myDataSet, "TestTable");
foreach (DataRow dr in ds.Tables[0].Rows) //<-- this is my Excel file to iterate through
{
 DataRow row = myDataSet.Tables[0].NewRow();
 row["GUID"] = refGUID;
 row["Name"] = dr.ItemArray[0];
 row["Pricing"] = dr.ItemArray[1];
 myDataSet.Tables[0].Rows.Add(row);
 dataAdapter.Update(myDataSet);
}

现在我的问题如下：

1. 发送一个命令是否更好？每行的 INSERT 命令（即 SqlCommand 方法）还是填充特殊的 DataSet（第二种方法）更好？我想向 SQL 服务器进行 1000 次插入是相当“愚蠢”的？
2. dataAdapter.Update(myDataSet) <-- 我应该在迭代所有 Excel 行或每一行之后执行此操作（如上面的示例代码所示），这会神奇地创建一个事务吗？
3. 我应该使用哪种方法？ 还有 LINQ to SQL - 为什么不使用它（也许性能，因为另一层）？
4. 当读取 Excel 文件时发生一些错误时，DataSet 会发生什么情况 - 更新是否仍推送到 SQL Server 还是所有内容都丢失了？

---

In short: I want to import an Excel file to an SQL server row-by-row whilst making changes on the data to be imported (and I don't want to use SSIS packages [because besides data conversion I am doing a lot more with the Excel file like importing it to Sharepoint and firing of Workflows] or BizTalk)
»» How to do that beautifully?

---

In the end I went ahead and bought Aspose Cells. Aspose has a very nice set of tools at their disposal.

Answer 1

您提到不想使用 SSIS - 但您考虑过 SqlBulkCopy 吗？那么除了 .NET 之外不需要任何东西，但您仍然可以使用最快/最直接的导入。

这将接受一个数据表，因此您可以在数据表中准备数据，然后触发。交易可选择支持 IIRC。对于更大的数据，您还可以实现 IDataReader 以提供完全流式上传（同时仍在传输中处理每一行）。

Answer 2

鉴于您对问题的描述：“我想将 Excel 文件逐行导入到 SQL 服务器，同时对要导入的数据进行更改” - SSIS 是完成这项工作的完美工具。

使用 SQL Server Integration Services SSIS 导入 Excel 数据时出现 unicode 和非 unicode 数据问题

Answer 3

数据集可能最终会将 INSERT 语句发送到服务器，因此在我看来，最好只发送不包含数据集的 INSERT 语句。您还可以对流程进行更多控制，例如检查各个行是否有错误、日志记录等。

Answer 4

您可以将处理后的数据表转换为 XML，并将其传递给 Sql 服务器中的存储过程（在一个查询中），然后让存储过程解析 XML 以创建记录。

Answer 5

INSERT INTO [dbo].[TableName]
           ([ColumnName1]
           ,[ColumnName2])
)
SELECT [ColumnName1]
           ,[ColumnName2]

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source= PathToFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]