用Access数据库整理清洗150万条数据的经验总结

书香世家

​你看得没错，Access数据库的数据清洗整理能力也是超强的，但是多数人对于Access数据库的数据分析和数据清洗能力其实没有认识，仍然拘泥于用Excel来清洗和处理超过10万行的数据，其实这样做的效率是非常低的。今天我就来讲一讲我用Access数据库来清理150万行销售数据的经验总结。
这个案例的需求产生来自于我的工作，近期我要着重分析一种特定服务类商品的销售数据，因此花了两天的时间从公司的ERP中下载了2017年到2019年总共三年的销售详细数据，由于数据量比较大，因此在导出的时候我采取的方法是按照月份为单位导出的方法。即便如此，导出数据也花了我两天的时间才OK。
因此我要整理的对象是36张表（每年12张表，分别代表12个月的销售数据），总共的数据量有150万余条，每张表5-8万条数据不等。我第一步要做的事情就是把这36张表合并成一张大表：


这一步看着简单，其实不简单，需要使用SQL语言才行。
很多学习Access的朋友，包括我的Access学员在哪，对于表的重要意义认识的并不是很充分，经常无视基础表的可追溯性和数据整洁性，胡乱用Excel合并表格，也不注重细节质量，令我很无语。而且多数人的想法是：能用Excel能解决的事情用不到Access。这样的想法当然是错误的。
可实际情况是Excel能处理150万条数据么？明明105万条数据就到达Excel的极限了，它能做大量数据清洗才是见了鬼了。数据分析讲究的就是严谨和细致，实际上Excel并不是个严谨的软件，至少不是个数据库软件，并不能胜任数据清洗的工作。


上图是用Union Select合并三年的表的SQL语句，看着很舒服，也很清爽，最重要的是这样做出来的合并表，它的可追溯性很好，在今后的数据分析过程中哪里出了问题比较容易顺藤摸瓜的进行定位。这样的数据清洗工作才是真正的数据清洗工作。我希望以后我的学员多拿给我这样的作品，少给我一大堆乱七八糟的Excel表格和奇奇怪怪的需求。
合并完三年的数据之后还存在着两个问题，第一个问题是合并采用的是普通查询，每次打开由于有150多万条数据，Access数据库的速度非常的慢，第二是由于ERP的设计问题，原始数据存在大量的空行，据估算有十几万行之多。
先说第一个问题的解决方案：
我采用了生成表查询的方式，直接把计算结果生成了一张独立的表而非查询，这样就能大大加快处理速度，不用每次都打开一个100多万行的查询。
再说第二个问题的解决方案：
清洗掉多出的空行，直接采用IS NOT NULL的查询语句就可以，具体方法很简单也很清楚。具体细节不过多展开了。
可以说，通过高效的运用Access数据库，比较好的解决了这项数据分析的重要前提——数据清洗工作，为业务数据分析奠定了良好的数据基础。
总结：

• 用Excel处理上百万条数据的清洗工作，是效率很低的工作。建议大数据量处理采用Access进行。
  
• Access处理完上百万条数据后，尽量采用生成表查询生成新表，提高运行速度。
  
• Access的数据处理能力比Excel强悍太多，二者实际并不在同一个水平上，基本没有可比性。
  

風萧路漫

您觉得我是对Excel一无所知么～

博途

数据分析讲究的就是严谨和细致，实际上Excel并不是个严谨的软件，至少不是个数据库软件，并不能胜任数据清洗的工作。

嘎网她爹

我问一下，这30张表是怎么导入Access的，好像只能一张一张导入吧，不写代码的话

大琛

用SQL语句，作者附了图的，图2

苹果味鲜

你是完全没搞清楚状况吧，你得先把这30张表导入到Access ，以后才能使用那个sql语句合并。 至于这一大堆的表 怎么快速导入Access 作者没说，我觉得这是最关键的一步， 要让人产生兴趣，首先你就得解决导入问题

一无心

access能像power query一样，直接合并文件夹下所有工作簿的数据吗？

隨心遠行

[惊喜]估计是私聊博主有报课之类的

奈何桥下孟婆的碗

对呀，这个账号就是卖课的

抱朴子

这个问题很重要。不知道有没有批量方法？楼上回答没有到点。