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

Question

Eu tenho um arquivo de texto como este:

egostariadeorganizarnoExcelassim:

Eu poderia fazer isso manualmente manualmente para duas entradas de dados, mas gostaria de poder automatizar isso em uma escala maior. O que eu tentei até agora é inserir os dados no Excel usando seu recurso interno, mas é aí que estou preso. Eu tentei transpor as colunas em linhas, mas isso não é ordenadamente organizado como eu quero. Preciso fazer algum código ou existe uma função embutida? Se eu precisar de codificação, alguém poderia me apontar na direção certa?

Obrigado pelo seu tempo!

    

Answer 1

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

Eu experimentei este desafio em particular e consegui fazê-lo funcionar. Você pode baixar e usar meu arquivo de demonstração do meu OneDrive:

link

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

Como descrito na folha Leia-me, eu só tive que escrever uma função simples - o resto era apenas clicar na interface do usuário. O Power Query é ótimo!

Para usá-lo em seus próprios arquivos de texto, basta editar o primeiro passo na consulta para apontar para o arquivo. BTW você pode até obter Power Query para fazer um loop sobre os arquivos em uma pasta, se necessário.

    

Answer 2

Powershell de uma maneira 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