Normalize Excel Headers Before Import: Clean Names and Column Mapping

2026-07-18

  • Excel
  • headers
  • column mapping
  • SQL
  • import
  • tutorial

Why headers break imports more often than data

Failures are often about the first row, not row 5,000:

  • Spaces: User Name vs DB UserName
  • Synonyms: Mobile / Phone / 手机
  • Duplicate headers confuse JSON/XML/SQL mapping
  • Row 1 is a title or merged cells, not real columns

Normalize headers first, then Excel to SQL or Excel to JSON, and you redo far less work.

Header conventions

Rule Good Bad
Match DB fields UserId, Email Random report labels
No leading/trailing spaces OrderId OrderId
Unique names Amount, AmountTax Two Amount columns
Letters, digits, underscore CreatedAt 创建时间(必填)
Row 1 = headers Columns on row 1 Report title on row 1

For XML, special characters become _ (Excel to XML)—better to start with valid names.

Quick cleanup in Excel

  1. Remove title/breadcrumb rows so row 1 is headers.
  2. Unmerge header cells.
  3. Find/replace spaces, full-width punctuation, line breaks.
  4. Pick one casing style (PascalCase or snake_case).
  5. Save as clean .xlsx or UTF-8 CSV.

ComTools helpers

1. Format to inspect structure

Excel Format previews headers and first rows—spot blank or shifted columns, unify dates, then export.

2. Remap in Excel to SQL

In Excel to SQL, map Excel columns to target field names (spaces in headers are also trimmed). Good when headers are slightly messy but the DB schema is fixed.

3. Text tools on a header line

For a single header line, Text Format can split, trim, and rejoin.

4. Tables from the web

HTML to Excel first, then verify row 1 is really the header.

Pre-import checks

Check How
Columns map to the table Excel header ↔ field list
PK exists and is unique Dedup preview
Types convert cleanly Spot-check dates/numbers; import tips
Empty headers Delete or rename ColumnN

Workflow

Raw export
  → normalize headers (this guide)
  → (optional) dedup
  → Excel to SQL INSERT / UPDATE
  → verify on test DB

For size: large file tips, merge → dedup → SQL.

FAQ

Must names match the DB exactly?
No—remap in Excel to SQL—but matching names prevent mistakes.

Chinese headers?
Allowed; map to English/pinyin field names when possible.

Garbled CSV headers?
Use UTF-8 or CSV to Excel, then fix names.

Multi-row headers?
Tools treat row 1 as headers. Collapse to a single header row first.

中文版