Writing to excel file line by line from loop - only last line is written

Question

The goal is to be able to take a messy list of e-mails with unneeded characters and trim it down, then write it to a spreadsheet to do some other work on it. I'm iterating over a text file and doing the trim work, and then writing the result line by line.

For reference, this is working properly, when the excel com object is not included and just using Out-File:

$untrimmedEmails = 'S:\PS_Scripts\Data\trimEmails.txt'
foreach ($line in Get-Content $untrimmedEmails) {
    $row = 1
    $newLine = $line.Split(':')[1]
    Out-File -FilePath .\Notes\trimResults.txt -InputObject $newLine.trimEnd('>') -Encoding ASCII -Append
    $row++
}

But when trying to use excel com object to write to each cell, just this minor change results in only getting output of the last line:

$untrimmedEmails = 'S:\PS_Scripts\Data\trimEmails.txt'
& {
    $excel = New-Object -ComObject Excel.Application
    $wb = $excel.Workbooks.Open('S:\PS_Scripts\Data\Leavers.xlsx')
    $ws = $wb.WorkSheets.Add()
    foreach ($line in Get-Content $untrimmedEmails) {
        $row = 1
        $newLine = $line.Split(':')[1]
        $ws.cells.item($row, 1).Value2 = $newLine.TrimEnd('>')
        $row++
    }
    $wb.SaveAs($CSVImportFile, 6)
    $wb.Close()
    $excel.Quit()
}
[GC]::Collect()

Any ideas what's going wrong? I'm under the impression that the loop is working properly because I am seeing the LAST line written... so I am inclined to believe it's an issue with the worksheet object...

Answer 1

Move the initial assignment to $row out of the loop - otherwise you're resetting the row offset on each iteration:

$untrimmedEmails = 'S:\PS_Scripts\Data\trimEmails.txt'
& {
    $excel = New-Object -ComObject Excel.Application
    $wb = $excel.Workbooks.Open('S:\PS_Scripts\Data\Leavers.xlsx')
    $ws = $wb.WorkSheets.Add()
    # initialize row offset before entering the loop
    $row = 1
    foreach ($line in Get-Content $untrimmedEmails) {
        $newLine = $line.Split(':')[1]
        $ws.cells.item($row, 1).Value2 = $newLine.TrimEnd('>')
        $row++
    }
    $wb.SaveAs($CSVImportFile, 6)
    $wb.Close()
    $excel.Quit()
}
[GC]::Collect()