利用 Power Query + VBA 实现 CSV 数据清洗

2024-01-12 09:03:16

准备工作

CSV 格式数据文件；
Excel（Office 365）；

大致处理思路

1. 数据导入

注意：这里务必使用 Power Query 打开，而不是直接用 Excel 打开。因为后者会自动进行一些无用的格式转换，比如将长文本编号识别为数字（第一列），从而丢失源数据的精度。

2. 主要的数据转换

2.1. 转为【文本】型：

图中圈中部分可以单击，以快速转为某种格式。虽然方便，但当要处理的字段列较多时，频繁单击鼠标也很低效。这里推荐使用【快速访问工具栏】：将转为“文本”型的操作直接添加到该工具栏内，再批量选中要转换的字段列，按组合键【Alt + 2】实现一键转换——

注意：这里可能是【Alt+1】，也可能【Alt+3】，具体加几，取决于命令添加后的相对位置（顺序为从左往右）。

2.2. 字段值的批量提取

对于目标数据前后存在冗余符号的情况（如下图所示）：

Power Query 的【转换】选项卡提供了多种处理途径：既可以使用【替换值】的方式，也可以通过提取【分隔符之间的文本】。不过后者更适合数据较规范的场合，否则对于匹配失败的数据行，该列的提取内容 将变为空文本。

2.3. 批量删除与撤销

选中若干列后，直接【Delete】删除选中列；任何情况下，想要撤回上一步操作，都可以通过【查询设置】工具栏内、【应用的步骤】列出的操作列表进行撤回，非常方便。

2.4. 其他转换问题

除了上述三种常规操作，实操过程中还遇到了新的转换问题，如字典值到字典码的转换（户籍类型的文字描述转为数据库中的类型码）、文本的拆分与转换（把形如 20岁的数据拆成 20 和岁，同时把单位部分转换为字典编码，比如 001）。

虽然 Power Query 也提供了添加条件列（【添加列–常规–条件列】），但设置起来相对费劲，遇到多个类似的列也很难快速套用前面的设置，无法批量操作。

起初的处理方案是使用 Excel 函数公式（如 =LEFT([@年龄], LEN([@年龄]) - 1)），但弊端也很明显：每一列都要批量复制公式，遇到字典编码转换的问题，手写公式本就很长，效率低下且容易出错。

于是想到 VBA 自定义函数，参数只要一个单元格的引用，函数名也可以任意指定。例如户籍地址类型的转换，刚开始效果就不错：

'Convert address type text into dictionary code 
Public Function cvtType(ByVal rng As Range) As String
  Dim txt$, result$
  
  txt = Trim(rng.Text)
  Select Case txt
    Case "本县区"
      result = "01"
    Case "本市其它县区", "本市其他县区"
      result = "02"
    Case "本省其它地市", "本省其他地市"
      result = "03"
    Case "其它省", "其他省"
      result = "04"
    Case "港澳台"
      result = "05"
    Case "外籍"
      result = "06"
    Case Else
      result = ""
  End Select
  
  cvtType = result
End Function

这样，只需要使用公式 =cvtType([@地址类型]) 就能得出结果。

但使用公式会出现另一个性能问题：若遇到稍大一点的数据文件，公式批量复制、批量计算、后期批量选择性粘贴的执行速度将显著下降，严重时甚至卡死 Excel。这是频繁引用单元格区域造成的。

要想彻底解决这个性能问题，通常的做法是借助 VBA 中的数组，通过将参数批量放入 VBA 数组，实现一次读取、内存处理、一次写回，尽可能减少单元格的反复引用、计算结果的反复写入。最后，将处理逻辑写入宏过程，就能实现转换结果“一步到位”：

'Convert address type text into dictionary code:
Sub cvtAddrType()
    Dim rng As Range, col&, arr As Variant, i&, rowMax&, arr1 As Variant, str$
    
    '1. Retrieve data from Range cells
    Set rng = Range(ActiveCell.Address(0, 0)).Offset(0, -1)
    col = rng.Column
    rowMax = Cells(Rows.Count, col).End(xlUp).Row
    arr = Range(rng, Cells(rowMax, col))
    ReDim arr1(1 To rowMax - 1) As String

    '2. Core conversion process
    For i = 2 To rowMax
        Select Case Trim(arr(i - 1, 1))
            Case "本县区"
                str = "01"
            Case "本市其它县区", "本市其他县区", "本市其他区"
                str = "02"
            Case "本省其它地市", "本省其他地市", "本省其他市"
                str = "03"
            Case "其它省", "其他省"
                str = "04"
            Case "港澳台"
                str = "05"
            Case "外籍"
                str = "06"
            Case Else
                str = ""
        End Select
        arr1(i - 1) = str
    Next i

    '3. Write back results
    Set rng = Range(rng, Cells(rowMax, col))
    rng.NumberFormatLocal = "@"
    rng = Application.Transpose(arr1)
    Set rng = Nothing
End Sub

3. 收尾工作

数据清洗结束后，应另存为新的 CSV 文件，作为文本导入工具的新数据源；
为确保导入发生未知异常时，数据库能迅速快速标识已导入的数据，还可以在主键上添加统一规则的前缀，如 jan2020_uuid 表示 2020 年 1 月的历史数据，all2020_uuid 表示 2020 年全年数据；
分批次生成导入数据的 SQL 文件，方便后期查阅，并设置统一规范；
编写数据导入说明文档，提示关键操作，避免重复采坑。

4. 小结

对于结构相同的 CSV 文件，还可以利用内置的 M 公式实现数据清洗的【格式刷】式操作；
Power Query 虽然强大，但也不是万能。需要从实际出发，选择最合适的解决方案；
实际操作过程中，面临新的问题要多问几个为什么，抓住问题的本质（如公式对性能影响的根本原因等），这样才能在实战中逐步提高；
良好的习惯需从平时培养，如英文注释、代码缩进、多留文档等。

码农公寓

利用 Power Query + VBA 实现 CSV 数据清洗

相关背景

准备工作

大致处理思路

1. 数据导入

2. 主要的数据转换

2.1. 转为【文本】型：

2.2. 字段值的批量提取

2.3. 批量删除与撤销

2.4. 其他转换问题

3. 收尾工作

4. 小结

码农公寓

相关背景

准备工作

大致处理思路

1. 数据导入

2. 主要的数据转换

2.1. 转为【文本】型：

2.2. 字段值的批量提取

2.3. 批量删除与撤销

2.4. 其他转换问题

3. 收尾工作

4. 小结

相关文章