雕虫小技也重要--数据处理中的电子表格技巧
在数据处理过程中,常遇到一些很棘手的问题。如需要一次性的导入一批非格式化的数据、临时统计、筛选一些参数等工作,如果不写代码,觉得难以实现;写代码,又感到不值得。从多年的职场经验来看,这种鸡肋工作往往还是影响效率的关键点,一个人能力强不强,往往体现在能否灵活、高效地应对这些麻烦。下面,就以几个实例,讲一讲目前公司中盛行的一些“雕虫小技”。 1 向数据库快速导入临时数据应用场景:新增了一批券信息,但是上游提供的数据格式并不满足既有自动导入程序的规范;BOSS要求下班前要看到结果。 1.1 数据样式数据中包含如下几列:
我们数据中,券号是一致的,但是公司名称并不作为关联存储,转而为公司的ID。此外,生效日期也不符合数据库的日期格式。 1.2 自动关联公司ID与公司名称第一步,是把公司名称换为ID。我们从现有数据库中拷贝出公司静态信息表的名称、ID,名称在前、ID在后。 select comp_name,comp_id from tb_comp_2017;
在原始表格“公司名称”后面插入一列,并把上述结果粘贴到本电子表格的尾部,如下图所示。 而后,我们利用VLOOKUP函数自动为B列关联公司ID到C列。 VLOOKUP有四个参数: =VLOOKUP(B2,P:Q,2,FALSE)
回车后发现已经匹配完毕: 使用拖拽C2复选框右下角小黑点的方法,可以迅速填满全列: 1.3 纠正日期格式日期格式数据库可接受以”-“分隔,因此,直接进行替换: =SUBSTITUTE(D2,".","-")
1.4 生成SQL我们采用字符串拼接函数来组装生成SQL =CONCATENATE(A2,",",C2,'",E2,"');")
效果如下: 同样,选中两个单元格G2,H2,下拉选中框右下角的黑点,得到所有语句:
把该语句复制到SQL管理器直接运行,即可插入所有数据。 2 批量规整文件来自传感器的原始数据文件被扔在一个单一的文件夹下,文件名的命名如下: 2.1 枚举文件名借助windows控制台,可以方便的获取文件名: D:Data2017>dir /b *.dat> all.txt
用文本编辑器打开该文件,可以看到文件名: temperature_20150103011816_57F012FA05BA3B97.dat
temperature_20150103014804_547E7DD139696DE7.dat
temperature_20150103084317_2714341909C94ABF.dat
temperature_20150104065907_0193123B757C1664.dat
temperature_20150106091127_4AE0074A458A6D32.dat
temperature_20150106213531_77E51FD259803C6D.dat
temperature_20150107160039_6A7F22120E576FA1.dat
temperature_20150107181009_49575A1F1FA40F3E.dat
temperature_20150108060447_5F8765A566A94390.dat
temperature_20150108164202_1F196C34657D1A44.dat
把所有文件名粘贴到电子表格,在1、2列各粘贴一次。 2.2 分列获取元素使用分列功能,按照分隔符号或者宽度,将文件名分为5列 内容类似:
2.3 产生批处理命令使用字符串操作函数,批量产生批处理命令。 =CONCATENATE("md d:T2017",E1,"",LEFT(C1,8),CHAR(13),CHAR(10))
其中,CHAR(13),CHAR(10)表示回车、换行 =CONCATENATE("MOVE ",A1," d:T2017",CHAR(10))
效果如下: md d:T201757F012FA05BA3B9720150103
MOVE temperature_20150103011816_57F012FA05BA3B97.dat d:T201757F012FA05BA3B9720150103
批量复制公式到所有记录,得到20多万条指令: 2.4 执行批处理文件把生成的指令复制到记事本,另存在原始文件夹下,而后在原始文件夹下执行即可。 结果如下 3 后记程序猿往往专注于代码,对办公系统的了解程度可能不足。我们公司曾经招聘了一名很厉害的毕业生,写代码的速度和质量很高。但是,为了一个简单的问题,编写一次性的代码、脚本,还是没有搞运维的小伙子用电子表格、Notepad++来的快。 在我们公司,把代码用于上述工作是不受鼓励的。因为编程时,对人的耐心与精力消耗很高,这些精力更应该用在刀刃上。 即便熟练掌握了bash、python等脚本技术,与电子表格、NotePad++等GUI工具相比,实现简单功能耗费的精力完全不同。好的员工除了主干的编程技术,必须要把常用的周边软件掌握好,才能避免纠缠于琐碎的事务无法脱身。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |