如何从Excel导入复杂的关系型数据到SQL Server

Question

如何从Excel导入复杂的关系型数据到SQL Server

3

我们有一些业务用户将产品信息输入到Excel电子表格中，我被指定为必须想出一种方法将这些信息输入到我们的SQL Server数据库中。问题在于Excel电子表格不仅仅是一个平面表格，它们是分层次的。类似于这样：

-[Product 1] [Other fields]...
   -[Maintenance item 1] [Other fields]...
      -[Maintenance task 1] [other fields]...
   -[Maintenance item 2] [Other fields]...
      -[Maintenance task 2] [other fields]...
      -[Maintenance task 3] [other fields]... 
-[Product 2] [Product Description] [Other fields]...
        ETC.......

产品可能有0到多个维护项，每个维护项可能有0到多个维护任务。这是数据库的结构。我需要设计一个标准的Excel模板，以便向我们的业务用户发送，让他们输入此信息，然后想办法将其导出到SQL Server中。由于数量很大，因此我需要将导入过程自动化。我该如何做？

- Cody C

显然不是。我从未说过它是。如果是这样，这就不是什么大问题了。我在那个你断章取义的引用中解释了数据库表之间的关系。 - Cody C

1

电子表格，呃。一个基于浏览器的在线门户网站是可能的替代方案吗？ - Larry Lustig

5个回答

2

我不认为您会找到一个可以为您完成此操作的导入工具。相反，您需要编写一个脚本来ETL电子表格文件。我经常使用Python进行这项工作（事实上，我今天就在做这个）。

请确保您在单元格级别处理异常，并向用户报告哪些单元格包含了意外信息。使用手动创建的电子表格时，您必须定期处理此问题。

话虽如此，如果您收到的是XLSX文件，则可能可以开发一种XML翻译来将其转换为更易处理的XML文档。

- Larry Lustig

1

把它分成几个 Excel 工作簿可能更有意义……一个用于产品，但另一个用于维护项目，另一个用于维护任务。对于每个工作簿，他们都必须输入某种 ID 来将它们联系起来（例如：maintenance_task_id=1 链接到 maintenance_item_id=4）。这可能会让业务用户难以记住，但唯一的替代方法是为每行输入大量冗余数据。

接下来，创建一个归一化的数据库模型（以避免存储冗余数据），并编写应用程序或脚本以解析您的 Excel 工作簿来填充它。模糊而高层次，但这就是我会做的方式。

- Aaron

这是简单的方法，但不幸的是我无法让管理层接受。 - Cody C

1

总体而言，我同意之前的帖子...

我的建议是-完全避免使用电子表格。花时间制作一个简单的前端表单-最好是基于Web的表单。尽可能清洁地捕获数据（这里的任何内容都比电子表格更干净-包括仅具有命名字段）

你最终会花费更少的时间。

- Randy

1

我会在模板中添加VBA代码，以尽可能多的结构化和智能化方式来处理用户数据输入和验证。

在极端情况下，您可以通过表单让用户输入所有数据，这些表单会将所有已验证的数据放在工作表上，并且拥有一个内置于保存或关闭事件中的总体验证程序。
较少极端的情况是添加3个命令按钮驱动以下代码：
- 添加产品
- 添加维护条目
- 添加维护任务
并在保存/关闭时添加一些总体验证代码，这样您就可以在数据输入任务中添加尽可能多的智能。

使用命名单元格或其他由VBA代码创建的隐藏元数据作为标记，以便您的DB更新程序可以更好地理解数据。

我最后一次像这样做花了3-4人周，包括DB更新程序，但我认为它可能比您的示例更复杂。但是，如果您没有经验使用VBA和Excel对象模型和事件，显然需要更长时间。

- Charles Williams

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- HLGEM · Accepted Answer

欢迎来到最糟糕的存储数据并尝试将其导入数据库的方式。如果可能的话，请不要让他们创建那样的Excel电子表格。这种方法很容易在数据导入中产生许多错误，如果您必须支持这种混乱，您将会永远憎恨它。

我甚至不能相信我正在建议这个，但是您能让他们使用一个简单的Access数据库吗？它甚至可以直接链接到SQL服务器数据库并正确地存储数据。通过使用Access表单，用户将发现添加和维护信息相对容易，并且您将遇到比尝试按照您描述的形式导入Excel数据更少的问题。这将是解决您问题的一种成本较低且易于出错的方案。

您无法改变格式，我找到的最佳处理方式是将其按原样导入到暂存表中，为每个下属行添加ID（您可能需要循环执行此操作），然后将信息拖出到关系暂存表中，最后将其导入生产数据库。

您可以使用SSIS创建所有这些内容，但这不会很容易、也不会很快，如果用户在输入数据时没有纪律性（他们永远没有表格要填写），那么这将非常容易出现错误。请确保完全拒绝Excel电子表格，并将其退回给用户，如果它与指定结构有任何偏差。相信我。

我估计使用Access解决方案需要一个月的时间，而Excel解决方案至少需要六个月的开发时间。实际上它会很糟糕。