locked
How to export out the date into the csv file? RRS feed

  • Question

  • Hi, I had been trying to export out the value of the date to csv file.

    This is the  script:

    $strADPath = 'LDAP://dc=test,dc=com'
    
    function ConvertLargeIntegerToDate([object]$LargeInteger){
    
        try
        {
            $int64 = ConvertLargeIntegerToInt64 ($LargeInteger)   
         
            if ($int64 -gt 0)       
            {
              $retDate = [datetime]::fromfiletime($int64)
            }
            else
            {
                $retDate = $null
            }
            return $retDate
         }
         catch
         {
            return $null
         }
    
        
     }
     
    $objSearch = New-Object DirectoryServices.DirectorySearcher
    
    $objSearch.Filter = '(&(objectClass=user)(samaccountname=user1))'
    
    $objSearch.SearchRoot = $strADPath
    
    $objSearch.PageSize = 1000
    $objSearch.SearchScope = "subtree" 
    $objResults = $objSearch.Findall()
    
    
    $dateAccountExpires = ConvertLargeIntegerToDate $objUser.accountexpires[0]
    Write-Host "date Account expires: "  $dateAccountexpires
    
    $objResults| ForEach-Object{                    
              $_.GetDirectoryEntry()
         } | 
    Select-Object -Property @{Name="sAMaccountName";Expression={$_.sAMAccountName}},
    @{Name="cn";Expression={$_.cn}},
    @{Name="name";Expression={$_.name}},
    @{Name="manager";Expression={$_.manager}},
    @{Name="givenName";Expression={$_.givenName}},
    @{Name="accountExpires";Expression={$_.dateAccountExpires}},
    @{Name="department";Expression={$_.department}} |
    Export-Csv -Path 'D:\test44.csv'
    

    This is what I get in PowerShell ISE:

    This is what I had get for the csv file for the expire date:


    Tuesday, May 27, 2014 8:14 AM

Answers

  • Hi noobycy,

    you can do this by replacing that select block close to the end with this one:

    Foreach-Object {
    	$temp = $_.Properties
    	$res = @{ }
    	$properties = $temp.Get_PropertyNames()
    	$properties | %{ $res.$_ = $temp.Item($_) }
    	New-Object PSObject -Property $res
    }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by noobycy Tuesday, June 3, 2014 6:59 AM
    Friday, May 30, 2014 6:38 AM

All replies

  • hi noobycy,

    you messed up a little:

    @{Name="accountExpires";Expression={$_.dateAccountExpires}},

    when it should be

    @{Name="accountExpires";Expression={$_.AccountExpires}},

    because the return object does not have a property named "dateAccountExpires". That's what you called the variable you used to write that line to the command-line.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, May 27, 2014 8:56 AM
  • Hi, i had solved the problem by this code:

    @{Name="accountExpires";Expression={convertToIntegerToDate($_.AccountExpires)}},

    But there got one question in my mind, am i able to loop out all the attributed and values without writing all the attributes name out and export them to the csv file?

    If yes, where can i see the website for or what is the code for, I had been searching for it but all of the reasult is not what I want.. 

    With lot of thanks,

    noobcy

    Thursday, May 29, 2014 11:23 PM
  • Hi, i had solved the problem by this code:

    @{Name="accountExpires";Expression={convertToIntegerToDate($_.AccountExpires)}},

    But there got one question in my mind, am i able to loop out all the attributed and values without writing all the attributes name out and export them to the csv file?

    If yes, where can i see the website for or what is the code for, I had been searching for it but all of the reasult is not what I want.. 

    With lot of thanks,

    noobcy

    You can find all the returned property names in:

    $_.Properties.PropertyNames

    Just loop through it to list out all the attribute values.

    Thursday, May 29, 2014 11:26 PM
  • Hi noobycy,

    you can do this by replacing that select block close to the end with this one:

    Foreach-Object {
    	$temp = $_.Properties
    	$res = @{ }
    	$properties = $temp.Get_PropertyNames()
    	$properties | %{ $res.$_ = $temp.Item($_) }
    	New-Object PSObject -Property $res
    }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    • Marked as answer by noobycy Tuesday, June 3, 2014 6:59 AM
    Friday, May 30, 2014 6:38 AM
  • hi, it did export out all the attributes but I also wanted the attributes value to be able to loop out at the same time.

    with lot of thanks

    noobcy

    • Edited by noobycy Monday, June 2, 2014 10:54 PM
    Monday, June 2, 2014 10:48 PM
  • hi FWN,

    the code had giving me error saying that it could not call method on a null-value expression.

    $temp = $_.Properties

    the code had gave error saying that it could not call method on a null-value expression.

    $properties | %{ $res.$_ = $temp.Item($_) }

    the code had gave error saying that it could not call method on a null-value expression.

    with lot of thanks

    noobcy

    Monday, June 2, 2014 10:54 PM
  • Hi noobcy,

    if by "attributes value [...] loop out" you meant to extract all arrays, so you don't get ugly System.String[] in your csv, you can do that by joining the values in the foreach-object segment like this:

    $properties | %{ $res.$_ = $temp.Item($_) -join " # "}

    If there's only one value, no harm done. If you have multiple values, they ought to get joined by the string behind -join. Feel free to chose a more pleasing pattern, but beware not to use your Csv Delimiter ("," by default) in there or you might break the csv file.

    Error Message

    You can probably fix that by encapsuling the foreach-object content in an if clause:

    if ($_.Properties -ne $null)
    {
    	$temp = $_.Properties
    	$res = @{ }
    	$properties = $temp.Get_PropertyNames()
    	$properties | %{ $res.$_ = $temp.Item($_) }
    	New-Object PSObject -Property $res
    }

    this ought to catch empty return objects, which means you don't try to call a method on an object that is NULL.

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, June 3, 2014 7:30 AM
  • Hi Fred,

    Does that mean that I can't retrieve the attributes that is null value?

    Can I ask where can I study study regards about why attributes that is null value is unable to call out? and why powershell had some extra attributes that do not show in ADSIedit. Sorry to trouble you because I do not know which forum is suitable to ask this question.

    With lot of thanks

    noobycy


    • Edited by noobycy Wednesday, June 4, 2014 1:41 AM
    Tuesday, June 3, 2014 10:55 PM
  • Hi noobycy,

    not to worry, you can retrieve all attributes of an object, even null ones. The error was probably thrown when your query had a return object that did not have any properties at all.
    Another thing though:
    $dateAccountExpires = ConvertLargeIntegerToDate $objUser.accountexpires[0]
    Write-Host "date Account expires: "  $dateAccountexpires
    These two lines don't make much sense to me, since you never create a variable $objUser and thus this should write an empty date to the console (or throw an error).

    You can research errors simply using the powershell console, by entering each line above the step where the error occurs (Errors usually write in which line they occured) manually or by Importing a script containing everything above that line. Then you can inspect what information you are actually passing into the command ($objResults in your case), or send only some of it through the next command and isolate the particular information-element that causes the error.

    For this, you will need some basic knowledge:

    Cheers and good luck,
    Fred


    There's no place like 127.0.0.1

    Wednesday, June 4, 2014 6:53 AM
  • Hi Fred,
    I had been trying to code out the null attributed result by using your given codes and change a bit for the null value attributes
    Code (sorry that it is all black because I am currently using my phone to reply)
    $objResults | foreach-object {
    $temp= $_.properties if (where-object {$temp.values -eq $null})
    {
    $nullres = @ { }
    $nullproperties = $temp.get_propertyname()

    #write host is to check write am I successful to calling out the attributes or not
    Write-host 'null properties: ' $nullproperties
    $properties | % {$nullres.$_ = $temp. Item ($_) -join " # " }
    New-object PSObject -property $nullres }
    Else {
    Write-host "Unsuccessful calling" } }

    Result: unsuccessful calling

    Did I code the where-object command wrongly? Regards about the dateAccountExpire I had create variable $objUser just that forgot to paste as I do not know why I can't use my company's Internet to reply..
    Thanks for your patience,
    Noobycy

    • Edited by noobycy Thursday, June 5, 2014 7:36 AM
    Thursday, June 5, 2014 7:30 AM
  • Hi noobycy,

    yeah, you did mess up a bit on the where command and the general logic is concerned. One minor rule ...

    if/else conditionals work well, but each of those commands is the first word of the line. Period.

    Another problem is that the return collection from a DirectorySearcher does not handle like a simple array where it's childsplay to apply Where-Object. Sooo ... I did a little rewrite which, while not exactly elegant, will probably work for your issue:

    $objResults | Foreach-Object {
    	$names = @()
    	$pairs = @()
    	$props = $_.Properties
    	$props.PropertyNames | ForEach-Object { $pairs += New-Object PSObject -Property @{ Name = $_; Value = $props.Item($_) } }
    	$names += $pairs | Where { (($_.Value -eq $null) -or ($_.Value -eq "") -or (($_.Value.Length -eq 1) -and ($_.Value[0] -eq $null))) } | Select -ExpandProperty "Name"
    	if ($names.length -gt 0)
    	{
    		Write-host 'null properties: ' $names
    	}
    	Else
    	{
    		Write-host "Unsuccessful calling"
    	}
    }

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Thursday, June 5, 2014 8:12 AM
  • Hi Fred,
    It is unsuccessful calling...
    Monday, June 9, 2014 1:29 AM