C# 将 csv 文件/excel 导入 sql 数据库 asp.net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/489620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
import csv file/excel into sql database asp.net
提问by kiev
I am starting a project with asp.net visual studio 2008 / SQL 2000 (2005 in future) using c#.
我正在使用 c# 使用 asp.net Visual Studio 2008/SQL 2000（未来 2005）启动一个项目。
The tricky part for me is that the existing DB schema changes often and the import files columns will all have to be matched up with the existing db schema since they may not be one to one match on column names. (There is a lookup table that provides the tables schema with column names I will use)
对我来说棘手的部分是现有的 DB 模式经常更改，导入文件列都必须与现有的 db 模式匹配，因为它们在列名上可能不是一对一的匹配。（有一个查找表提供了我将使用的列名的表模式）
I am exploring different ways to approach this, and need some expert advice. Is there any existing controls or frameworks that I can leverage to do any of this?
我正在探索不同的方法来解决这个问题，需要一些专家的建议。是否有任何现有的控件或框架可以用来做这些？
So far I explored FileUpload .NET control, as well as some 3rd party upload controls to accomplish the upload such as SlickUploadbut the files uploaded should be < 500mb
到目前为止，我探索了 FileUpload .NET 控件，以及一些 3rd 方上传控件来完成上传，例如SlickUpload但上传的文件应该 < 500mb
Next part is reading of my csv /excel and parsing it for display to the user so they can match it with our db schema. I saw CSVReaderand others but for excel its more difficult since I will need to support different versions.
下一部分是读取我的 csv /excel 并解析它以显示给用户，以便他们可以将它与我们的数据库模式相匹配。我看到了CSVReader和其他人，但对于 excel 它更困难，因为我需要支持不同的版本。
Essentially The user performing this import will insert and/or update several tables from this import file. There are other more advance requirements like record matching but and preview of the import records, but I wish to get through understanding how to do this first.
本质上，执行此导入的用户将从该导入文件插入和/或更新多个表。还有其他更高级的要求，例如记录匹配但是和导入记录的预览，但我希望首先了解如何执行此操作。
Update: I ended up using csvReader with LumenWorks.Framework for uploading the csv files.
更新：我最终使用带有 LumenWorks.Framework 的 csvReader 来上传 csv 文件。
采纳答案by kiev
I am using csvReader from LumenWorks.Framework for uploading and importing the csv files into a sql table and a DataTable in memory which I create based on the columns imported.
我正在使用 LumenWorks.Framework 中的 csvReader 将 csv 文件上传和导入到我根据导入的列创建的内存中的 sql 表和 DataTable 中。
I also have the user map the fields in the ui and proceed to validate and prepare the data for import by labeling each record as insert/update/error. I then create/populate strongly typed DataSet for each table that will be affected and build the insert/update queries for Enterprise Library UpdateDataset() method.
我还让用户映射 ui 中的字段，并通过将每条记录标记为插入/更新/错误来验证和准备要导入的数据。然后，我为每个将受到影响的表创建/填充强类型数据集，并为企业库 UpdateDataset() 方法构建插入/更新查询。
Then I submit the transaction to insert/update the database. –
然后我提交事务以插入/更新数据库。——
The mapping is a grid with 4 columns. ( import field name, destination table, destination field name, ignore, match status), with destination table and field names being selects which refresh based on table selected. I dynamically populate the selects. Initially select combo is populated with 1 value if the match is found, or please select if it isn't. ignore allows user to ignore the field. match status is if a field was auto-mapped
映射是一个有 4 列的网格。（导入字段名称、目标表、目标字段名称、忽略、匹配状态），目标表和字段名称是根据所选表选择刷新的。我动态填充选择。如果找到匹配项，则最初选择组合填充 1 个值，否则请选择。ignore 允许用户忽略该字段。匹配状态是一个字段是否被自动映射
回答by Antony Perkov
You can easily create an IDataReader over an Excel or CSV file (see http://support.microsoft.com/kb/316934).
您可以通过 Excel 或 CSV 文件轻松创建 IDataReader（请参阅http://support.microsoft.com/kb/316934）。
Are you using SQL Server as your database engine? If so, you can use the SqlBulkCopy class (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx) to efficiently take your IDataReader, map columns as appropriate, and store the results in your database.
您是否使用 SQL Server 作为数据库引擎？如果是这样，您可以使用 SqlBulkCopy 类 ( http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx) 有效地获取您的 IDataReader、适当地映射列并存储结果在您的数据库中。
回答by HectorMac
I suspect there may exist some robust and flexible tools to help you with this potentially very complex application that hopefully someone will point you to.
我怀疑可能存在一些强大而灵活的工具来帮助您处理这个可能非常复杂的应用程序，希望有人能指点您。
In the mean time, here is a function I have found useful to covert an excel file to a DataTable. This version only looks for the first worksheet. It might serve as a starting point. To to something similar with csv files should only require modifying the connection string.
同时，我发现这里有一个函数可以将 excel 文件转换为 DataTable。此版本仅查找第一个工作表。它可以作为一个起点。与 csv 文件类似的内容应该只需要修改连接字符串。
public static DataTable GetDataTableFromExcel(string SourceFilePath)
{
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + SourceFilePath + ";" +
"Extended Properties=Excel 8.0;";
using (OleDbConnection cn = new OleDbConnection(ConnectionString))
{
cn.Open();
DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string WorkSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "]", cn);
DataTable dt = new DataTable(WorkSheetName);
da.Fill(dt);
return dt;
}
}
回答by marc_s
Check out the excellent FileHelpers library - there an article on CodeProjectabout it, and it's hosted here.
查看优秀的 FileHelpers 库 - CodeProject 上有一篇关于它的文章，它托管在这里。
It's pure C#, and it can import just about any flat-file, CSV, and it deals with Excel, too. The import is totally configurable - you can define things like delimiters, rows and/or columns to skip, and so on - lots of options.
它是纯 C#，几乎可以导入任何平面文件、CSV，也可以处理 Excel。导入是完全可配置的 - 您可以定义分隔符、行和/或列等要跳过的内容 - 很多选项。
I've successfully used it in various projects and it just works flawlessly - highly recommended.
我已经成功地在各种项目中使用了它，而且它完美无缺 - 强烈推荐。
回答by Rad
FileHelpersis your friend. I've used it happily for several projects and it has saved me much grief and labour
FileHelpers是您的朋友。我已经愉快地将它用于几个项目，它为我省去了很多悲伤和劳力
