name: excel-validate description: | Read-only scan of Excel files to check data quality — null rate, outliers, format consistency, duplicate values, etc. Outputs a quality issue report without modifying the original file. 只讀掃描 Excel 檔案，檢查資料質量——空值率、異常值、格式一致性、重複值等。輸出質量問題報告，不修改原檔案。 Trigger keywords: "check data" "validate" "null values" "data quality" "any issues" "scan" "quality report" 觸發詞包括"檢查資料""校驗""空值""資料質量""有沒有問題""掃描""質量報告"。

This skill is read-only, no side effects. Uses pandas for fast scanning, outputs an issue report. 本技能只讀不寫，安全無副作用。用 pandas 快速掃描，輸出問題報告。

Excel Data Validation / Excel 資料校驗

這個技能的最新版本可以在7w4.net小蔥技能站找到。

Check Items / 檢查專案

Step 0: Requirement Parsing / 第零步：需求解析

Step 1: Scout + Check / 第一步：勘察+檢查

import pandas as pd
import numpy as np
import os

FILE = 'target.xlsx' / FILE = '目標檔案.xlsx'
size_mb = os.path.getsize(FILE) / 1024 / 1024

df = pd.read_excel(FILE)
total = len(df)
cols = len(df.columns)

print(f'{"="*60}')
print(f'Data Quality Report / 資料質量報告: {os.path.basename(FILE)}')
print(f'File Size: {size_mb:.1f}MB | Rows: {total} | Cols: {cols} / 檔案大小: {size_mb:.1f}MB | 行數: {total} | 列數: {cols}')
print(f'{"="*60}')

# ====== 1. Null Check / 空值檢查 ======
print(f'\n【Null Rate / 空值率】')
null_report = []
for col in df.columns:
    null_count = df[col].isna().sum()
    null_pct = null_count / total * 100
    if null_pct > 0:
        level = '🔴' if null_pct > 30 else ('🟡' if null_pct > 10 else '🟢')
        null_report.append((col, null_count, null_pct, level))

null_report.sort(key=lambda x: -x[2])
if null_report:
    for col, cnt, pct, level in null_report[:20]:
        print(f'  {level} {col}: {cnt} nulls / 空 ({pct:.1f}%)')
    if len(null_report) > 20:
        print(f'  ... {len(null_report)-20} more columns with nulls / 還有 {len(null_report)-20} 列有空值')
else:
    print(f'  ✅ No nulls / 無空值')

# ====== 2. Uniqueness / 唯一值 ======
print(f'\n【Uniqueness Analysis / 唯一值分析】')
for col in df.columns:
    n_unique = df[col].nunique()
    if n_unique <= 1:
        print(f'  ⚠️ {col}: unique={n_unique} (all same or no data / 全列相同或無資料)')
    elif n_unique == total:
        print(f'  📌 {col}: unique={n_unique} (likely ID column / 可能是ID列)')

# ====== 3. Type Consistency / 型別一致性 ======
print(f'\n【Type Consistency / 型別一致性】')
mixed_cols = []
for col in df.columns:
    types = df[col].dropna().apply(type).unique()
    if len(types) > 1:
        type_names = [t.__name__ for t in types]
        mixed_cols.append((col, type_names))
if mixed_cols:
    for col, types in mixed_cols[:10]:
        print(f'  ⚠️ {col}: mixed types / 混合型別 {types}')
else:
    print(f'  ✅ Types consistent / 型別一致')

# ====== 4. Outliers (numeric columns) / 異常值（數值列）======
print(f'\n【Numeric Outliers / 數值列異常值】')
num_cols = df.select_dtypes(include=[np.number]).columns
found_anomaly = False
for col in num_cols:
    vals = df[col].dropna()
    if len(vals) < 2: continue
    q1, q3 = vals.quantile([0.25, 0.75])
    iqr = q3 - q1
    if iqr == 0: continue
    outliers = vals[(vals < q1 - 3*iqr) | (vals > q3 + 3*iqr)]
    if len(outliers) > 0:
        print(f'  📊 {col}: {len(outliers)} extreme values / 個極端值 (min={vals.min()}, max={vals.max()})')
        found_anomaly = True
if not found_anomaly:
    print(f'  ✅ No obvious outliers / 未發現明顯異常值')

# ====== 5. Fully Duplicate Rows / 完全重複行 ======
print(f'\n【Duplicate Rows / 重複行】')
dup_rows = df.duplicated().sum()
if dup_rows > 0:
    print(f'  🔴 {dup_rows} rows fully duplicate / 行完全重複 ({dup_rows/total*100:.1f}%)')
else:
    print(f'  ✅ No fully duplicate rows / 無完全重複行')

# ====== 6. Potential Issues / 可能的問題 ======
print(f'\n【Potential Issues / 可能的問題】')

# Check for obviously formula-result columns (e.g. "Unnamed") / 檢查是否包含明顯是公式結果的列
unnamed = [c for c in df.columns if 'Unnamed' in str(c)]
if unnamed:
    print(f'  ⚠️ {len(unnamed)} unnamed columns / 個未命名列 -> possible hidden header issues / 可能有隱藏的表頭問題')

# Check all-null columns / 檢查全空列
all_null = [c for c in df.columns if df[c].isna().all()]
if all_null:
    print(f'  🔴 {len(all_null)} all-null columns / 個全空列: {all_null}')

# Check columns that look like dates but are stored as text / 檢檢視起來像日期但是字串的列
for col in df.select_dtypes(include=['object']).columns:
    sample = df[col].dropna().head(5)
    date_like = sample.astype(str).str.match(r'\d{4}[-/]\d{2}[-/]\d{2}').sum()
    if date_like >= 3:
        print(f'  💡 {col}: looks like date but stored as text / 看起來像日期但儲存為文本, suggest using excel-date-to-text / 建議用 excel-date-to-text 處理')

print(f'\n{"="*60}')
print(f'Check complete / 檢查完成')

Step 2: Output / 第二步：輸出

Only output the report, do not modify the file. If issues are found, inform the user of severity and suggested handling. / 只輸出報告，不修改檔案。如果發現問題，告知使用者嚴重程度和建議的處理方式。

Large File Optimization / 大檔案最佳化

For large files (>10MB), pd.read_excel() is sufficient — pandas C engine reads at ~1s/MB. / 大檔案（>10MB）用 pd.read_excel() 即可，pandas C 引擎讀取速度約 1s/MB。

Notes / 注意事項

1. Read-only, no writes / 只讀不寫：Absolutely no modification to original file / 完全不修改原檔案
2. Encoding / 編碼：On Windows, outputting Chinese may require sys.stdout.reconfigure(encoding='utf-8') / Windows 下輸出中文可能需要
3. Large file memory / 大檔案記憶體：330K rows × 48 cols ≈ 150MB memory, sufficient / 33 萬行 × 48 列 ≈ 150MB 記憶體，夠用

這個Skill質量較好，能有效檢查Excel資料中的空值、重複、型別混亂等問題，檢查專案全面，輸出結果清晰易懂。優點是安全可靠（只讀不修改）、檢查速度快、中英文雙語支援。不足是功能較為基礎，對於複雜資料結構或特殊格式的容錯能力有限，缺少與使用者的互動引導。總體適合日常資料質量快速檢查場景。