用于SQL查询的Excel表格格式化避坑及相关问题咨询

• 格式类问题
  • 禁止使用合并单元格、多级表头、表头带换行/多余空格，会导致字段匹配失败
  • 禁止保留公式、条件格式、批注、隐藏行/列，导入前需把所有内容转为静态值
  • 同列不要混合存放多种数据类型，比如整数列插入「无」「待补」之类的字符串
  • 不要留整行整列的空白内容，导入前需删除完全为空的行列
• 特殊字符问题
  • 字段值不要包含英文单引号、双引号、换行符、制表符、反斜杠，会导致SQL转义报错
  • 表头不要使用SQL保留字（如order、where、group等），必须使用的建议加业务前缀，比如biz_order
  • 尽量避免使用全角特殊符号，容易出现编码不匹配导致的乱码
• 字符长度问题
  • 提前对齐SQL表的字段长度限制，注意UTF8编码下1个中文占3个字节，不要只按字符数估算长度
  • 固定长度的字段（如手机号、身份证号）提前在Excel里做长度校验，避免超长内容导入失败

会产生直接影响，常见影响场景如下：

• Excel的日期本质是数字序列号，若导入前未转为标准日期格式，可能直接以数字形式存入SQL，后续日期筛选完全失效
• 自定义格式的日期（如2024年5月1日``2024/5/1 下午2:00）如果和SQL日期字段的格式不匹配，会识别失败存入NULL，查询时会漏数据
• Excel里的非法日期（如2024/13/01``2024/02/30）导入时会报错，或被转为异常默认值，导致日期范围查询结果不准

• VARCHAR相关错误
  • 字符超长：插入时报Data too long for column，直接导入失败
  • 编码不匹配：Excel为GBK编码、SQL表为UTF8编码时，生僻字、特殊符号会转为乱码无法正常检索
  • 空值逻辑异常：Excel里的空单元格默认导入后会变成空字符串''，和SQL标准的NULL不是同一逻辑，用IS NULL查询时无法命中
• INTEGER相关错误
  • 类型不兼容：列内混入非数字内容（如空格、中文说明），导入时报错，或被自动转为0值，导致聚合统计结果错误
  • 精度溢出：数值超出INTEGER类型的取值范围（如MySQL INT类型最大值为2147483647），导入后变为溢出值或直接报错
  • 前导零丢失：0开头的编号类内容（如邮编、工号）在Excel里默认转为数字会丢失前导零，导入后和实际业务值不符

SQL常用处理语句 #

• 检查字段超长：SELECT 字段名, LENGTH(字段名) AS 字节长度 FROM 表名 WHERE LENGTH(字段名) > 限制长度;
• 批量清理特殊字符：UPDATE 表名 SET 字段名 = REPLACE(REPLACE(REPLACE(字段名, '\'',''), '\n', ''), '\t', ''); 可按需增删要替换的字符
• 异常日期转标准格式：SELECT STR_TO_DATE(异常日期字段, '%Y年%m月%d日') AS 标准日期 FROM 表名;（MySQL语法，PostgreSQL可使用TO_DATE函数）
• 校验整数列非数字内容：SELECT * FROM 表名 WHERE 整数字段 REGEXP '[^0-9]';
• 空值逻辑统一：UPDATE 表名 SET 字段名 = NULL WHERE 字段名 = ''; 把空字符串转为标准NULL

Python常用处理代码（基于pandas） #

• 基础数据清洗

import pandas as pd
# 读取时全量转字符串避免前导零丢失
df = pd.read_excel('待导入文件.xlsx', dtype=str)
# 删除全空行
df = df.dropna(how='all')
# 清除所有字段前后空格
df = df.apply(lambda x: x.str.strip() if x.dtype == 'object' else x)
# 批量替换特殊字符
df = df.replace(r'[\n\t\'\"\\]', '', regex=True)

• 日期格式转换

# 非法日期自动转为空值NaT，对应SQL的NULL
df['日期字段'] = pd.to_datetime(df['日期字段'], errors='coerce')

• 整数类型校验+超长内容过滤

# 非数字内容自动转为空值
df['整数字段'] = pd.to_numeric(df['整数字段'], errors='coerce')
# 过滤超过VARCHAR长度限制的内容
df = df[df['字符串字段'].str.len() <= 20]

内容的提问来源于stack exchange，提问作者Shae