Excel表格数据库导入

这次我们通过一个简单的案例，示范一下把既基础又标准的excel文件怎么导入数据库中去。先看一下数据的长相：

这种格式非常标准。A列虽然看起来是YYYY-MM只到月份的形式，但是我们点击A2单元格，看一下展示出来的具体值，它是2021/10/31，这是excel里标准的日期格式。采用三种方法导入数据库中，分别是用ETL工具，在excel中拼接SQL语句，以及用数据库查询工具。

但用任何方法之前，我们先要考虑一下，数据库中的表格式是什么样子。上图是一个关于区域（省份）的数据。最直接的方法就是表有4个值列，1个日期列，表看起来就是：

END_DATE, BEIJING, TIANJING, HEBEI, SHANXI

这种也没错，但上图毕竟只是个截图，全国有那么多的省、市，每个区域都在单独的列上，表结构既不灵活，用起来也很不方便。就拿算个全国平均值来说吧，怎么整？SQL里面要ｓｅｌｅｃｔ (BEIJING + TIANJING + HEBEI +SHANXI + ....)/N 才行。仔细分析一下，列属性的含义是“区域”，所以要把这个抽象出来作为一个维度，数值放在单独的列上，表结构看起来应该是：

END_DATE, REGION, VALUE

有了这一层定义后，先在数据库中把表建出来：

create table REGION_VALUE(END_DATE date,REGION varchar(20), VALUE decimal(10,2) );

在这我们用kettle，新建一个transform，

在Input目录下面，拖拉 Excel Input 节点到右侧画布上，

preview的结果

下面需要在kettle里进行列转行的操作，首先增加一个sort rows节点，按照日期排序，

增加一个列转行的节点，

右键该节点，点击Preview看下数据，

数据已经转成了符合的格式，最后需要把“时间”列重命名一下。

再添加一个 Table output的节点，即可把数据导入数据库的表中，

方法2，拼接SQL语句

这种方式比较直接，就是在excel里面，利用拖拉的功能，把ｉｎｓｅｒｔ语句拼出来，然后copy到数据库查询工具中执行即可。但不适用于行数太多的数据入库。几十万行的ｉｎｓｅｒｔ语句，copy到查询工具中估计都会卡住。

如上所示，在G2单元格中，拼接针对B列，北京，的入库语句。注意黄色背景的是绝对定位，因为B1这个值在拖动的时候不要改变。然后拖动G2往下，铺满日期。

再对“天津，河北，山西”用同样的方法处理，拼接出ｉｎｓｅｒｔ语句。

这是所有方法中最弱的一种。为什么说它“弱”呢？因为这种方法要求数据格式必须长得和库表一模一样。上面两种方法中，我们对数据原始形态都做了一定的调整。但在这种方法下，就不能用原先设计的表结构了，必须换成和原始数据一样的表结构，重新创建一张表：

create table REGION_VALUE_2(END_DATE date,BEIJING varchar(20), TIANJING varchar(20), HEBEI varchar(20), SHANXI varchar(20) );

这里用DataGrid，过程也很简单，找到表，右键，选择 Import Data from File，

再选择我们的文件就可以了。

好了，至此，三种方法的导入就介绍完了。如果对复杂excel的导入有需求，可以参考我名下的其他文章。