CODEWITHSUNDEEP
excelpowershell
R. Nader
R. Nader

Reputation: 13

Pipe foreach loop with Export-Excel

The script below is meant to read a list of servers in $ServerName and a list of files in $Myfiles then export the Select-Object criteria to Excel using the ImportExcel module. The problem I am having is the script loops through the 2 files and only outputs the criteria for the last server and file from the 2 lists.

If I change Export-Excel to Export-Csv C:\outputFile.csv -Append the script works as intended and loops through each file on each server and exports all the data. This would be fine but I prefer to use Export-Excel, so I can use Pivot Tables and coloring in my reports.

foreach ($server in Get-Content "C:\MyServers.txt") {
    $ServerName = $server
    foreach ($file in Get-Content "C:\MyFiles.txt") {
        $Myfiles = $file
        Get-ChildItem -Path \\$server\$Myfiles -Force -ErrorAction SilentlyContinue |
            ForEach-Object {
                try {
                    $_ | Add-Member NoteProperty ServerName $ServerName
                } catch {}
                $_
            } |
            Select-Object ServerName, Name, LastWriteTime, Directory |
            Export-Excel -Path C:\outputFile.xlsx
    }
}

Upvotes: 1

Views: 3753

Answers (1)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174485

Assign the entire output to a variable, and then pipe that to Export-Excel at the end:

$data = foreach ($ServerName in Get-Content "C:\MyServers.txt")
{
    foreach ($Myfiles in Get-Content "C:\MyFiles.txt")
    {
        Get-ChildItem -Path \\$ServerName\$Myfiles -Force -ErrorAction SilentlyContinue |ForEach-Object {
            try {
                $_ | Add-Member NoteProperty ServerName $ServerName
            } catch {}
            $_
        } | Select-Object ServerName,Name,LastWriteTime,Directory
    }
}

$data | Export-Excel -Path C:\outputFile.xlsx

Upvotes: 2

Related Questions