sql - 在SSIS中导入已合并单元格的Excel

Question

我的问题是在读取合并/组合列单元格的 Excel 文件时。例如，读取下面的excel数据到数据库。

Excel 输入:

+----+-------+---------+-------+
| ID |      NAME       | DEPT  |
+----+-------+---------+-------+
|    | FNAME | LNAME   |       |
+----+-------+---------+-------+
|  1 | Akil  | Tiwari  | IT    |
|  2 | Vinod | Rathore | IT    |
|  3 | Jatin | Khanna  | HR    |
|  4 | Divya | Kherde  | AD    |
|  5 | Amey  | Gauda   | FI    |
+----+-------+---------+-------+

预期数据库输出:

+----+-------+---------+------+
| ID | FNAME |  LNAME  | DEPT |
+----+-------+---------+------+
|  1 | Akil  | Tiwari  | IT   |
|  2 | Vinod | Rathore | IT   |
|  3 | Jatin | Khanna  | HR   |
|  4 | Divya | Kherde  | AD   |
|  5 | Amey  | Gauda   | FI   |
+----+-------+---------+------+

Answer 1

只要正常连接您的 Excel，Excel 连接管理器就会像下面这样读取它:

+----+-------+---------+-------+
| ID | NAME  | F3      | DEPT  |  <-- Header
+----+-------+---------+-------+
|    | FNAME | LNAME   |       |  <--First Row
+----+-------+---------+-------+
|  1 | Akil  | Tiwari  | IT    |  <-- Second Row
|  2 | Vinod | Rathore | IT    |   .
|  3 | Jatin | Khanna  | HR    |   .
|  4 | Divya | Kherde  | AD    |   .
|  5 | Amey  | Gauda   | FI    |   .
+----+-------+---------+-------+

在 Excel 源中只需重命名列，如下所示”

NAME --> FNAME
F3   --> LNAME

然后只需添加一个条件拆分，使用以下表达式过滤 ID = NULL 的行

ISNULL([ID]) == false

那么第一行将被忽略