Can't export the result of a loop to .csv

PowerShell Help
1

Hello,

This is my script. It goes through all the scopes of the DHCP server to get the HostName, IpAddress and ClientID (MAC Address) of a machine.

    
$Scopes = Get-DhcpServerv4Scope -ComputerName DHCP1
foreach ($Scope in $Scopes) 
{
Get-DhcpServerv4Lease -ComputerName DHCP1 -AllLeases -ScopeId $($Scope.ScopeId) | Select-Object HostName,IPAddress,ClientID | Sort-Object HostName
} 
| Export-Csv -Path C:\Users\user1\Desktop\export.csv

I’m a novice in Powershell and object-oriented proramming. I don’t know what I did wrong, I just want to export the result of the ForEach loop to a .csv file with 3 colums: HostName,IPAddress,ClientID.

Can you help me please?

If I remove the last pipeline before Export-CSV this is the error message I get:

cmdlet Export-Csv at command pipeline position 1

Supply values for the following parameters:

InputObject:

2

The foreach loop is not part of the pipeline. You should continue your pipeline within the loop and append to the CSV as it’s produced.

$Scopes = Get-DhcpServerv4Scope -ComputerName DHCP1
foreach ($Scope in $Scopes) 
{
Get-DhcpServerv4Lease -ComputerName DHCP1 -AllLeases -ScopeId $($Scope.ScopeId) | Select-Object HostName,IPAddress,ClientID | Sort-Object HostName | Export-Csv -Path C:\Users\user1\Desktop\export.csv -Append
}

or you can collect your results in a variable and pipe them out after the loop completes

$Scopes = Get-DhcpServerv4Scope -ComputerName DHCP1
$results = @()
foreach ($Scope in $Scopes) 
{
$results += Get-DhcpServerv4Lease -ComputerName DHCP1 -AllLeases -ScopeId $($Scope.ScopeId) | Select-Object HostName,IPAddress,ClientID | Sort-Object HostName
}

$results | Export-Csv -Path C:\Users\user1\Desktop\export.csv
3

Hello,

Thank you very much for your help. I tested both scripts. If I put the Export-CSV in the {} loop, the CSV only contains the results from the last scope of the DHCP server.

The 2nd script work flawlessly.

:smiley:

4

In the first first loop, that is the result I would expect if you didn’t add -Append to your export command as shown in the example. Without it, export-csv will just overwrite the file each time the loop runs rather than appending the new content to the existing file.

5

Oooh, nice to know. Everything is in the details!

6

The alternative if you don’t want a variable is to wrap the ForEach loop in an array.
This is crude, but it works, I use it quite a lot with 1 liners straight off the command line:

@(Foreach ($a in $b) {Do-Something $a}) | Export-csv output.csv -Notype

The downside of this approach is that the whole object is built in memory before being dumped to the CSV. If you don’t trap any terminating errors, you lose the whole thing. With the Export-csv -Append, you’ll at least get the data thus far.