none
Special characters - Out-File / Export-Csv RRS feed

  • Question

  • Hi guys.

    I'm trying to export some Active Directory user information to a csv file, however all special caracters are replaced by "?".

    Does anyone know how to solve this problem?

    ConvertTo-Csv -NoTypeInformation -Delimiter "," | % {$_.Replace('"','')}  | Out-File "C:\Suporte\Script\IBM_Maximo\newusers_maximo.csv" -Encoding ascii -Force

    I've tried using all kinds of encoding.

     "Unicode", "UTF7", "UTF8", "UTF32", "ASCII", "BigEndianUnicode", "Default", and "OEM"


    David Soares MCTS:MBS - MCTS - MCITP

    Friday, April 22, 2016 3:30 PM

Answers

  • I solved the problem.
    Someone added user information using the wrong encoding, so the fields was filled with these characters incorrect.

    Thank you for your help.


    David Soares MCTS:MBS - MCTS - MCITP


    Friday, April 22, 2016 9:11 PM

All replies

  • Good Day

    As far as i know Out-File is used for TXT files
    Please try
    ConvertTo-Csv -NoTypeInformation -Delimiter "," | % {$_.Replace('"','')}  | Out-File "C:\Suporte\Script\IBM_Maximo\newusers_maximo.txt" -Encoding ascii -Force

    Regards
    Friday, April 22, 2016 3:39 PM
  • Why would you remove the quotes?

    The character interpretation depends  on what you are exporting. If you try to export binary data you will get very odd output.

    When saving MUI AD fields you must also use Unicode encoding.


    \_(ツ)_/

    Friday, April 22, 2016 4:12 PM
  • Are you trying to export portuguese characters? like ä ?
    Friday, April 22, 2016 4:25 PM
  • I tried to exporting the file as .txt, but it didn't worked. (Using Unicode)

    Names like:

    - Jardim América

    - Anália Franco

    - Angélica

    User properties like name, displayname, surname.


    David Soares MCTS:MBS - MCTS - MCITP

    Friday, April 22, 2016 5:55 PM
  • Of coarse, as a tech you understand that using txt as a file extension has no effect on the file.

    You have to use Unicode on both commands.

    A better method is this:

    $adlist | Export-Csv temp.csv -NoType
    $lines=Get-Content temp.csv | %{$_ -replace '"',''}
    $lines | Out-File final.csv -Encoding Unicode

    Check that $lines has the correct character set.  You may have issues with your output character set do to you culture settings.

    Do not export an binary fields from AD.


    \_(ツ)_/

    Friday, April 22, 2016 6:01 PM
  • The file was exported without special characters in the same way.

    I did some modifications into the code, but doesn't worked.

    Get-ADUser -prop userprincipalname,emailaddress,displayname,POBox,department,title,city,State,country,postalcode,OfficePhone -filt {extensionattribute3 -eq "colaborador" -or extensionattribute3 -eq "terceiro"} | ? {$_.DistinguishedName -notlike "*ou=Conta de Serviço*" -and $_.EmailAddress -ne $null}  | Select @{e={if($_.emailaddress){$_.emailaddress}else{'N/A'}};n='TSPRIMARYEMAIL'}, @{ expression={$_.Surname}; label='TSLASTNAME' },@{ expression={$_.GivenName}; label='TSFIRSTNAME'},@{ expression={$_.DisplayName}; label='TSDISPLAYNAME'},@{ expression={$_.PostalCode}; label='TSPOSTALCODE'},@{ expression={$_.Country}; label='TSCOUNTRY'},@{ expression={$_.StreetAddress}; label='TSADDRESSLINE1'},@{ expression={$_.State}; label='TSSTATEPROVINCE'},@{ expression={$_.OfficePhone}; label='TSPRIMARYPHONE'},@{ expression={$_.Title}; label='TSTITLE'},@{ expression={$_.Department}; label='TSDEPARTMENT'},@{ expression={$_.City}; label='TSCITY'},@{ expression={$_.emailaddress}; label='TSLOGINID'},@{ expression={$_.emailaddress}; label='TSUSERID'},@{expression={$_.emailaddress}; label='TSPERSONID'},@{ expression={$_.POBox}; label='TSCLIENTUSERID'} | ForEach-Object {
    
    $data1 = ''
    $data2 = 'FLEURY'
    $data3 = 'Brazil/East'
    $data4 = 'pt_BR'
    $data5 = 'BR'
    $data6 = 'FLY-00'
    $data7 = 'TSTYPE 1'
    $data8 = 'IBMENDUSER'
    
    
    $_ | 
    Add-Member -MemberType NoteProperty -Name 'TSJOBCODE' -Value $data1 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSLOCATIONSITE' -Value $data2 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSTIMEZONE' -Value $data3 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSLOCALE' -Value $data4 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSLANGUAGE' -Value $data5 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSPLUSPCUSTVENDOR' -Value $data6 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSTYPE' -Value $data7 -PassThru |
    Add-Member -MemberType NoteProperty -Name 'TSGROUP' -Value $data8 -PassThru } | ConvertTo-Csv -NoTypeInformation -Delimiter "," | % {$_.Replace('"','')}  | Out-File "C:\Suporte\Script\IBM_Maximo\newusers_maximo.csv" -Encoding Unicode -Force
    
    '99999999LAST RECORD'| Out-File "C:\Suporte\Script\IBM_Maximo\newusers_maximo.csv" -Encoding Unicode -Append -Force
    
    copy-item "C:\Suporte\Script\IBM_Maximo\newusers_maximo.csv" -destination "\\server\integracao.adibm\newusers_maximo.csv" -force


    David Soares MCTS:MBS - MCTS - MCITP

    Friday, April 22, 2016 7:35 PM
  • Will it work if we replace just the vocals?
    Example
    Replace á with a? and é with e? 
    Friday, April 22, 2016 7:42 PM
  • How do you suggest making the replacement?

    Some examples (Output):

    Ang�lica = Angélica

    FL�An Franco = Anália Franco

    Tatuap� = Tatuapé


    David Soares MCTS:MBS - MCTS - MCITP

    Friday, April 22, 2016 8:08 PM
  • I solved the problem.
    Someone added user information using the wrong encoding, so the fields was filled with these characters incorrect.

    Thank you for your help.


    David Soares MCTS:MBS - MCTS - MCITP


    Friday, April 22, 2016 9:11 PM