스프레드시트에 있는 텍스트 파일의 데이터를 자동으로 구성하려면 어떻게 해야 합니까?

Question

다음과 같은 텍스트 파일이 있습니다.

다음과 같이 Excel로 구성하고 싶습니다.

두 개의 데이터 항목에 대해 수동으로 이 작업을 수행할 수 있지만 더 큰 규모로 자동화할 수 있기를 바랍니다. 지금까지 제가 시도한 것은 Excel에 내장된 기능을 사용하여 데이터를 Excel에 입력하는 것이었지만, 여기서 막혔습니다. 열을 행으로 바꾸려고 했지만 원하는 대로 깔끔하게 정리되지 않았습니다. 코딩을 해야 합니까, 아니면 내장된 기능이 있습니까? 코딩이 필요한 경우 누군가 나에게 올바른 방향을 알려줄 수 있습니까?

시간 내 줘서 고마워!

Answer 1

이를 위해 파워 쿼리 추가 기능을 사용해 보겠습니다. 데이터를 조작하기 위한 강력한 명령이 있습니다. 아래에 기능이 있지만 파워 쿼리 창에서 단추를 클릭하기만 하면 많은 것을 얻을 수 있습니다. 파워 쿼리에서 시각적으로 작업하는 것은 일반적으로 VBA에서 코딩하는 것보다 훨씬 쉽습니다.

나는 이 특별한 도전에 도전했고 성공했습니다. 내 OneDrive에서 데모 파일을 다운로드하여 사용할 수 있습니다.

http://1drv.ms/1AzPAZp

파일은 다음과 같습니다. 파워 쿼리 데모 - 행을 columns.xlsx로 재구성

ReadMe 시트에 설명된 대로 간단한 함수 하나만 작성하면 되었습니다. 나머지는 UI를 클릭하는 것뿐이었습니다. 파워 쿼리는 훌륭합니다!

자신의 텍스트 파일에 대해 이를 사용하려면 쿼리의 첫 번째 단계를 편집하여 파일을 가리키면 됩니다. 그런데 필요한 경우 파워 쿼리를 사용하여 폴더의 파일을 반복할 수도 있습니다.

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