使用R语言转存Excel到MySQL

Z.TIAN

ML/R学习笔记

2020年6月19日

大约1k字

阅读需7分钟

本页文档最后更新于：2021年3月8日

花了两天时间写了一个Excel数据转换脚本，原需求除了要把数据转存到Mysql中，还要对每一条数据进行拆分和重组，并不容易。最终我利用R语言完成了这个小需求，本着总结学习的想法，在此处将多余逻辑删除，抽离出了最基本的Excel转存Mysql的功能，这样也可以算一个小轮子了。（仅做学习用，Navicat等工具可以直接导入Excel）

本项目已开源至Github，地址：https://github.com/TianZonglin/transferExcelbyR

适用场景和使用要求（暂）

需要处理的表文件以文件夹形式存储
全部的数据表均必须包含相同的列格式，切忌无关表的污染
每个表文件中只有一个Sheet
数据表必须在第三级目录（单文件亦是如此），例如

1 2	ecProject\io_Input_Excel_Folder\simples\ORGDATA.XLS # 分别是：工作目录 -> 输入文件夹(1st) -> 内部自定义的文件夹(2nd) -> 真正的表文件(3rd)

此脚本的特点

批量处理全部输入文件夹下二级文件夹的全部Excel表文件
自动根据所需要转换的Excel表文件在Mysql中创建表
自动检测Excel表文件的数据边界
详细的debug统计信息
合并全部Excel表文件到单一的Mysql数据表
默认不需要对数据库进行操作

使用方法

使用对象

tool_excel2mysql.R，这是通用的转换工具，其他脚本面向特殊的需求。推荐使用** R Studio **运行此代码。

所需要的软件和开发环境

即需要Mysql的环境+Mysql的可视化工具+R环境+R可视化开发工具，上述所有软件均可在网上找到。

注意：更新使用 Navicat 11 premium，原版本太过老旧。百度网盘：https://pan.baidu.com/s/18zg6NNogRVRHHD-fEj9UCg 提取码：cbkj

安装所需要的程序包

# 注意这部分仅运行一次即可
# near line 8
#install.packages("RMySQL")
#install.packages("stringr")
#install.packages("readxl")
#install.packages("readr")

修改工作路径

1 2	# near line 14 setwd("C:\\Users\\zonglin\\OneDrive - Universiteit Utrecht\\Desktop\\ecProject\\")

<< 更多精彩尽在『程序萌部落』>>
<< https://www.cxmoe.com >>

修改Mysql配置

# 默认数据库名称: test
# near line 17
conn = dbConnect(MySQL(), user = 'root', password = 'root', dbname = 'test',host = 'localhost')

# 默认生成的表名称: tb_from_excel
# 如果需要修改，可以直接在代码中全局替换这个字段
# use editor's find/replace function to replace it all.

选择合适的起始列

# 默认起始列数: 1
# 可以自由设置转存的起始列
# near line 222
tmp = transExcel2MysqlDB(tmpPath, cnt, startmark = 1)

设置仅测试部分数据

如果你有大量的Excel文件，且你只想测试此代码或使用日志中的errinfo with finally来捕获Excel的调试信息（可以打开或打不开），则可以修改以下部分。其只加载有限的表数据。

1 2	# near line 85 edata <- edata[30:35,]

日志 (processRecord.csv)

errinfo with summary

这是插入SQL失败的记录。如果使用文件夹包含多个Excel，则每个Excel都可以输出单独的errinfo with summary。使用此缓存信息，我们可以在Navicat的帮助下自动的找到错误的sql位置。

然后你可以修改 tool_excel2mysql 的代码内容来修复或者直接给我相关反馈。

errinfo with finally

这部分是全局信息，包含无法读取的Excel表信息和最终的统计数据。如果此处显示了某个excel，那你需要手动检查此文件以查找真正的问题。有时重新保存（打开然后保存）就可以解决不能读取的问题。

基本上来说，该工具可以顺利地将数据从我的xls、xlsx文件转存到mysql，并且成功率几乎达到100％。（上面的截图是为了演示errinfo的例子）

English Version

赶紧使用一下吧…

😒 留下您对该文章的评价 😄