当列和行与平面文件不匹配时，如何解决(使用)ETL

Question

我有 2 个大文件，一个 Excel 电子表格和一个 csv 文件，它们都搞砸了，但仍然需要上传到表格中。我正在学习如何使用 SSIS。假设列和行看起来像这样。

第一个 Excel 电子表格(文件扩展名 .xlxs)...

ID    Name    GroupName City        Time        Price Date
A1    South   Group1    London      10/06/2018  $4.50 13.30
A2    North   Group2    New York                $60   10/07/2018  09:00  AM  

图1

第二个Excel电子表格(文件扩展名.csv)...

ID  Name    GroupName   City         Date         Time   Price  
A3  East    Group3      Paris        09/09/2017   $5.00  03:00  AM   
A4  West    Group4      Berlin                    01/05/2018    $12.50  18:00

图2

如果您查看图 1 中的 ID A2，您将看到日期为 9.00，然后在不同的列中看到 AM。你如何解决这样的问题？这是一个示例，因此时间数据在每列中随机不同。另请注意图 2 中的 A4

我对Script Task和Foreach Loop Container在一定程度上很熟悉.

我在网上搜索并找到了这个网站....

这就是我正在寻找的。

现在，已经创建了一个具有这些列名的表

ID, Name, GroupName, City, Date, Time and Price.

因此，理想情况下，当数据加载到表中时，它应该看起来像这样......

ID    Name    GroupName   City         Date         Time   Price  
A1    South   Group1      London       10/06/2018   13.30   $4.50           
A2    North   Group2      New York     10/07/2018   09:00AM $60 
A3    East    Group3      Paris        09/09/2017   03:00AM $5.00  
A4    West    Group4      Berlin       01/05/2018   18:00   $12.50

我不知道如何处理这个问题。

请注意：我只想知道需要使用哪些 SSIS 工具箱组件。一旦我知道，我会尝试解决这个问题。这就是没有代码示例的原因。

提前谢谢。

更新

谢谢哈迪。如果没有人介意，我将保持此线程打开并在VS 2019中SSIS完全可用时进行更新，并有机会找到解决方案。

Answer 1

我认为没有一个简单的解决方案。但我会尝试给出一些建议：

1. 将 Excel 文件转换为 csv 文件
2. 在平面文件连接管理器中，仅在类型为 DT_STR 且长度 = 4000 的列上定义
3. 在数据流任务中，添加一个脚本组件以拆分每行并验证每个列值并将其分配给相关的输出列

您可以参考以下答案以了解更多信息，因为它包含有关如何在数据结构不是很好时从平面文件中读取数据的有用信息(即使情况不同)

• 无法识别 SSIS 不规则文件 CRLF
• 如何加载空间不均匀的非结构化平面文件作为分量仪？并且文件还包含两个标头
• SSIS 将 LF 读取为终止符，当其设置为 CRLF 时