SSIS读取excel电子表格，生成数字并插入数据库

本文关键字：数字 插入 数据库 读取 excel 电子表格 SSIS | 更新日期: 2023-09-27 18:06:47

大家好，我必须解决一个问题，即使用ssis加载excel电子表格，解释数据，执行数字生成练习，然后插入sql server数据库。我可以阅读excel电子表格，并得到数据很好。然而，我所面临的问题是数字生成部分，然后插入到数据库。

我的excel电子表格看起来像这样:

Range       Location    0   1   2   3   4   5   6   7   8   9                   
01132 21    Leeds       Y       Y   Y       Y   Y       Y

例如，我们读到利兹范围在2列下有一个"Y"，这意味着我们需要生成01132212000 - 01132212999之间的数字。我有点确定我们如何阅读excel电子表格，检查一个数字是否在它下面有一个"Y"，为该范围生成这些数字，然后将所有生成的数字插入数据库。什么好主意吗?

SSIS读取excel电子表格，生成数字并插入数据库

实际上并不太难——如果我能假设这些"数字"中的每一个都应该作为一行出现的话。

你需要一个包含Excel源的数据流(这将决定你在32位模式下运行你的包)。然后使用Unpivot组件将"数字"列转换为行，这样每个区域/位置都有一个带有Y或N的行。使用条件分割过滤掉N行，只剩下Y。然后，您将需要一个源—一个脚本源或一个灵巧的OLE DB源—来生成从0到999编号的1000行。接下来，您将使用派生列、排序和合并连接在Excel行和数字行之间进行笛卡尔连接。然后，您可以使用派生列生成您想要的"真实"数字，将其转换为字符串，并在左侧填充零。

澄清"脚本源或狡猾的OLE DB源"来生成行号…使用脚本作为源代码:

  1. 添加一列，只是一个DT_I4。
    2. 在CreateNewOutputRows中，使用for循环迭代1000次，在循环中使用Output0Buffer。AddRow，并将列设置为循环值。

使用OLE DB源:

  1. 创建一个CTE或其他一些T-SQL魔法(我没有资格建议-我只知道这是可能的)来创建一个"数字表"从1到1000，并从中选择。

就我个人而言，我会在数据库中解决这个问题。在数据库中创建一个表来保存excel表格中的原始数据。然后，您可以像这样在SQL中解决这个问题:

create table #excel (
    range       nvarchar(7),
    location    nvarchar(20),
    col_0       nvarchar(1),
    col_1       nvarchar(1),
    col_2       nvarchar(1),
    col_3       nvarchar(1),
    col_4       nvarchar(1),
    col_5       nvarchar(1),
    col_6       nvarchar(1),
    col_7       nvarchar(1),
    col_8       nvarchar(1),
    col_9       nvarchar(1)
)
/*Use SSIS to load your Excel sheet in, instead of this insert*/
insert into #excel
values ('0113221', 'Leeds', 'Y', NULL, 'Y', 'Y', NULL, 'Y', 'Y', NULL, 'Y', NULL)

;with numbers as
(
    select 0 x
    union all
    select x + 1
    from numbers
    where x < 99
)
select e.location, e.range + '0' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_0 = 'Y'
union all
select e.location, e.range + '1' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_1 = 'Y'
union all
select e.location, e.range + '2' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_2 = 'Y'
union all
select e.location, e.range + '3' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_3 = 'Y'
union all
select e.location, e.range + '4' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_4 = 'Y'
union all
select e.location, e.range + '5' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_5 = 'Y'
union all
select e.location, e.range + '6' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_6 = 'Y'
union all
select e.location, e.range + '7' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_7 = 'Y'
union all
select e.location, e.range + '8' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_8 = 'Y'
union all
select e.location, e.range + '9' + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.col_9 = 'Y'

如果你能将你的数据按照下面的格式(或类似的格式)进行规范化，你就可以为自己节省很多不整洁的代码:

create table #excel (
    range       nvarchar(7),
    location    nvarchar(20),
    number      nvarchar(1),
    yes_no      nvarchar(1)
)
insert into #excel
values ('0113221', 'Leeds', '0', 'Y'),
    ('0113221', 'Leeds', '1', NULL),
    ('0113221', 'Leeds', '2', 'Y'),
    ('0113221', 'Leeds', '3', 'Y'),
    ('0113221', 'Leeds', '4', NULL),
    ('0113221', 'Leeds', '5', 'Y'),
    ('0113221', 'Leeds', '6', 'Y'),
    ('0113221', 'Leeds', '7', NULL),
    ('0113221', 'Leeds', '8', 'Y'),
    ('0113221', 'Leeds', '9', NULL)

;with numbers as
(
    select 0 x
    union all
    select x + 1
    from numbers
    where x < 99
)
select e.location, e.range + e.number + RIGHT('00' + CAST(n.x as nvarchar), 3)
from #excel e cross join
    numbers n
where e.yes_no = 'Y'

我的SSIS有点生疏了，我面前没有实例可以玩，所以我恐怕不能帮你做规范化部分。

相关文章：