user2026188 user2026188 - 3 months ago 61
PowerShell Question

converting text file to CSV file

i have written some powershell code to read a very large .txt file, select certain lines and put them into a CSV. the problem is that the file is formatted like this:

header1: Data1
header2: Data1
header3: Data1
header4: Data1
header1: Data2
header2: Data2
header3: Data2
header4: Data2


and i need to convert it to this:

Header1,Header2,Header3,Header4
data1,data1,data1,data1
data2,data2,data2,data2


the code is this:

$path = get-location
$textfile = Get-FileName $env:USERPROFILE\Downloads\


$writefile = "$path\data2.csv"
$reader = [System.IO.File]::OpenText($textfile)
$writer = New-Object System.IO.StreamWriter $writefile
$writer.WriteLine('{0},{1},{2},{3}', "Policy","Schedule Type","Retention Level","Host")

for(;;) {

$line = $reader.ReadLine() #
if ($null -eq $line) {
break
}

$data = $line.Split(":")

if ($null -ne $data[0]) {
$newdata0 = $data[0].trimstart(" ")
}
if ($null -ne $data[1]) {
$newdata1 = $data[1].trimstart(" ")
}

if ($newdata0 -eq "Policy") {$writer.WriteLine('{0},{1},{2},{3}', $newdata1,$null,$null,$null)}

if ($newdata0 -eq "Schedule Type") {$writer.WriteLine('{0},{1},{2},{3}', $null,$newdata1,$null,$null)}

if ($newdata0 -eq "Retention Level") {$writer.WriteLine('{0},{1},{2},{3}', $null,$null,$newdata1,$null)}

if ($newdata0 -eq "Host") {$writer.WriteLine('{0},{1},{2},{3}', $null,$null,$null,$newdata1)}

}



$reader.Close()
$writer.Close()


but i end up with this (actual data):

Policy,Schedule Type,Retention Level,Host
FS-Win-Servers-Tokyo-DACS_ONLY,,,
,FULL (0),,
,,infinity (9),
,,,opback03e.options-it.com
DB-Win-Exch2013-ADB11,,,
,INCR (1),,
,,6 months (6),
,,,opback03e.options-it.com
DB-Win-Exch2013-MDB11,,,
,INCR (1),,
,,6 months (6),
,,,opback03e.options-it.com
DB-Win-Exch2013-MDB10,,,
,INCR (1),,
,,6 months (6),
,,,opback03e.options-it.com


i think i am going about this code wrong or maybe just need to find a way to reformat the csv?

Tav Tav
Answer

Your problem is each call to $writer.WriteLine is advancing the line in the destination file that you're writing to. You need to collect the information on each loop but only write once every 4th loop, this might work:

  $loopCounter = 0

  for(;;) {

            $line = $reader.ReadLine() #
            if ($null -eq $line) {
            break
            }

            $data = $line.Split(":")

            if ($null -ne $data[0]) {
            $newdata0 = $data[0].trimstart(" ")
            }
            if ($null -ne $data[1]) {
            $newdata1 = $data[1].trimstart(" ")
            }

            if ($newdata0 -eq "Policy")  {$data1=$newdata}

            if ($newdata0 -eq "Schedule Type") {$data2=$newdata}

            if ($newdata0 -eq "Retention Level") {$data3=$newdata}

            if ($newdata0 -eq "Host") {$data4=$newdata}    


            if (($loopCounter % 4) -eq 3) {$writer.WriteLine('{0},{1},{2},{3}', $data1, $data2, $data3, $data4)}        

            $loopCounter++

        }