↑ 画面不动?没声音?点这里!(教程

花了两天时间写了一个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

安装所需要的程序包

1
2
3
4
5
6
# 注意这部分仅运行一次即可
# 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\\")

修改Mysql配置

1
2
3
4
5
6
7
# 默认数据库名称: 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
2
3
4
# 默认起始列数: 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


赶紧使用一下吧…



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



 评论




全站共计 295.4k 字,自豪地使用 Volantis 主题

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议