2019年5月18日之从Excel导入数据到Oracle

有一个需求，需要将一个系统的excel数据导入到我们的数据库中的某张表。

首先，确认对应关系

然后，拼接SQL

目标SQL是如下这种

insert into XX_problem (id,problemno,ORIGINALNUMBER, PARTSNAME,PROBLEMSOURCE,STATUS, PROJECT,TITLE,CREATEDT,PROBLEMDESC,DUTYSUPPLIER,SEVERITY,FACTORY,AGENT,LEADDEPT,CAUSEFINISHDT,CAUSEDESC,STEMPMEASURE,PMEASURE,CHANGENO,STEMPMEASUREFINISHDT,PMEASUREFINISHDT,SMEASUREFINISHDT) select gpis_fi_problem_sq.nextval,concat('MI-',(select case when max(to_number(substr(t.problemNo, -7, 7))) = 9999999  then '0000001' else lpad(nvl(to_char(max(to_number(substr(t.problemNo, -7, 7)))+1),1) , 7 , '0') end from gpis_fi_problem t where substr(t.problemNo,0,3) = 'MI-')),'GX713201412021120', '空调主机总成(电动)(GX7)',  'Fracas',   '4/4',  'RX6453K01',    '关于GX7车系空调主机冷暖转换开关无法调节问题的信息反馈', '', '空调冷暖转换开关无法调节', '重庆超力高科技股份有限公司',    'B',    '成都制造厂',    '张忠钰',  '', to_date('2015/01/17', 'yyyy/mm/dd'),    '产生原因:查询控制盘供应商生产巡检记录，发现有43pcs产品尺寸不合格（∮26.85）；
经调查，当时供应商为赶产量将控制盘的成型周期调快（6月8日12：00），发现不合格后立即将成型周期还原（ 6月8日14：00 ）。供应商将该43pcs产品后经用铰刀扩孔返修。;流出原因:但返修后未进行全部检查，抽检部分返工产品合格且未记录，所以导致不合格品产品流出。    ', '更换故障件',    '1.固定成型周期，要求产线不得擅自更改成型参数
2.重新制作温度控制盘通止规，考虑天气温度变化的因素将通止规尺寸
按∮27.10+0.10/-0.05制作，后续生产100%全检OK后才可以出货。完成日期：2014.10.24
3.温度控制盘该孔径生产时使用通/止规进行全检，此项操作加入SOP/SIP进行管控。完成日期：2014.10.24
4. 将此不良现象加入在“品质履历表”中，后续重点管控。 完成日期：2014.11.05',  '205862',   to_date('2015/05/19', 'yyyy/mm/dd'),    to_date('2015/05/19', 'yyyy/mm/dd'),    to_date('2015/05/19', 'yyyy/mm/dd')  from dual;

可以用程序写个导入，不过Insert语句得写好，这个必须是第一步。

用程序写，得有一个模型类，字段就是表头，一一对应，然后使用jdbc批量执行insert语句。

但是，我利用的是Excel强大的处理能力。

使用Excel处理

首先，excel另存为格式，使用逗号分隔的csv格式，接着结合Excel函数做文本替换

需要在表格数据未拼接之前，将特殊字符转义替换

Oracle特殊字符转义

其他数据和符号替换

1、头部使用@@@，用于后续替换前缀

2、="@@@'"&A2&"',"

&是字符串拼接
文本使用双引号引起来
需要将日期使用TEXT函数进行文本格式化处理。
notepad++中替换，如果是要把目标文本替换成三行(多行)，只会替换掉其中第一行，所以需要把三行并为一行替换。

3、="'"&B2&"',"

4、="to_date('"&TEXT(N2,"YYYY/MM/dd")&"', 'yyyy/mm/dd')"

5、在最后加上from dual;后缀

6、如何复制函数的内容：复制，粘贴的时候选择粘贴值

复制到notepad++中做文本替换时：

7、&需要转义，需要将文本中的&替换为'||chr(38)||'拼接，最好在第一步做特殊字符替换

8、将多余的双引号去掉，采用替换为空的方式

需要不断测试：使用第一条sql在plsql中执行，查看是否执行成功

9、导入的时候，会出现有些字段长度不够，需要扩充长度

其他补充说明：

NAME? Excel中损坏的数据删除后者确认，否则影响替换。

总结：

我花了几个小时时间在测试excel函数和验证sql上，中间出了各种坑，还有自己的不小心，比如="to_date('"&TEXT(N2,"YYYY/MM/dd")&"', 'yyyy/mm/dd')"写成="to_date('"&TEXT(N2,"YYYY/MM/dd")&"' 'yyyy/mm/dd')"。

看出区别了吗，少个逗号。。。

有了上面的经验，如果下次再有这种情况，应该在半小时内可以搞完，工作量就是你得拼好第一条sql，后面都是直接执行

xinrong2019 / xinrong2019.github.io