Excel表格内数据,在实际工作业务场景之中,有时希望将Excel表格内数据批量拼接成可执行SQL脚本。
例如图表案例数据,希望每行数据批量转化为可执行的SQL插入数据脚本。
insert into my_table1
(序号,班组,姓名,工资,籍贯,境界,进修级别,工作单位)
values
(1,"01班","卞丽",11764.8064006068,"重庆","仙帝",385,"言家拳")
Excel最常用的文本拼接函数,当属“&”文本拼接符号,该拼接符出现历史最为悠久。通常可以利用该操作符,实现批量拼接数据。使用拼接符合有个痛点,就是需要频繁切换文本数据和拼接符号,当需要拼接的数据量非常多时,拼接工作就显得比较凌乱。
这里介绍ETStrHold函数,来实现SQL脚本批量拼接。同时为了提升数据处理效率,需要TEXTJOIN函数和ARRAYTOTEXT两个函数辅助。
ETStrHold函数有三个参数:
- 参数1:SQL脚本,这里SQL脚本需要应用到两个占位符;"insert into my_table1 ({1}) values({2})" ,占位符使用数字和大括弧标志
- 参数2:填充占位符1的位置;这里使用TEXTJOIN函数,将字段名称拼接起来。
- 参数2:填充占位符2的位置。这里使用ARRAYTOTEXT函数,将数据拼接成数组文本数据,因为文本数据多了两个大括弧,需要借助MID函数去掉首尾的大括弧数据。
最终完整公式为:
ETStrHold("insert into my_table1 ({1}) values({2})",TEXTJOIN(",",,$A$3:$H$3),MID(ARRAYTOTEXT(A4:H4,1),2,LEN(ARRAYTOTEXT(A4:H4,1))-2))
公式向下填充,就可以批量获取执行的数据插入脚本了。
这里需要应用到TEXTJOIN和ARRAYTOTEXT两个函数。这两个函数是office 365函数。2010至2019版本Excel可以使用EFunction插件工具。
ETStrHold函数利用占位符特性,避免了拼接切换拼接符。增强了公式可读性。