locked
Export to CSV? RRS feed

  • Question

  • Hello,

    I have just started using PowerShell and I have tried to retrive information from Active Directory but not been able to export the information to a csv file.

    I want the script to find all groups named "Finance" and then read the "Info" attribute on this groups and export to a CSV file.

    This is the script:

    function connect{

    $strFilter = "(&(objectCategory=group)(Name=finance*))"

    $objou = New-Object System.DirectoryServices.DirectoryEntry("LDAP://ou=groups,dc=xx,dc=xx")

    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher

    $objSearcher.SearchRoot = $objou
    $objSearcher.PageSize = 1000
    $objSearcher.Filter = $strFilter
    $objSearcher.SearchScope = "Subtree"

    $colProplist = "name", "info"
    foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}

    $colResults = $objSearcher.FindAll()

    foreach ($objResult in $colResults)
        {$objItem = $objResult.Properties
     "Name: " + $objItem.name
     "Info: " + $objItem.info}
    }
    $csvfile="c:\finance.csv"
    connect | export-csv $csvfile

    When I open the csv file it only contains #TYPE system.int32

    Thank you

    Hakan

    Friday, August 13, 2010 10:55 AM

Answers

  • Hi,

     

    Please use this code :

     

    function Get-FinanceGroups
    {
      $objEntry = [ADSI]'LDAP://ou=groups,dc=xx,dc=xx'
      $objSearcher = New-Object System.DirectoryServices.DirectorySearcher($objEntry)
      $objSearcher.PageSize = 1000
      $objSearcher.Filter = '(&(objectCategory=group)(Name=Finance*))'
      $groups = @()
    
      [void]$objSearcher.PropertiesToLoad.Add('name')
      [void]$objSearcher.PropertiesToLoad.Add('info')
    
      $colResults = $objSearcher.FindAll()
    
      foreach ($objResult in $colResults) {
    	$o = New-Object Object
    	$o | Add-Member -MemberType NoteProperty -Name 'Name' -value $objResult.Properties.name[0]
    	$o | Add-Member -MemberType NoteProperty -Name 'Name' -value $objResult.Properties.info[0]
        $groups += $o
      }
    
      $groups
    }
    
    $csvfile=".\finance.csv"
    Get-FinanceGroups | export-csv $csvfile
    

     

    There was two problems :

    1 - Calling $objSearcher.PropertiesToLoad.Add() method returns an integer which is the result you got.

    2- $objSearcher.FindAll() returns a complex object (hashtable). So, you need to extract data from that complex object : the job is done in the foreach.

    Hope this helps.


    Grégory Schiro - PowerShell MVP - PowerShell & MOF
    • Proposed as answer by Grégory Schiro Friday, August 13, 2010 11:41 AM
    • Marked as answer by Boe ProxMVP Saturday, September 11, 2010 2:23 AM
    Friday, August 13, 2010 11:35 AM
  • My bad, that is what I get for doing this early in the morning. This one should work...

    function connect{
    
    $strFilter = "(&(objectCategory=group)(Name=finance*))"
    
    $objou = New-Object System.DirectoryServices.DirectoryEntry("LDAP://ou=groups,dc=xx,dc=xx")
    
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    
    $objSearcher.SearchRoot = $objou
    $objSearcher.PageSize = 1000
    $objSearcher.Filter = $strFilter
    $objSearcher.SearchScope = "Subtree"
    
    $colProplist = "name", "info"
    foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
    
    $colResults = $objSearcher.FindAll()
    
    
    $report = @()
    foreach ($objResult in $colResults)
     {$objItem = $objResult.Properties
    $temp = New-Object PSObject
    $temp | Add-Member NoteProperty Name $($objitem.name)
    $temp | Add-Member NoteProperty Info $($objitem.info)
    $report += $temp
    }
    Return $report
    }
    $csvfile="c:\finance.csv"
    
    connect | export-csv -notypeinformation $csvfile
    
    • Edited by Boe ProxMVP Thursday, September 9, 2010 11:02 AM
    • Marked as answer by Boe ProxMVP Saturday, September 11, 2010 2:24 AM
    Friday, August 13, 2010 12:09 PM

All replies

  • Use this instead:

    function connect{
    
    $strFilter = "(&(objectCategory=group)(Name=finance*))"
    
    $objou = New-Object System.DirectoryServices.DirectoryEntry("LDAP://ou=groups,dc=xx,dc=xx")
    
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    
    $objSearcher.SearchRoot = $objou
    $objSearcher.PageSize = 1000
    $objSearcher.Filter = $strFilter
    $objSearcher.SearchScope = "Subtree"
    
    $colProplist = "name", "info"
    foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
    
    $colResults = $objSearcher.FindAll()
    
    
    $report = @()
    foreach ($objResult in $colResults)
      {$objItem = $objResult.Properties
    $temp = New-Object PSObject
    $temp | Add-Member NoteProperty Name $objitem.name
    $temp | Add-Member NoteProperty Info $objitem.info
    $report += $temp
    }
    Return $report
    }
    $csvfile="c:\finance.csv"
    
    connect | export-csv $csvfile
    

    Friday, August 13, 2010 11:07 AM
  • Hi Boe,

    Thank you for reply.

    I still get the #TYPE system.int32 as the only text when I open my finance.csv.

     

    Friday, August 13, 2010 11:18 AM
  • Hi,

     

    Please use this code :

     

    function Get-FinanceGroups
    {
      $objEntry = [ADSI]'LDAP://ou=groups,dc=xx,dc=xx'
      $objSearcher = New-Object System.DirectoryServices.DirectorySearcher($objEntry)
      $objSearcher.PageSize = 1000
      $objSearcher.Filter = '(&(objectCategory=group)(Name=Finance*))'
      $groups = @()
    
      [void]$objSearcher.PropertiesToLoad.Add('name')
      [void]$objSearcher.PropertiesToLoad.Add('info')
    
      $colResults = $objSearcher.FindAll()
    
      foreach ($objResult in $colResults) {
    	$o = New-Object Object
    	$o | Add-Member -MemberType NoteProperty -Name 'Name' -value $objResult.Properties.name[0]
    	$o | Add-Member -MemberType NoteProperty -Name 'Name' -value $objResult.Properties.info[0]
        $groups += $o
      }
    
      $groups
    }
    
    $csvfile=".\finance.csv"
    Get-FinanceGroups | export-csv $csvfile
    

     

    There was two problems :

    1 - Calling $objSearcher.PropertiesToLoad.Add() method returns an integer which is the result you got.

    2- $objSearcher.FindAll() returns a complex object (hashtable). So, you need to extract data from that complex object : the job is done in the foreach.

    Hope this helps.


    Grégory Schiro - PowerShell MVP - PowerShell & MOF
    • Proposed as answer by Grégory Schiro Friday, August 13, 2010 11:41 AM
    • Marked as answer by Boe ProxMVP Saturday, September 11, 2010 2:23 AM
    Friday, August 13, 2010 11:35 AM
  • My bad, that is what I get for doing this early in the morning. This one should work...

    function connect{
    
    $strFilter = "(&(objectCategory=group)(Name=finance*))"
    
    $objou = New-Object System.DirectoryServices.DirectoryEntry("LDAP://ou=groups,dc=xx,dc=xx")
    
    $objSearcher = New-Object System.DirectoryServices.DirectorySearcher
    
    $objSearcher.SearchRoot = $objou
    $objSearcher.PageSize = 1000
    $objSearcher.Filter = $strFilter
    $objSearcher.SearchScope = "Subtree"
    
    $colProplist = "name", "info"
    foreach ($i in $colPropList){$objSearcher.PropertiesToLoad.Add($i)}
    
    $colResults = $objSearcher.FindAll()
    
    
    $report = @()
    foreach ($objResult in $colResults)
     {$objItem = $objResult.Properties
    $temp = New-Object PSObject
    $temp | Add-Member NoteProperty Name $($objitem.name)
    $temp | Add-Member NoteProperty Info $($objitem.info)
    $report += $temp
    }
    Return $report
    }
    $csvfile="c:\finance.csv"
    
    connect | export-csv -notypeinformation $csvfile
    
    • Edited by Boe ProxMVP Thursday, September 9, 2010 11:02 AM
    • Marked as answer by Boe ProxMVP Saturday, September 11, 2010 2:24 AM
    Friday, August 13, 2010 12:09 PM
  • Hi Grégory,

    Thank you for answer and explanations.

    The script is running but it don't retrive the "info" attribute. I only get the "name" of the group in the csv file.

    Error message:

    TargetObject        :0

    CategoryInfo        :InvalidOperation: (0:Int32) [], RuntimeException

    FullyQualifiedErrorId   : NullArray

    ErrorDetails     :

    InvocationInfo   : {}

    PSMessageDetails    :

    WriteErrorStream    : True

    Exception     : System.Management.Automation.RunTimeException: Cannot index into a null array.

     

     

    Friday, August 13, 2010 12:55 PM
  • Hi Boe,

    Thank you again but I still get #TYPE system.int32  in the csv file.

    Friday, August 13, 2010 1:05 PM
  • Hi Boe,

    Thank you again but I still get #TYPE system.int32  in the csv file.


    Nice, I'm striking out left and right on this one.  I'll take another look at it and see whats going on. Sorry!
    Friday, August 13, 2010 1:10 PM
  • The #TYPE system.int32  in the csv file case is explained in the first point of my first message.

     

    Then, I suppose that the info field is empty for some groups.

    You have to check the field or try using the following lines instead :

        $o | Add-Member -MemberType NoteProperty -Name 'Name' -value $($objResult.Properties.name)
        $o | Add-Member -MemberType NoteProperty -Name 'Info' -value $($objResult.Properties.info)

     

    Hope this helps.


    Grégory Schiro - PowerShell MVP - PowerShell & MOF
    Friday, August 13, 2010 1:13 PM
  • The #TYPE system.int32  in the csv file case is explained in the first point of my first message.

     

    Then, I suppose that the info field is empty for some groups.

    You have to check the field or try using the following lines instead :

        $o | Add-Member -MemberType NoteProperty -Name 'Name' -value $($objResult.Properties.name)
        $o | Add-Member -MemberType NoteProperty -Name 'Info' -value $($objResult.Properties.info)

     

    Hope this helps.


    Grégory Schiro - PowerShell MVP - PowerShell & MOF

    Maybe I misunderstood, were you only getting the #TYPE system.int32 in the CSV, or were you getting that AND the information in the CSV?


    You can just pipe the output from the load to out-null to avoid getting the integers

    $objSearcher.PropertiesToLoad.Add($i)} | out-null

    That will prevent you from getting the #TYPE system.int32 in the CSV.

    Friday, August 13, 2010 1:29 PM
  • Is this still an issue for you?  If so, please let us know and we will continue to work with you to resolve this.

    Friday, September 3, 2010 4:24 PM
  • Hello Boe,

    This is not an issue for me anymore, Sorry for not updating.

    Thank you for support.

    Hakan

    Friday, September 17, 2010 10:56 AM
  • such a simple thing to want to do

    such a totally weird way of achieving it

    non-intuitive

    ..and what's more, it doesn't even work - still

    Why do people put up with this muck ?

    Saturday, March 17, 2012 8:18 PM
  • Half of the issue iis bafd function design and hald is incorrect use of Export-Csv.

    Without including -NoTypeInfo in teh Export-Csv caommand yuo wil always get a #type statement at teh beginning of the file.

    See: HELP Export-Csv -full

    for the compete documentation on how this works.

    The following is much closer to what you are trying to do.

    function connect{
         $searcher=[adsisearcher] '(&(objectCategory=group)(Name=finance*))'
         $searcher.SearchRoot = "LDAP://ou=groups,dc=xx,dc=xx"
         $searcher.PageSize = 500
         $props='name', 'info'
         [void]$searcher.PropertiesToLoad.AddRange($props)
         $searcher.FindAll() |
         ForEach-Object{
              New-Object PSObject -Property
                   @{
                        'Name' =$($_.Properties['name'])
                        ' Info'=$($_.Properties['name'])
                    }
         }
    } 
    $csvfile="c:\finance.csv"
    connect | export-csv $csvfile

    ¯\_(ツ)_/¯


    • Edited by jrv Saturday, March 17, 2012 8:54 PM
    Saturday, March 17, 2012 8:54 PM