Excel转换成批量操作sql

需求：导入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
	(
	'2011',
	'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根据频繁变化字段拆分分段，放入不同的单元格中，保留单引号，如果单引号开头，则在前面多打一个单引号

1	拆分
2	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 ( '
3	'','
4	'','Z003','
5	'', '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' );

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执行即可

该方法同样适用于批量修改字段，同时需要修改映射字段的情况。