Excel Data Import
📖 技能介紹
name: excel-data-import description: > Import, merge, and transform data from Excel (.xlsx/.csv) files using YAML-driven configuration. Use when the user asks to: (1) import data from Excel/CSV into a template, (2) batch-process multiple files in a directory, (3) merge/consolidate data from multiple sources, (4) map and transform columns with validation rules, (5) do incremental data updates on existing spreadsheets. 當用戶要求匯入Excel、CSV匯入、表格資料匯入、Excel資料合併、批次處理Excel、欄位對映、資料校驗、 表頭自動檢測、增量更新Excel、多sheet合併時使用此技能。 Supports Chinese field names, multi-layer merged cell headers, auto header detection, CSV (auto-encoding), custom validators, and multi-source imports.
Excel Data Import
Configuration-driven data import from Excel and CSV files with field mapping, validation, and batch processing.
Prerequisites
- Python 3.8+
- Required:
pip3 install openpyxl pyyaml - Optional:
pip3 install python-calamine(for .xls legacy format)
Quick Start
# import_config.yaml
task_name: "人員資訊匯入"
source:
file_path: "data/source.xlsx"
sheet_name: "Sheet1"
header_row: 1
key_field: "身份證號"
target:
file_path: "output/result.xlsx"
sheet_name: "人員資訊"
header_row: 2
data_start_row: 3
field_mappings:
- source: "姓名"
target: "員工姓名"
required: true
- source: "身份證號"
target: "身份證號碼"
required: true
validate: "id_card"
- source: "部門"
target: "所屬部門"
default: "待分配"
error_handling:
backup: true
python3 scripts/excel_import.py import_config.yaml
python3 scripts/excel_import.py import_config.yaml --dry-run # preview only
Import Modes
| Mode | Source Config | Use Case |
|---|---|---|
| Single file | source.file_path |
One-to-one import |
| Directory batch | source.type: "directory" |
Process all files in a folder |
| Multi-source | sources: [...] |
Merge from multiple files |
| CSV | .csv file_path |
Auto-encoding detection (UTF-8/GBK/GB2312) |
| Legacy .xls | .xls file_path |
Requires python-calamine |
| Auto header | header_row: "auto" |
Detect header in complex sheets |
For full parameter docs, see data-mapping-guide.md.
Key Features
- Incremental update: Match by
key_field, update existing or append new rows - Multi-layer merged headers: Auto-detect and expand merged cell values
- Validation rollback: Failed rows are skipped entirely (no partial writes)
- Source deduplication: Duplicate keys across files are merged
- Auto-create target: Template generated from field_mappings if missing
Built-in Transforms & Validators
Transforms: strip, upper, lower, title, int, float, date
Validators: required, not_empty, id_card, phone, email, numeric, range, regex, length
For advanced usage, see advanced-features.md.
CLI Options
| Option | Description |
|---|---|
--dry-run |
Preview mode, no file writes |
--verbose |
Detailed per-record output |
--no-backup |
Skip target file backup |
Reference Documents
- Data Mapping Guide: data-mapping-guide.md — field mapping, transforms, validators
- Advanced Features: advanced-features.md — multi-source, batch, auto-header
- Auto Header Detection: auto_header_detection.md — complex header detection
- Quickstart: quickstart.md — step-by-step tutorial
- Workflow: workflow.md — detailed execution flow
- Best Practices: best_practices.md — usage recommendations
- Error Handling: error-handling.md — error codes and recovery
- Troubleshooting: troubleshooting.md — common issues
Workflow
- Read user's import requirements and source/target file info
- Create or adjust YAML config file
7w4.net小蔥技能站，你的AI助手技能庫。
- Run
python3 scripts/excel_import.py <config.yaml>with--dry-runfirst - Review output, fix issues, then run without
--dry-run - Check the JSON report alongside the output file
🤖 AI 評測
這是一個實用的 Excel 資料匯入工具，能幫助使用者快速完成表格資料的批次匯入和合並。優點是功能豐富、配置靈活、中文支援好，配有詳細的中文文件和示例。不足之處在於部分高階功能的使用說明不夠清晰，示例配置偶有前後矛盾，容易讓新手使用者感到困惑。總體質量良好，適合有批次資料處理需求的使用者使用。
