テキスト ファイルのデータを自動的にスプレッドシートに整理するにはどうすればよいですか?

Question

次のようなテキスト ファイルがあります。

そして、Excel に次のように整理したいと思います。

2 つのデータ入力については手動で行うこともできますが、より大規模な場合は自動化できればと思います。これまで試したのは、Excel の組み込み機能を使用してデータを入力することでした。しかし、そこで行き詰まっています。列を行に転置しようとしましたが、望みどおりに整理されません。コーディングが必要ですか、それとも組み込み関数がありますか? コーディングが必要な場合は、正しい方向を教えていただけますか?

御時間ありがとうございます！

Answer 1

この場合は、Power Query アドインを試してみることをお勧めします。このアドインには、データを操作するための強力なコマンドがあります。その下には関数がありますが、Power Query ウィンドウのボタンをクリックするだけで多くのことを実現できます。Power Query で視覚的に作業する方が、通常、VBA でコーディングするよりもはるかに簡単です。

私はこの特定のチャレンジに挑戦し、うまくいきました。私のデモ ファイルを OneDrive からダウンロードして使用できます。

http://1drv.ms/1AzPAZp

ファイルは次のとおりです: Power Query デモ - 行を列に再編成.xlsx

ReadMe シートに説明されているように、1 つの簡単な関数を記述するだけで済み、残りは UI をクリックするだけです。Power Query は素晴らしいです!

これを独自のテキスト ファイルに使用するには、クエリの最初のステップを編集して、そのファイルを指定するだけです。ちなみに、必要に応じて、Power Query でフォルダー内のファイルをループすることもできます。

Answer 2

汚いやり方での Powershell。

$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