使用U-SQL读取未知列数Excel时消除最后列值重复问题（Azure Data Lake）

你现在面临两个小问题：一是oh22is的ExcelExtractor会用最后一个有数据列的值自动填充后续空列，二是需要把当前的宽表转成Year/Month/SUM的窄表格式。我给你梳理一套可行的解决步骤：

第一步：标记行类型并拆解所有列 #

首先我们得先给每行打上类型标签（区分SUM汇总行、年份行、月份行），然后把所有数据列转成「列名-值」的键值对形式，方便后续过滤和聚合：

USE DATABASE master;
REFERENCE ASSEMBLY [DocumentFormat.OpenXml];
REFERENCE ASSEMBLY [oh22is.Analytics.Formats];
DECLARE @ExcelFile = @SourceFolderPath+@SourceFileName;

-- 提取所有列并标记行的类型
@ResourcesWithRowType =
    SELECT *,
           CASE [A]
               WHEN "SUM:" THEN "SumRow"
               WHEN "年份" THEN "YearRow"
               WHEN "月份" THEN "MonthRow"
               ELSE "Other" -- 按需处理其他可能的行，比如表头行
           END AS RowType
    FROM @ExcelFile
    USING new oh22is.Analytics.Formats.ExcelExtractor("Ark1");

-- 把B到HZ的所有数据列拆成键值对（这里需要手动列出所有列，U-SQL静态类型要求必须明确）
@Unpivoted =
    SELECT RowType,
           ColumnName,
           ColumnValue
    FROM @ResourcesWithRowType
    CROSS APPLY (
        VALUES
            ("B", [B]), ("C", [C]), ("D", [D]), 
            -- 中间省略其他列，直到列出("AM", [AM]), ("AN", [AN]), ..., ("HZ", [HZ])
            ("AM", [AM]), ("AN", [AN]), ..., ("HZ", [HZ])
    ) AS Columns(ColumnName, ColumnValue);

第二步：过滤掉重复填充的列 #

核心思路是：找出那些所有行的值都和最后一个有效列（你的例子里是AM列）完全相同的列，这些就是被重复填充的无效列，直接排除：

-- 先获取AM列（最后一个有真实数据的列）在各行的值
@AMColumnValues =
    SELECT RowType, ColumnValue AS AMValue
    FROM @Unpivoted
    WHERE ColumnName == "AM";

-- 筛选有效列：要么是AM列本身，要么至少有一行的值和AM列不一样
@ValidColumnNames =
    SELECT DISTINCT u.ColumnName
    FROM @Unpivoted u
    JOIN @AMColumnValues am ON u.RowType == am.RowType
    WHERE u.ColumnName == "AM" OR u.ColumnValue != am.AMValue;

-- 只保留有效列的数据
@FilteredUnpivoted =
    SELECT u.RowType, u.ColumnName, u.ColumnValue
    FROM @Unpivoted u
    JOIN @ValidColumnNames vc ON u.ColumnName == vc.ColumnName;

第三步：聚合生成目标窄表 #

现在我们已经有了干净的键值对数据，接下来按列名分组，把同一列对应的年份、月份、SUM值聚合到一行：

-- 转成Year/Month/SUM的目标结构
@FinalResult =
    SELECT 
        MAX(CASE WHEN RowType == "YearRow" THEN ColumnValue END) AS Year,
        MAX(CASE WHEN RowType == "MonthRow" THEN ColumnValue END) AS Month,
        MAX(CASE WHEN RowType == "SumRow" THEN ColumnValue END) AS SUM
    FROM @FilteredUnpivoted
    GROUP BY ColumnName;

-- 输出最终结果
OUTPUT @FinalResult TO "/FinalPivotedResult.csv" USING Outputters.Csv(quoting: false);

额外小贴士 #

• 如果后续Excel的有效列数量变化（比如下个月新增到AO列），你不需要修改代码里的AM判断逻辑——这套代码会自动识别所有和最后一个有差异的列，只要某列存在至少一行的值和当前最后一个有效列不同，就会被保留。
• 关于ExcelExtractor的重复填充问题，你可以查一下该库的文档，看看有没有参数能设置只提取有实际数据的列，这样能避免一开始就提取大量空列，提升运行效率。

内容的提问来源于stack exchange，提问作者Ville Lehtisaari