手动将excel表中的数据通过sql语句填入数据库

时间:2022-11-06 12:48:22
     此次过程为将实际数据导入管理系统,由于实际数据资料的格式与字段在表中的分布不尽相同,所以需要进行一些处理。通过数据库表中字段与excel表中列对应,在excel表中构建sql语句来导入数据,主要方式为组装模板语句。      步骤如下:      1.导出数据库中表已存在的一条数据的insert格式,为插入数据做铺垫。      2.通过对比excel表中有的字段,删除insert格式中没有的字段,但要保留表的非空字段和基本公共字段(如果有的话)。      3.删除insert格式中values()中的值,根据需要的对应字段一步步填充。      4.将已组装好的语句sql应用到每一行,并直接拷贝到数据库中进行操作。
     通过以上步骤,可以将excel表中的数据与数据库中表的字段对应起来,然后进行填入。      其间可能遇到数据格式不正确,数据不在一张表中,数据字典的转化等多种情况,可以通过excel自带函数进行处理,也可以用sql函数进行处理,也可以再导入后通过update人工进行处理。推荐顺序excle函数>sql函数>人工。 详细过程      1.导出insert格式的sql语句。(以下数据均为虚拟)         以tb_person_base_info表为例         手动将excel表中的数据通过sql语句填入数据库手动将excel表中的数据通过sql语句填入数据库          2.剔除不需要的字段      excel中的数据为第6行,列从“编号”到“退休返聘”为A-M。       手动将excel表中的数据通过sql语句填入数据库    手动将excel表中的数据通过sql语句填入数据库      假设在excel表中有姓名,性别,出生年月,学历,党派,五列对应tb_person_base_info表中的XM,XB,CSRQ,XL,ZZMM,非空和公共字段有PERSON_BASEINFO_ID,IS_DELETED, CREATED_BY_USER, CREATED_DTM_LOC, CREATED_OFFICE, UPDATED_BY_USER, UPDATED_DTM_LOC, UPDATED_OFFICE, RECORD_VERSION。            剔除完不需要字段和values后的值      手动将excel表中的数据通过sql语句填入数据库手动将excel表中的数据通过sql语句填入数据库      3.填充需要的数据      3.1字段对应填充           因为PERSON_BASEINFO_ID为主键,为需要添加项,值为uuid();           XM字段对应excel表中 B6(以下用 A:B表示 数据库表字段A对应Excel表位置B)           XB: D6           CSRQ : E6           XL: F6           ZZMM:G6           公共字段(8个)可根据实际填写,这里暂用之前的数据           0, admin,2016-12-28, ADMIN, admin, 2017-02-07, ADMIN, 0      3.2字段处理           sql中日期格式为"yyyy-mm-dd",这里的CSRQ(出生年月)为1958.2,所以用excel函数进行处理,在excel表N6中,=SUBSTITUTE(E6,".","-")&"-1",,后处理后为 “1958-2-1”可以正确填入。(如果本身就是excel日期格式,用=TEXT(E6,"yyyy-MM-dd"))。           XL(学历)是使用数据字典存入数据库的,假设大学本科毕业对应的字典值为'1',在excel表 O6中,=IF(F6="大学本科毕业 ",1,null)。多个选择可以写IF嵌套,=IF(F6="大学本科毕业 ",1,IF(F6="大学专科毕业",null))。数据较多的话我是用手动更新的。           实际的问题根据Excel函数或sql函数或手动解决。      4.拼接数据并应用      4.1拼接数据           excel中的拼接字符为&,常量字符串放在""中。           在excel表P6中填入拼接好的sql语句 ="INSERT INTO tb_person_base_info (PERSON_BASEINFO_ID,XM,XB,CSRQ,XL,ZZMM,IS_DELETED,CREATED_BY_USER, CREATED_DTM_LOC,CREATED_OFFICE,UPDATED_BY_USER,UPDATED_DTM_LOC,UPDATED_OFFICE,RECORD_VERSION) VALUES (replace(uuid(),'-',''), '"&B6&"','"&D6&"','"&N6&"','"&M6&"','"&G6&"',0, '"&B6&"', '2016-12-28', 'ADMIN', 'admin', '2017-02-07', 'ADMIN', 0);" //这里CREATED_BY_USER,用B6(姓名)存入,作为唯一标识,进行增删改查或与其它表连接有个唯一标识比较方便。       4.2应用语句模板            鼠标移到P6,点击右下角实+号,向下拉,每一行的sql都产生了,然后按ctrl+c复制,就生成了每一行数据的插入,可以复制到txt中查看语句是否正确,再导入到数据库中。