Extrair dados específicos de um arquivo de texto e importá-lo no Excel

Question

Eu tenho um arquivo .txt com muita informação, mas eu só preciso dos números de Erro Médio Absoluto e Erro Quadrático Médio da Raiz . Como posso automatizar e extrair os números e colocá-los em uma tabela do Excel.

Qualquer ajuda é bem vinda. Linha de Comando, Script, Java, Expressões Regulares, Powershell ...
Estou usando o Windows 8.1

O Hear é uma pequena parte do meu arquivo .txt:

#############################################################################################################
Recommendation Type: ClusterBasedRecommendation (Experiment 1) for User 1 based on 2 friends: 
     Friends: 9, 220
     Distance: normal
     distThreshold: 0.0 / support weight: 0.35 / relevance weight: 0.65
     Highest predicted rating: 0.87 / Lowest predicted rating: 0.0
     Mean Absolute Error: 0.1516666666666667 / Root Mean Squared Error: 0.1552149047825842
     Fetching friends: 16ms  / Computing distances: 0ms  / Filtering friends: 0ms  / Calculate Recommendations: 15ms  / Overall: 31ms 
    Kendall Tau : 
#############################################################################################################
#############################################################################################################
Recommendation Type: ClusterBasedRecommendation (Experiment 1) for User 2 based on 3 friends: 
     Friends: 22, 182, 310
     Distance: normal
     distThreshold: 0.0 / support weight: 0.35 / relevance weight: 0.65
     Highest predicted rating: 1.0 / Lowest predicted rating: 0.0
     Mean Absolute Error: 0.15166666666666664 / Root Mean Squared Error: 0.16581448804143878
     Fetching friends: 1ms  / Computing distances: 0ms  / Filtering friends: 0ms  / Calculate Recommendations: 0ms  / Overall: 1ms 
    Kendall Tau : 
#############################################################################################################
#############################################################################################################
Recommendation Type: ClusterBasedRecommendation (Experiment 1) for User 3 based on 2 friends: 
     Friends: 20, 98
     Distance: normal
     distThreshold: 0.0 / support weight: 0.35 / relevance weight: 0.65
     Highest predicted rating: 0.87 / Lowest predicted rating: 0.0
     Mean Absolute Error: 0.07 / Root Mean Squared Error: 0.07826237921249264
     Fetching friends: 0ms  / Computing distances: 0ms  / Filtering friends: 0ms  / Calculate Recommendations: 0ms  / Overall: 0ms 
    Kendall Tau : 
#############################################################################################################
#############################################################################################################

    

Answer 1

Aqui está uma solução VBA.

Instruções: cole seu texto na coluna A e execute este código VBA.

Sub ParseData()

Dim counter As Long
counter = 2

For Each cell In Range("A1", Range("A1").SpecialCells(xlCellTypeLastCell))

    If Left(Trim(cell), 19) = "Mean Absolute Error" Then
        Cells(counter, 3) = Mid(cell, InStr(1, cell, ":") + 1, InStr(1, cell, "/") - InStr(1, cell, ":") - 1)

    End If

    If InStr(1, cell, "Root Mean Squared Error:") > 0 Then
        Cells(counter, 4) = (Mid(cell, InStr(1, cell, "Root Mean Squared Error:") + 25, 30))
    counter = counter + 1
    End If

Next

End Sub

Aviso: esteja ciente da limitação da precisão decimal do Excel, porque ela é cortada após 11 pontos decimais.

    

Answer 2

Aqui está uma solução de não programação ... Cole o texto em uma única coluna no Excel Filtro automático - texto que contém "Erro médio absoluto" e, como os números que você está procurando estão na mesma linha, você só terá as informações que você precisa então você pode usar o Converter texto para o assistente de coluna para diliminar apenas os números.

São necessárias várias etapas manuais, mas se você não precisar disso automatizado, isso deverá ser feito em um ou dois minutos.