Export-CSV changing Case of data

This topic contains 5 replies, has 4 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 3 years, 5 months ago.

  • Author
    Posts
  • #11120
    Profile photo of Gerald
    Gerald
    Participant

    In PS v2, I have a simple script obtain some info via Get-ADUser, and write that out to a CSV file.

    The script works, and returns what I'm after. The only issue is one of the fields(Enabled) is being reflected in my csv file as Upper Case, while it should be mixed case.

    When I remove the Export-CSV, the field values are corecctly displayed as True or False. However with the Export-CSV in place, they are returned as TRUE or FALSE.

    Any idea why that would be?

    $Users = Get-Content 'C:\TEMP\Users.txt'

    # Define cumulative array to be used later
    $Merged = New-Object System.Collections.ArrayList

    # Query AD to see if the User exists, and if that User is Disabled or not.
    ForEach ($User in $Users) {
    $Output = New-Object -Type PSCustomObject
    $UserInfo = Get-ADUser -Filter {SamAccountName -eq $User}

    # Build Output record.
    $Output | Add-Member -type NoteProperty -Name UserID -Value $User
    $Output | Add-Member -type NoteProperty -Name GivenName -Value $UserInfo.GivenName
    $Output | Add-Member -type NoteProperty -Name SurName -Value $UserInfo.SurName
    $Output | Add-Member -type NoteProperty -Name Enabled -Value $UserInfo.Enabled

    # Combine all the Users Permissions into one array
    $Merged.Add($Output)
    }

    # Output to CSV file
    $Merged | Export-CSV "C:\Temp\Users_Output.csv" -NoType

    Thanks.

  • #11121
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Not sure what's going on there. I can't reproduce the problem on my machine.

  • #11152
    Profile photo of Kirt Carson
    Kirt Carson
    Participant

    Google "export-csv upper case" returns many results of good reads.

  • #11156
    Profile photo of Gerald
    Gerald
    Participant

    Google does return many results, though unless I'm missing something, nothing relevant that I can see.

    I believe the issue is is related to this field being a Boolean value. I seem to have the same issue when returing any Boolean fields from GET-ADUser.

    I tried using the ToTitleCase() method to work around that, but no luck. Inserting this into my above script –

    $TextInfo = (Get-Culture).TextInfo
    $Output | Add-Member -type NoteProperty -Name Enabled -Value $TextInfo.ToTitleCase($UserInfo.Enabled.ToLower())

    returns this error –

    Method invocation failed because [System.Boolean] doesn't contain a method named 'ToLower'.
    At line:25 char:120
    + $Output | Add-Member -type NoteProperty -Name Enabled -Value $TextInfo.ToTitleCase(
    $UserInfo.Enabled.ToLower <<<< ()) + CategoryInfo : InvalidOperation: (ToLower:String) [], RuntimeException + FullyQualifiedErrorId : MethodNotFound Thanks....

  • #11165
    Profile photo of Joakim Svendsen
    Joakim Svendsen
    Participant

    I am also unable to reproduce this issue, using the exact same code.

    However, your workaround will work if you cast the bool to a string type, like this, for instance:


    $TextInfo.ToTitleCase(([string]$UserInfo.Enabled).ToLower())

    For bonus points, here's a regex match evaluator solution:


    [regex]::Replace('TRUE', '^(.)(.*)', { $args[0].Groups[1].Value.ToUpper() + $args[0].Groups[2].Value.ToLower() })

    In the latter case, replace the string 'TRUE' with the relevant variable ($UserInfo.Enabled).

    If the upper-casing happens within Export-Csv itself, I guess you're out of luck. Odd that it seemingly can't be reproduced.

  • #11392
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    I may have experienced this issue today at work. PowerShell and the Export-Csv cmdlet were working fine, but if you open the CSV file with Excel, it displays the values as TRUE and FALSE (and will save them that way, if you save the file back to disk from Excel.)

You must be logged in to reply to this topic.