How do I clean ~500,000 records in Excel 2010 without crashing my PC?

Question

I have to remove large chunks of information from tables in .xlsx files using Excel 2010. Every method I have tried so far has produced no results, either crashed or I've killed it after waiting for a day.

Somebody else created the spreadsheets using an SQL server. I have to analyse the data, but in order to do that I have to remove all of the bad data and then visualise it. I have asked the server guy to filter on his end but he says only one person in the company is competent to do that and they're too busy. I can't comment on that because I don't know anything about databases.

There are approximately ~500,000 records in the typical spreadsheet. I have tried removing all of the bad values manually using a filter, but my PC crashes when I delete of the bad records.

I have turned the .xlsx files into .csv files because I think these are simpler, and although they seem faster, they still crash.

I've written a VBA script, which I've tried to leave running for several days to no avail:

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

And I have also written a .bat file using findstr as a kind of filter, however some records mysteriously disappear and I'm not sure why. The general format is:

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"

Answer 1

Thanks to @EBGreen for suggesting PowerShell and showing me how to use it; although it seems simple now, I would have never known/thought about using PowerShell on my own!

Here's what I did:

1. Create the .csv file
2. Add a filter in Excel, and remove all the entries with the type I do want
3. Copy the filtered list of entries with types I do not want and then remove duplicates
4. Save this in not_good.txt

5. Run this PowerShell script:

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

The script takes seconds, and I can then use it for all of the other spreadsheets I've got.

Answer 2

If you want to load a lot of data into Excel, use Microsoft's Get & Transform power tools. (For Excel 2010-2013, use the free MS add-in Power Query for Excel.)

With this tool you have the possibility to access many different sources (CSV, Excel files, database, web, ...) without the need to write code. Also, transforming your data or combining it with other sources is quite easy.