需求:导入excel数据到数据库中,有插入有修改,个别字段需要计算转换
1,将需要导入的excel数据按照规则老老实实拼接出一条sql,计算其中需要转换的字段,例如:物料组字段拆分为大中小小小类四个字段,工厂字段映射成本中心字段。
INSERT INTO `xxxxtable` (`factory_code`,`outside_code`,`pack_code`,`pack_name`,`materials_type`,`material_group`,`broad_code`,`center_code`,`min_code`,`mini_code`,`minunit_code`,`pack_spec`,`change_info`,`spec_detail`,`buyer_tpcode`,`wd_info`,`pack_status`)VALUES('','11','301101866','23度苹果味200mL美国1/背标','Z003','301110','30','11','10','0','Z07','1个','[{"number":1,"mcode":"Z07","name":"个","mname":"个"}, {"number":1,"mcode":"Z07","name":"个","mname":"个"}, {"number":1,"mcode":"Z07","name":"个","mname":"个"}]','{"center_name":"个","max_name":"个","min_number":1,"min_name":"个","max_number":1,"center_number":1}','101',NULL,'1000');
2,观察要导入的excel数据,找出变化频繁的字段,变化很少的字段和固定相同的字段,以变化很少的字段做分组筛选,选择其中一个下拉值,先批量生成这一批的sql
3,将第一条可用sql根据频繁变化字段拆分分段,放入不同的单元格中,保留单引号,如果单引号开头,则在前面多打一个单引号
4,编写公式
=CLEAN(CONCATENATE($A$2,A8,$A$3,VLOOKUP(A8,基础映射!G2:I18,3),$A$3,B8,$A$3,C8,$A$4,E8,$A$3,MID(E8,1,2),$A$3,MID(E8,3,2),$A$3,MID(E8,5,2),$A$3,IF(DELTA(LEN(E8),6),"0",MID(E8,7,3)),$A$5))
CLEAN:清除隐藏字符
CONCATENATE:连接字符串
MID:截取字符串
DELTA:判断数值是否相等
VLOOKUP:从基础映射sheet页获取字段映射
VLOOKUP(A8,基础映射!G2:I18,3),其中A8是搜索值, 基础映射!G2:I18,3) 是搜索范围,搜索值必须放第一列,3代表返回匹配的搜索范围哪一列的值
5,检查sql拼接是否正确,如果没问题,下拉公式单元格,将excel所有数据转为插入sql。
6,通过excel筛选变化很少的字段的其他值,修改第三步中拆分的sql片段,公式生成的所有sql语句会自动改为修改后的值,复制到navicat执行即可
该方法同样适用于批量修改字段,同时需要修改映射字段的情况。