为什么 Excel 会"自作主张"Why Excel "Helps" You (Badly)
CSV 是纯文本,本身没有"日期"或"数字"类型。Excel 打开时会按本地语言习惯猜测每列类型:看到 3/4 当成 3 月 4 日,看到 00123 当成 123,看到长身份证号当成科学计数法。这些"智能"正是数据出错的来源。
CSV is plain text with no "date" or "number" type. When Excel opens it, it guesses each column's type by local convention: 3/4 becomes Mar 4, 00123 becomes 123, and long IDs become scientific notation. That "smartness" is exactly where errors creep in.
日期请用 ISO 8601Use ISO 8601 for Dates
最稳妥的日期写法是 YYYY-MM-DD(如 2026-10-30)。它不依赖语言、排序即自然序,几乎所有系统都认。避免 2026/10/30 或 10-30-2026 这类易歧义写法。
The safest date is YYYY-MM-DD (e.g. 2026-10-30). It's language-neutral, sorts naturally, and every system reads it. Avoid ambiguous forms like 2026/10/30 or 10-30-2026.
数字前导零丢失Lost Leading Zeros
邮编 010000、工号 00123、手机号前的 0 在 Excel 里常被打掉。解决:把该列存成文本,或在值前加单引号 '010000;用 365Lab 清洗工具 也可强制按文本保留。
Postal codes 010000, IDs 00123, leading 0 on phones get stripped by Excel. Fix: store the column as text, or prefix with a single quote '010000; the 365Lab cleaner can force text too.
长数字变科学计数法Long Numbers Become Scientific Notation
身份证号、银行卡号(15 位以上)在 Excel 中会变成 1.23E+17 且末位变 0。务必以文本导入:用"数据 → 从文本/CSV"向导,将该列设为"文本";或用 导入向导 而非直接双击打开。
ID cards and bank numbers (15+ digits) turn into 1.23E+17 with trailing zeros in Excel. Import as text: use "Data → From Text/CSV" and set the column to "Text", or use the import wizard instead of double-clicking.
货币与千分位Currency & Thousands Separators
- CSV 里只存纯数字(如
1234.56),不要带¥、$或逗号,符号和格式留给展示层。 - 含逗号的
1,234.56会被当成两列——先去掉千分位符号(见 CSV 数据清洗 10 技巧)。 - 不同地区的 decimal 分隔符不同(中英文用点,部分欧洲用逗号),统一用点最安全。
- Store raw numbers only (e.g.
1234.56) without¥,$or commas; leave symbols/formatting to the display layer. - A comma in
1,234.56splits into two columns — strip thousands separators first (see 10 CSV Cleaning Tips). - Decimal separators differ by region (dot in EN/ZH, comma in parts of Europe); the dot is safest.
时区Time Zones
跨时区系统交换数据时,建议在日期后附时区(如 2026-10-30T09:00:00+08:00),避免"差 8 小时"的混乱。至少要在文档里写明所用时区。
When exchanging across zones, append the zone (e.g. 2026-10-30T09:00:00+08:00) to avoid off-by-8-hours confusion. At minimum, state the zone in your docs.
修复方法How to Fix It
- 用导入向导指定列类型(文本/日期),而不是直接打开。
- 在 365Lab 清洗工具 中批量把数字列强制为文本、统一日期格式。
- 导出时优先选择 UTF-8 与 ISO 日期,从源头减少歧义(见 CSV 转 Excel)。
- Use the import wizard to set column types (text/date) instead of just opening.
- In the 365Lab cleaner, force number columns to text and unify date formats in bulk.
- On export, prefer UTF-8 and ISO dates to remove ambiguity at the source (see CSV to Excel).
日期数字一团乱?打开在线清洗工具,一键把数字列锁定为文本、统一日期格式,全程本地处理。
Dates and numbers a mess? Open the cleaner to lock number columns as text and unify dates — all local.