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

This topic contains 5 replies, has 3 voices, and was last updated by Profile photo of David Johnson David Johnson 1 year, 2 months ago.

  • Author
    Posts
  • #30144
    Profile photo of hardbrain
    hardbrain
    Participant

    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:

  • #30145
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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
    
  • #30176
    Profile photo of hardbrain
    hardbrain
    Participant

    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.

    😀

  • #30178
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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.

  • #30190
    Profile photo of hardbrain
    hardbrain
    Participant

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

  • #30300
    Profile photo of David Johnson
    David Johnson
    Participant

    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.

You must be logged in to reply to this topic.