Another Empty Pipe Error when Export CSV used

This topic contains 5 replies, has 4 voices, and was last updated by  Jaap Brasser 2 years, 4 months ago.

  • Author
    Posts
  • #10021

    notarat
    Participant

    Powershell 2 user here. Not allowed to use version 3 yet.
    Having a weird error when trying to export

    # Grab Users from List
    
    $Userlist = Get-Content c:\inputfiles\Users.txt
    
    # Iterate through list using Foreach
    
    foreach($User in $UserList) {
    
    # Grab Username, ExServName, Applicable Mailbox info
         get-aduser $User -property name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults | select name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults 
    
    # Export results to out.csv
    } | export-csv out.csv
    

    With

    | export-csv out.csv

    after the last curley bracket, I get the empty pipe element is not allowed error.

    If I place it after mDBUseDefaults but before the last curley bracket, it works, but only records the information from the last user in the list of users.

    Where's my major malfunction?

  • #10023

    Richard Siddaway
    Moderator

    The problem is using foreach.

    This throws the same error

    £> $processes = Get-Process
    £> foreach ($i in 1..5){$processes[$i]} | Export-Csv test.csv
    At line:1 char:38
    + foreach ($i in 1..5){$processes[$i]} | Export-Csv test.csv
    + ~
    An empty pipe element is not allowed.
    + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : EmptyPipeElement

    You are using the foreach keyword rather than foreach which is an alias of Foreach-Object

    if I change my loop to this

    1..5 | foreach{$processes[$_]} | Export-Csv test.csv

    it works

    I think your code needs to become

    Get-Content c:\inputfiles\Users.txt |
    foreach{

    # Grab Username, ExServName, Applicable Mailbox info
    get-aduser $_ -property name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults | select name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults

    # Export results to out.csv
    } | export-csv out.csv

  • #10028

    Dave Wyatt
    Moderator

    You can't pipe the result of a foreach loop to anything (which is different from the ForEach-Object cmdlet). There are a couple of ways you could change your code:

    Preferred method (not saving the entire contents of the Users.txt file in memory, but processing one line at a time:

    # Iterate through list using Foreach
    
    Get-Content c:\inputfiles\Users.txt |
    ForEach-Object {
    # Grab Username, ExServName, Applicable Mailbox info
         get-aduser $_ -property name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults | select name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults 
    
    # Export results to out.csv
    } | export-csv out.csv
    

    Another option to force a foreach loop to pipe its results somewhere (placing the loop into a subexpression, and piping the results of the subexpression to the next command):

    # Grab Users from List
    
    $Userlist = Get-Content c:\inputfiles\Users.txt
    
    # Iterate through list using Foreach
    
    $(
        foreach($User in $UserList) {
    
            # Grab Username, ExServName, Applicable Mailbox info
             get-aduser $User -property name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults | select name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults 
    
            # Export results to out.csv
        }
    ) | export-csv out.csv
    

    And a third option, the same sort of idea (instead of a subexpression, placing the foreach loop into a script block and invoking it, then piping the results of that script block to the next command):

    # Grab Users from List
    
    $Userlist = Get-Content c:\inputfiles\Users.txt
    
    # Iterate through list using Foreach
    
    & {
        foreach($User in $UserList) {
    
           # Grab Username, ExServName, Applicable Mailbox info
            get-aduser $User -property name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults | select name, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults 
    
           # Export results to out.csv
        }
    } | export-csv out.csv
    
  • #10029

    notarat
    Participant

    Thanks for the response!

    I went with

    # Input Users from text file
    $USERS = Get-Content C:\inputfiles\USERS.txt
    
    #pipe variable through to get requisite information and export the information to csv
    $USERS|Foreach{Get-ADUser $_ -Properties * |Select Surname, GivenName, SAMAccountName, Loc, msExchHomeServerName, mDBStorageQuota, mDBOverQuotaLimit, mDBOverHardQuotaLimit, mDBUseDefaults}|Export-CSV -Path C:\outputfiles\UserEMail.csv -notype
    

    Seems to work fine. I just have to putz around in excel to do a

    =RIGHT(E2,13)

    to make the exchange server name a bit more readable. (no biggie)

    I appreciate you taking the time to show me the "why" as much as the "what" when things go wrong.

  • #10030

    notarat
    Participant

    [quote=10028]You can’t pipe the result of a foreach loop to anything (which is different from the ForEach-Object cmdlet). There are a couple of ways you could change your code:[/quote]

    I get those two confused STILL(!) at times. Then I look really dumb, lol.

    Thanks for the examples! It really helps me understand better when I can compare the different ways to gather the data.

    As I said to Richard, I do appreciate it even more when you explain it to me because seeing the solution -with- the explanation of where/why I went wrong.

    That seems to be the best way I have found so far to learn Powershell. (I've never really been a programmer but I am liking powershell more and more each time I learn something new!)

  • #28662

    Jaap Brasser
    Participant

    Alternatively you can also wrap the foreach-construct in the $(..) notation, this will force the entire loop to complete before piping it into the next cmdlet. I do recommend using the pipeline properly, but it might be interesting to know another method of working with foreach:

    `
    $(
        foreach ($i in 1..5) {
            $i
        }
    ) | ForEach-Object { $_ }
    `
    

You must be logged in to reply to this topic.