Export-csv changing names with apostrophes

Tagged: 

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of Bryan Holmstrom Bryan Holmstrom 3 years ago.

  • Author
    Posts
  • #14138
    Profile photo of Bryan Holmstrom
    Bryan Holmstrom
    Participant

    Good afternoon,

    We have 2 servers that we use for production and development.

    When I run the exact same script in the "dev" box the export-csv leaves the users name with apostrophes alone.
    The "Production" box changes the names with an apostrophe with an "?"

    We are using the following:

    $MyTable.rows | Export-Csv -Path $FileName -NoTypeInformation

    Examples:

    In Dev the user is Bryan O'Holmstrom and the export csv file has it as the same
    In Production the user is Bryan O'Holmstrom and the export csv file has it as 'Bryan O?Holmstrom

    Thanks in advance,

  • #14139
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Most likely, you're dealing with a Unicode character that just looks like an apostrophe, and your CSV file has encoded as ASCII. Depending on what you need to do with the resulting CSV file, you can either add "-Encoding UTF8" (or Unicode, or whatever) to your Export-Csv command, or you can make sure the text is all ASCII before exporting it. If you're trying to open the file in Excel, you may need to stick to ASCII; I don't believe it will parse Unicode CSV files properly.

  • #14140
    Profile photo of Bryan Holmstrom
    Bryan Holmstrom
    Participant

    Dont you think it's strange that one server does it right and the other doesn't?

    I sent this to my engineers:

    In production I get 'Stephanie O?Rourk'

    I checked and both versions of Powershell are identical.

    The only difference I see right now is that newk-shared (Dev) has Office 2010 and hostshare103v (Production) does not have Office installed. And the Dev box has .NETFramework 4.0 and Production has .NETFramework 3.5

  • #14141
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Strange, yes, but there's not much I can do to troubleshoot that from here. First thing I would do is check to see if that string contains any multi-byte characters, and then check the encoding of the CSV file. Here's a quick example of one way to test for that, and several examples of Unicode characters that look similar to the ASCII apostrophe:

    function ContainsMultiByteCharacters ([string] $String)
    {
        if ($null -eq $String) { return $false }
        return [System.Text.Encoding]::UTF8.GetByteCount($String) -ne $String.Length
    }
    
    [char[]]$chars = [char]0x02BC, [char]0x275C, [char]0x2019, "'"
    $string1 = New-Object string (,$chars)
    $string2 = "Just a plain old string."
    
    
    New-Object psobject -Property @{
        String = $string1
        ContainsMultiByteCharacters = ContainsMultiByteCharacters $string1
    }
    
    New-Object psobject -Property @{
        String = $string2
        ContainsMultiByteCharacters = ContainsMultiByteCharacters $string2
    }
    
  • #14144
    Profile photo of Bryan Holmstrom
    Bryan Holmstrom
    Participant

    Thank you,

    I found the special characters, they were coming from our AD extract everynight on the users name.

You must be logged in to reply to this topic.