Como posso organizar automaticamente os dados de um arquivo de texto em uma planilha?

Question

Eu tenho um arquivo de texto como este:

e eu gostaria de organizar no Excel assim:

Eu poderia fazer isso manualmente para duas entradas de dados, mas gostaria de poder automatizar isso em uma escala maior. O que tentei até agora foi inserir os dados no Excel usando seu recurso integrado, mas é aí que estou preso. Tentei transpor as colunas em linhas, mas isso não organiza tudo como eu queria. Preciso fazer alguma codificação ou existe uma função integrada? Se eu precisar de codificação, alguém poderia me indicar a direção certa?

Obrigado pelo seu tempo!

Answer 1

Eu tentaria o suplemento Power Query para isso. Possui comandos poderosos para manipulação de dados. Existem funções abaixo, mas você pode conseguir muito apenas clicando nos botões da janela do Power Query. Trabalhar visualmente no Power Query geralmente é muito mais fácil do que codificar em VBA.

Eu tentei esse desafio específico e consegui que funcionasse. Você pode baixar e usar meu arquivo de demonstração no meu OneDrive:

http://1drv.ms/1AzPAZp

É o arquivo: Demonstração do Power Query - reorganizando linhas em colunas.xlsx

Conforme descrito na planilha ReadMe, eu só tive que escrever uma função simples - o resto foi apenas clicar na interface do usuário. A consulta avançada é ótima!

Para usá-lo em seus próprios arquivos de texto, basta editar a primeira etapa da consulta para apontar para o seu arquivo. A propósito, você pode até fazer com que o Power Query faça um loop nos arquivos em uma pasta, se necessário.

Answer 2

Powershell de uma forma suja.

$xlsFile="c:\temp\your.xlsx"
$excel = new-object -comobject excel.application
$excel.displayAlerts = $false
$wbs=$excel.workbooks.open($xlsFile)
$ws=$wbs.sheets.item(1)
$ws.cells.item(1,1) = "Customer"
$ws.cells.item(1,2) = "Apples"
$ws.cells.item(1,3) = "Bananas"
$ws.cells.item(1,4) = "Oranges"
$row = 1
$reader = [System.IO.File]::OpenText("c:\temp\your.txt")
try {
    while ($true) {
        $line = $reader.ReadLine()
        if ($line -eq $null) { break }
        if ($line.length -lt 3) {continue}
        switch ($line.substring(0,3))
            {
                "Cus" {
                    $val="'"+$line.split("#:")[1]
                    $row++
                    $ws.cells.item($row,1) = $val
                }
                "App" {
                    $val=$line.split(" ")[1]
                    $ws.cells.item($row,2) = $val
                }
                "Ban" {
                    $val=$line.split(" ")[1]
                    $ws.cells.item($row,3) = $val              
                }
                "Ora" {
                    $val=$line.split(" ")[1]
                    $ws.cells.item($row,4) = $val
                }
            }
    }
}
finally {
    $reader.Close()
}
$wbs.saveas($xlsFile)
$wbs.close()
$excel.quit()
$ws = $null
$wbs = $null
$excel = $null