将 csv 文件/excel 导入 sql 数据库 asp.net [英] import csv file/excel into sql database asp.net

问题描述

我正在使用 c# 使用 asp.net Visual Studio 2008/SQL 2000(未来 2005)开始一个项目.

I am starting a project with asp.net visual studio 2008 / SQL 2000 (2005 in future) using c#.

对我来说棘手的部分是现有的数据库架构经常更改，导入文件列都必须与现有的数据库架构匹配，因为它们可能不是列名的一对一匹配.(有一个查找表提供了我将使用的列名的表模式)

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)

我正在探索不同的方法来解决这个问题，需要一些专家的建议.是否有任何现有的控件或框架可供我用来执行任何这些操作?

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?

到目前为止，我探索了 FileUpload .NET 控件，以及一些 3rd 方上传控件来完成上传，例如 SlickUpload 但上传的文件应该是 <500MB

So far I explored FileUpload .NET control, as well as some 3rd party upload controls to accomplish the upload such as SlickUpload but the files uploaded should be < 500mb

下一部分是读取我的 csv/excel 并将其解析以显示给用户，以便他们可以将其与我们的数据库模式进行匹配.我看到了 CSVReader 和其他人，但对于 excel 它更困难，因为我需要支持不同版本.

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 CSVReader and others but for excel its more difficult since I will need to support different versions.

本质上，执行此导入的用户将从此导入文件插入和/或更新多个表.还有其他更高级的要求，例如记录匹配但是和导入记录的预览，但我希望先了解如何执行此操作.

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.

更新:我最终使用带有 LumenWorks.Framework 的 csvReader 来上传 csv 文件.

Update: I ended up using csvReader with LumenWorks.Framework for uploading the csv files.

推荐答案

我正在使用 LumenWorks.Framework 的 csvReader 将 csv 文件上传和导入到我基于导入的列创建的内存中的 sql 表和 DataTable 中.

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.

我还让用户映射 ui 中的字段，并通过将每条记录标记为插入/更新/错误来验证和准备要导入的数据.然后，我为每个将受到影响的表创建/填充强类型数据集，并为企业库 UpdateDataset() 方法构建插入/更新查询.

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.

然后我提交事务以插入/更新数据库.——

Then I submit the transaction to insert/update the database. –

映射是一个有 4 列的网格.(导入字段名称、目标表、目标字段名称、忽略、匹配状态)，目标表和字段名称正在选择，根据所选表刷新.我动态填充选择.如果找到匹配项，则最初选择组合填充 1 个值，否则请选择.ignore 允许用户忽略该字段.匹配状态是一个字段是否被自动映射

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

这篇关于将 csv 文件/excel 导入 sql 数据库 asp.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！