☰

如何在不导致电脑崩溃的情况下清理 Excel 2010 中的约 500,000 条记录？

tag-icon • tag-icon microsoft-excel vba batch-file csv database
如何在不导致电脑崩溃的情况下清理 Excel 2010 中的约 500,000 条记录？

我必须使用 Excel 2010 从 .xlsx 文件的表格中删除大量信息。到目前为止，我尝试过的每种方法都没有产生任何结果，要么崩溃，要么在我等待一天后就将其杀死。

其他人使用 SQL 服务器创建了电子表格。我必须分析数据，但为了做到这一点，我必须删除所有坏数据，然后将其可视化。我已要求服务器人员在他的终端进行过滤，但他说公司只有一个人有能力做这件事，而且他们太忙了。我无法对此发表评论，因为我对数据库一无所知。

典型电子表格中大约有 500,000 条记录。我尝试使用过滤器手动删除所有坏值，但删除坏记录时我的电脑崩溃了。

我已将 .xlsx 文件转换为 .csv 文件，因为我认为这些文件更简单，虽然它们看起来更快，但它们仍然会崩溃。

我编写了一个 VBA 脚本，并尝试运行了几天但无济于事：

Sub delete_bad_records()
Dim not_good() As Variant
Dim cell As Excel.range
Dim none As Boolean

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

not_good = Array("example_value", "another one")
none = False
Columns("C:C").Select

For Each element In not_good
    none = False
    Do While Not none
        Set cell = Selection.Find(element, ActiveCell)
        If cell Is Nothing Then
            none = True
        Else
            cell.Rows().Delete
        End If
    Loop
Next element

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

我还编写了一个 .bat 文件，使用 findstr 作为一种过滤器，但是有些记录神秘地消失了，我不知道为什么。一般格式是：

findstr /v "keywords" "original.csv" > "filtered1.csv"
findstr /v /l "specific phrase1" "filtered1.csv" > "filtered2.csv"
findstr /v /l "specific phrase2" "filtered2.csv" > "filtered1.csv"

答案1

感谢 @EBGreen 推荐 PowerShell 并向我展示如何使用它；虽然现在看起来很简单，但我自己永远不会知道/想到使用 PowerShell！

这是我所做的：

  1. 创建.csv文件
  2. 在 Excel 中添加过滤器，删除所有类型为 I 的条目做想
  3. 复制已筛选的类型为 I 的条目列表不要然后删除重复项
  4. 保存于not_good.txt

  5. 运行此 PowerShell 脚本：

    $not_good = Get-Content .\not_good.txt
Import-CSV ".\results.csv"  | ?{$not_good -notContains $_.Type} | Export-CSV ".\results filtered.csv" -NoTypeInformation

该脚本只需几秒钟，然后我可以将它用于我拥有的所有其他电子表格。

答案2

如果要将大量数据加载到 Excel 中，请使用 Microsoft 的获取并转换 电动工具. （对于 Excel 2010-2013，请使用免费的 MS 插件Excel 的 Power Query。

使用此工具，您可以访问许多不同的来源（CSV、Excel 文件、数据库、Web 等），而无需编写代码。此外，转换数据或将其与其他来源相结合也相当容易。

相关内容