如何验证转置加载到Oracle的Excel数据一致性并编写对比脚本？

一、行数正确性验证 #

核心逻辑：Excel中每一行（排除主键Roll no列）转置后，对应Oracle中(Excel列数-1)行数据。验证步骤如下：

Excel端统计 #

用Excel内置公式快速获取关键数值：

• 有效数据行数（排除表头）：=COUNTA(A:A)-1（假设Roll no在A列）
• 需转置的列数（排除Roll no列）：=COUNTA(1:1)-1

Oracle端验证 #

执行SQL查询对比行数：

-- 验证总行数是否等于 Excel行数*(Excel列数-1)
SELECT COUNT(*) AS oracle_total
FROM your_table_name;

-- 验证单个Roll no对应的行数是否符合预期
SELECT roll_no, COUNT(*) AS row_count
FROM your_table_name
GROUP BY roll_no
HAVING COUNT(*) != [Excel列数-1]; -- 替换为Excel端统计的列数减1值

如果第二个查询无返回结果，说明所有Roll no的行数都符合转置规则。

二、数据值一致性验证 #

通过将Oracle表逆转置还原为Excel宽表结构，再与原Excel对比：

1. 动态逆转置SQL（适配表结构变更） #

由于工作表每6个月会变更，用动态SQL自动识别参数列：

DECLARE
    v_col_list VARCHAR2(4000);
BEGIN
    -- 自动获取所有唯一的Parame值，生成PIVOT列
    SELECT LISTAGG('''' || parame || ''' AS ' || parame, ', ') WITHIN GROUP (ORDER BY parame)
    INTO v_col_list
    FROM (SELECT DISTINCT parame FROM your_table_name);

    -- 执行动态逆转置
    EXECUTE IMMEDIATE '
        SELECT *
        FROM (
            SELECT roll_no, parame, param_value
            FROM your_table_name
        )
        PIVOT (
            MAX(param_value)
            FOR parame IN (' || v_col_list || ')
        )
    ';
END;
/

将查询结果导出为Excel，即可和原文件做单元格级对比。

2. Excel单元格对比 #

用Excel函数快速校验：

=IF(VLOOKUP(A2, 原表!$A:$D, 2, FALSE)=B2, "一致", "不一致")

替换公式中的列索引，可批量验证所有参数列。

三、自动化对比脚本（Python） #

编写Python脚本实现全流程自动化，无需手动处理，适配表结构变更：

import pandas as pd
from sqlalchemy import create_engine

# 1. 读取Excel原数据
excel_df = pd.read_excel("your_excel_file.xlsx")
excel_row_num = excel_df.shape[0]
excel_col_num = excel_df.shape[1] - 1  # 排除Roll no列

# 2. 连接Oracle并读取数据
engine = create_engine('oracle+cx_oracle://用户名:密码@主机:端口/服务名')
oracle_df = pd.read_sql_table("your_table_name", engine)

# 3. 行数验证
oracle_total_row = oracle_df.shape[0]
expected_row = excel_row_num * excel_col_num
if oracle_total_row == expected_row:
    print("✅ 行数验证通过：Oracle总行数与预期一致")
else:
    print(f"❌ 行数验证失败：Oracle实际{oracle_total_row}行，预期{expected_row}行")

# 4. 数据值验证：逆转置后对比
oracle_pivot_df = oracle_df.pivot(index="ROLL_NO", columns="PARAME", values="PARAM_VALUE").reset_index()
# 统一列名（去除Excel列名末尾的点，如Name.改为Name）
excel_df.columns = [col.rstrip('.') for col in excel_df.columns]

# 对比两个数据集
diff_df = excel_df.compare(oracle_pivot_df, keep_shape=True, keep_equal=True)
if diff_df.empty:
    print("✅ 数据值验证通过：所有数据与Excel完全一致")
else:
    print("❌ 数据值验证失败，差异内容：")
    print(diff_df)

脚本依赖库：pandas、sqlalchemy、cx_Oracle、openpyxl，执行前需提前安装。

四、关键注意事项 #

• 确保Excel中Roll no为唯一值，否则转置后Oracle的分组统计会出错
• 处理空值：Excel空值转置后Oracle会存为NULL，对比时需注意空值匹配规则
• 脚本可配置化：可将Excel路径、Oracle连接信息、主键列名抽为配置项，进一步提升灵活性

内容的提问来源于stack exchange，提问作者Harshita Porwal