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#

yaml

# 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

bash

python3 scripts/excel_import.py import_config.yaml
python3 scripts/excel_import.py import_config.yaml --dry-run   # preview only

Import Modes#

ModeSource ConfigUse CaseSingle filesource.file_pathOne-to-one importDirectory batchsource.type: "directory"Process all files in a folderMulti-sourcesources: [...]Merge from multiple filesCSV.csv file_pathAuto-encoding detection (UTF-8/GBK/GB2312)Legacy .xls.xls file_pathRequires python-calamineAuto headerheader_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#

OptionDescription--dry-runPreview mode, no file writes--verboseDetailed per-record output--no-backupSkip 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#

1. Read user's import requirements and source/target file info
2. Create or adjust YAML config file
3. Run python3 scripts/excel_import.py <config.yaml> with --dry-run first
4. Review output, fix issues, then run without --dry-run
5. Check the JSON report alongside the output file