现实中的 CSV 几乎都是"脏"的:多余空格、大小写混乱、空值、错误日期、重复行……下面 10 个技巧按"先易后难"排列,多数都能在 365Lab 数据清洗 里一键完成。
Real-world CSVs are almost always "dirty": stray spaces, messy case, nulls, wrong dates, duplicates… The 10 tips below go easy-to-hard, and most can be done in one click with 365Lab Clean.
1. 去除首尾空格1. Trim Whitespace
" 北京 "和"北京"在系统里是两个值,先统一去空格,去重和匹配才准确。
" 北京 " and "北京" look the same but compare as different — trim first so dedup and matching work.
2. 统一大小写2. Normalize Case
邮箱、状态码常混用大小写。统一成小写(或首字母大写)能避免重复与统计偏差。
Emails and status codes often mix case. Unify to lowercase (or title case) to avoid dupes and skewed stats.
3. 处理空值3. Handle Nulls
空字符串、"NULL"、"N/A" 都可能是"缺数据"。把它们统一为空,或按规则填充(如填 0、填"未知")。
Empty strings, "NULL", "N/A" may all mean "missing". Normalize to blank, or fill per rule (0, "Unknown").
4. 纠正日期格式4. Fix Date Formats
不同来源日期形如 2026/10/30、10-30-2026。统一为标准格式(推荐 ISO YYYY-MM-DD),后续排序、统计才不会出错。
Sources vary: 2026/10/30, 10-30-2026. Standardize to ISO YYYY-MM-DD so sorting and stats stay correct.
5. 删除重复行5. Remove Duplicates
按整行或指定列去重(详见 删除 CSV 重复行),保留首条或末条。
Dedupe by whole row or selected columns (see Remove Duplicate Rows), keep first or last.
6. 校验手机号/邮箱6. Validate Phones / Emails
用正则或格式校验,把明显错误的号码、邮箱挑出来单独处理,而不是混在干净数据里。
Use regex or format checks to flag obviously wrong numbers/emails for separate handling, not buried in clean data.
7. 脱敏敏感信息7. Mask Sensitive Info
对外分享前,把手机号、身份证中间几位替换为 *,既保留可用性又保护隐私。
Before sharing, replace the middle digits of phones/IDs with * — keeps usability while protecting privacy.
8. 拆分/合并列8. Split / Merge Columns
"姓名"列里挤着"张三|销售",可按分隔符拆成两列;反之也能把"省""市"合并成地址。
"张三|销售" in one column can split by delimiter into two; conversely merge "province"+"city" into an address.
9. 统一编码9. Unify Encoding
多份文件编码不一会互相污染。先全部转成 UTF-8(见 编码转换),再合并清洗。
Mixed encodings corrupt each other. Convert everything to UTF-8 first (see encoding conversion), then merge and clean.
10. 用"配方"固化流程10. Save as a Recipe
如果每周都要做同样的清洗,用 365Lab 的 操作配方 录制成一条流程,下次一键重放,省时且不会漏步骤。
If you clean the same way weekly, record it as a 365Lab Recipe and replay with one click — saves time and prevents missed steps.