关于400W+CSV数据要拆成多个Excel文件的问题,应该先考虑的是为什么要拆分?是因为Excel工作表只支持100万+的数据行,所以拆分?还是因为其他原因?
对于前者的情况比较常见,但是,实际上,现在用Excel来处理400w+行的数据,已经完全没有问题了,因为Excel2016的新功能Power Query(Excel2010或Excel2013可到微软官方下载相应的插件)对数据的处理原则上并没有限制,请参考高手对1亿行数据的处理(因为Power Query支持各种数据源,CSV也完全可以轻松解决):
本次测试目的并非与其他数据分析方法对比优劣、而是尝试介绍一种完全基于EXCEL的本地化大数据集处理方式。
分析人员常用的大数据处理方式
本次演示的方式
这种方式的优点
降低成本。减少工具间的切换成本,直接使用Excel作为存储和分析工具。
展现灵活。展现端继续使用Excel,发挥它灵活、自定义程度高的优势。
便于交付。其他方式得到的结果为了便于交付,还要导出为Excel,而现在整个分析流都在Excel内部完成。
结果可交互。PowerPivot相当于一个存储了源数据的OLAP引擎,通过控制切片器等外部筛选条件,可以迅速、动态的查看结果,使用其他方法,可能需要返回分析端改变计算条件重新导出。
向Excel导入大数据,有两种方式:
PowerPivot导入,直接导入,不支持数据转换和清洗操作。
PowerQuery导入,在导入前可以对数据做预处理。
本次使用的测试数据集共有19列,有多列需要进行格式转换和日期提取操作,使用第一种方式,需要导入后在PowerPivot内部进行,使用方式二可以在载入前完成,很明显的是,对于方式二,预处理步骤越多,加载时间会越长。
下图展示了不同量级不同导入方式的耗时情况(单位:秒)
为了直接对比PowerQuery和PowerPivot的加载效率,增加了一个*号方式,这种方式不对数据做任何清洗转换,直接加载到模型,与PowerPivot步骤相同。
现象
对比前两行结果,PowerQuery的数据导入效率与PowerPivot不分伯仲。
PowerQuery没有数据量的限制,而PowerPivot不到导入超过2G的文件。
清洗步骤和数据量的增多,都会显著增加PowerQuery的导入时间,比如一亿行数据,即使三个简单的清洗步骤,用时已经超过了30分钟
PowerPivot导入方式使用的是Access连接器,受限于Access文件本身的限制,不能导入超过2G的数据,这也说明,PowerPivot数据存储能力超过了Access。
PowerQuery是轻型ETL工具,处理大数据集性能不强(基于Excel版本的 PQ)。
如果尝试使用Buffer函数缓存数据,会发现这个缓存过程非常漫长,实际上,Buffer函数并不适合缓存大数据集,因为无法压缩数据,内存可能会很快爆掉。
该测试还有包括数据的压缩比、计算能力等等,希望详细了解的可以参考我相关回答内容。
以上是关于接入400+万CSV的数据的方法,首选考虑Power Query。如果您是因为其他的原因需要对CSV进行拆分,应该进一步根据您的实际情况来考虑具体方法,而不要首先局限于用Access来做中间桥梁的方法。
最后,再来说说Access的问题,Access数据库不支持超过2G的数据,因此,400+万的数据,你先要看一下是否超过2G,如果超过了,就基本不用考虑了。
关注并私信“材料”即可下载60+Excel函数、数据透视10篇及Power系列功能95篇汇总训练材料
Power Query从入门到实战80篇
更多精彩内容,敬请关注【Excel到PowerBI】
让我们一起学习,共同进步!
联系客服