查看: 694|回复: 0

[Mysql数据库] Excel表格与数据库的问题

发表于 2018-1-24 08:00:01

整理一下前两天工作中遇到一些最基础的问题

因为工作需求,把Excel表导入数据库并,然后SQL语句进行业务逻辑操作,最后把结果集导出表格。当我听到这个任务时候,第一感觉没什么难度,结果实力打脸。

问题一:Excel表格导入

解决方案一:

按照我学习过的思路,进行逻辑导入,转换成后缀.csv之后文件,打开cmd

格式:

sqlldr userid=system/password control='d:\test.ctl';--这句命令先不执行

我们编辑需要执行的脚本test.ctl

load data

infile 'e:\text.csv'--.csv的位置

replace into table test--导入数据库的表名称,三个参数(replace替换)append(追加)insert(插入空表)

fields termindted by ','--字段分隔符

(a1,a2,a3)--导入的列明

这时候我们在运行第一条指令便可成功插入,本人测试插入失败,有可能表明错误。

解决方案二:

利用PL/SQL进行解决:

工具中输入select * from test where 1=2 for update

出现如下图,打开小锁,我们需要先把Excel内容全选复制,表格中的类型每一列,一定与数据库表类型一样,否则一定会失败!选中我们数据库整个行,粘贴上去就完成,别忘了打小绿色的对号,然后再提交,不能忘!!个人在领导的指导下学习完成,虽然按不是难题,没有真正的实操一切都等于零!

blob.png

解决方案三:

利用PL/SQL自带的导入工具来完成(不啰嗦)


问题二:Excel表格默认科学计数法问题

那么导入之后惊奇的发现,一列number类型的值竟然不对而且表现形式为科学计数法,原因到底在哪里?经过排查并非转换过程中出现了错误,而是原本Excel表格中就是科学计数法!经过百度和学习知道当默认数值超过12位以上,默认是用科学计数法来显示的,也试过了很多方法,那么简单方便高效的方法分享给大家,总结如下

解决方案一:

鼠标选中右击,选择设置单元格,然后自定义类0(文本邮编等都可以),确定就可以变换成数字,不过有时候后面多出来几个零,但是我们相对来就可以进行操作!后缀.xls和.xlsx都可以那么.csv的保存之后再次打仍然为科学技术法未解决!

blob.png

问题三:再次导入一个新表格时候发现日期格式(数字类型)和我数据库类型不一样

解决方案:

1、转换日期格式需要在本列后面加上一空列

2、选中日期列,数据-->分页-->分隔符号-->选择空格-->选中日期-->选择YMD,点击完成转换成功,如下图

转换前:

blob.png

转换后:


blob.png



问题四:工作完成后备份一定不可忘记,数据量小的时候,那么高效方便导出的方法,逻辑备份无疑最好的选择,总结如下

解决方案:

利用数据库逻辑备份exp(CMD命令)

格式:

exp system/tiger@Databases FILE=$path FULL=Y;

解释:

导出整个库需要权限提示是否获取权限

整篇的文章看上去没有太大的技术含量,工作中让人寸步难行,一起总结分享累计经验决定成败



回复

使用道具 举报