Como faço para limpar ~ 500.000 registros no Excel 2010 sem danificar meu PC?

Question

Eu tenho que remover grandes pedaços de informação das tabelas em arquivos .xlsx usando o Excel 2010. Todos os métodos que tentei até agora não produziram resultados, falharam ou eu os matei depois de esperar por um dia.

Alguém criou as planilhas usando um servidor SQL. Eu tenho que analisar os dados, mas para fazer isso eu tenho que remover todos os dados ruins e depois visualizá-los. Eu pedi o cara servidor para filtrar em sua extremidade, mas ele diz que apenas uma pessoa na empresa é competente para fazer isso e eles estão muito ocupados. Não posso comentar isso porque não sei nada sobre bancos de dados.

Existem aproximadamente ~ 500.000 registros na planilha típica. Eu tentei remover todos os valores ruins manualmente usando um filtro, mas meu PC trava quando eu apago os registros ruins.

Eu transformei os arquivos .xlsx em arquivos .csv porque acho que eles são mais simples e, embora pareçam mais rápidos, eles ainda travam.

Eu escrevi um script VBA, que tentei deixar em execução por vários dias sem sucesso:

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

E eu também escrevi um arquivo .bat usando o findstr como um tipo de filtro, mas alguns registros misteriosamente desaparecem e não sei por quê. O formato geral é:

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

Obrigado ao @EBGreen por sugerir o PowerShell e me mostrar como usá-lo; Embora pareça simples agora, eu nunca teria conhecido / pensado em usar o PowerShell sozinho!

Veja o que eu fiz:

1. Crie o arquivo .csv
2. Adicione um filtro no Excel e remova todas as entradas com o tipo I fazer desejar
3. Copie a lista filtrada de entradas com tipos que eu não quero e remova duplicatas
4. Salvar em not_good.txt

5. Execute este script do PowerShell:

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

O roteiro leva segundos e eu posso usá-lo para todas as outras planilhas que eu tenho.

    

Answer 2

Se você deseja carregar muitos dados no Excel, use o Obter & Transforme ferramentas de poder . (Para o Excel 2010-2013, use o suplemento gratuito do MS Power Query for Excel .)

Com esta ferramenta você tem a possibilidade de acessar muitas fontes diferentes (CSV, arquivos do Excel, banco de dados, web, ...) sem a necessidade de escrever código. Além disso, transformar seus dados ou combiná-los com outras fontes é muito fácil.