Microsoft Excel的坑:数据格式转换时的陷阱简析(含导数据经验/mysql)

首先一个不可否认的事实:Microsoft Excel是个使用简便、功能丰富的数据处理工具;但这不能掩饰它本身的不少缺陷,本文只讲其在数据转换时的陷阱,就是“坑”。

脑残的转义约定:双引号转义、不转义换行符

csv是常用的数据交换格式,但excel生成的csv文件却有几个脑残的约定(大概是微软自己的历史包袱所致),通常excel使用双引号括起来字段内容,如果字段内容中有双引号,则使用 \" 代替;还有其他特殊符号,也会做相应转义。所以,使用excel转换数据(如导入到MySQL)时,要注意处理这些问题。

换行符转义。可以事先在excel中替换,excel查找格里先清空格中内容,按几次Backspace再按几次Delete,确保真正的清空。按住Alt键不松开,输入数字10,此时格里没有任何显示,然后在替换格中输入\n,全部替换,即完成换行符的转义。

双引号转义。这个问题有点恶心。1)如果对内容一致性要求不高,可以将双引号完全删除(查找替换成空字符串)。2)将双引号替换成一个其他字符串(比如~^这类特殊字符、或者~~这样的组合字符串),事先在excel文件里查找、以确保该字符串不会在内容中出现。保存成csv后,再将该保护字符串替换成\",完成转义替换。

 Excel表导入MySQL的几个可选方案

导数据前一般需要事先建好目标表,目标表的字段长度可以适当大一此,避免导入失败。或者通过事先计算每个字段的最大长度,比如用awk 参考。下面是几种导入方法,按操作难度从易到难排列

  • 使用第三方工具(如navicat 等)直接导入,但不少时候会报错,而且原因不明。
  • excel转为csv,通过phpMyAdmin或Load Data等导入MySQL。
  • excel公式构造一系列insert 语句,拿到mysql里执行(粘贴到客户端里运行,或保存到文件让mysql加载)。
  • excel导入到ms access(微软自家产品内部有良好兼容性),再使用navicat等第三方工具导到mysql。
  • 自己写脚本处理。

后面只是几张图,随便参考一下吧。

navicat导excel到mysql报错

navicat导excel到mysql报错

EOF

发表评论

电子邮件地址不会被公开。