Get-Contents command inserting unwanted characters

This topic contains 4 replies, has 3 voices, and was last updated by  JC Ruiz 8 months, 3 weeks ago.

  • Author
    Posts
  • #62793

    I am using the Get-ADUser command to retrieve AD info that I then want to use to populate an SQL table.
    I am feeding this output through the Get-Contents command to strip out unwanted double quote characters, however this is also inserting something, please see script and the results below.
    NB: note looking at the results file in notepad looks fine however if I od -c the file I can see all these extra characters.

    Get-ADUser -Filter * -Server DBC -Properties * | select GivenName, Surname, Name, SamAccountName, StreetAddress, City, State, PostalCode, Country, Title, Company, Description, Department, Office, OfficePhone, mail, Manager, Enabled, LastLogonDate, LastKnownParent | Export-Csv -Delimiter ^ D:\Powershell_scripts\AD_into_SQL\Output\DBC;
    Get-Content D:\Powershell_scripts\AD_into_SQL\Output\DBC | ForEach-Object { $_ -replace "`"","" } > D:\Powershell_scripts\AD_into_SQL\Output\DBC_T

    od -c DBC
    0000000 # T Y P E S e l e c t e d . M
    0000020 i c r o s o f t . A c t i v e D
    0000040 i r e c t o r y . M a n a g e m
    0000060 e n t . A D U s e r \r \n " G i v
    0000100 e n N a m e " ^ " S u r n a m e
    0000120 " ^ " N a m e " ^ " S a m A c c
    0000140 o u n t N a m e " ^ " S t r e e
    0000160 t A d d r e s s " ^ " C i t y "
    0000200 ^ " S t a t e " ^ " P o s t a l
    0000220 C o d e " ^ " C o u n t r y " ^
    0000240 " T i t l e " ^ " C o m p a n y
    0000260 " ^ " D e s c r i p t i o n " ^
    0000300 " D e p a r t m e n t " ^ " O f
    0000320 f i c e " ^ " O f f i c e P h o
    0000340 n e " ^ " m a i l " ^ " M a n a

    od -c DBC_T
    0000000 377 376 # \0 T \0 Y \0 P \0 E \0 \0 S \0
    0000020 e \0 l \0 e \0 c \0 t \0 e \0 d \0 . \0
    0000040 M \0 i \0 c \0 r \0 o \0 s \0 o \0 f \0
    0000060 t \0 . \0 A \0 c \0 t \0 i \0 v \0 e \0
    0000100 D \0 i \0 r \0 e \0 c \0 t \0 o \0 r \0
    0000120 y \0 . \0 M \0 a \0 n \0 a \0 g \0 e \0
    0000140 m \0 e \0 n \0 t \0 . \0 A \0 D \0 U \0
    0000160 s \0 e \0 r \0 \r \0 \n \0 " \0 G \0 i \0
    0000200 v \0 e \0 n \0 N \0 a \0 m \0 e \0 " \0
    0000220 ^ \0 " \0 S \0 u \0 r \0 n \0 a \0 m \0
    0000240 e \0 " \0 ^ \0 " \0 N \0 a \0 m \0 e \0
    0000260 " \0 ^ \0 " \0 S \0 a \0 m \0 A \0 c \0
    0000300 c \0 o \0 u \0 n \0 t \0 N \0 a \0 m \0
    0000320 e \0 " \0 ^ \0 " \0 S \0 t \0 r \0 e \0
    0000340 e \0 t \0 A \0 d \0 d \0 r \0 e \0 s \0
    0000360 s \0 " \0 ^ \0 " \0 C \0 i \0 t \0 y \0
    0000400 " \0 ^ \0 " \0 S \0 t \0 a \0 t \0 e \0
    0000420 " \0 ^ \0 " \0 P \0 o \0 s \0 t \0 a \0
    0000440 l \0 C \0 o \0 d \0 e \0 " \0 ^ \0 " \0
    0000460 C \0 o \0 u \0 n \0 t \0 r \0 y \0 " \0

  • #62794

    Olaf Soyk
    Participant

    I assume that "^" is not the best choice for a delimiter. Try this:

    $CSVFile = 'D:\Powershell_scripts\AD_into_SQL\Output\DBC.csv'
    Get-ADUser -Filter * -Server DBC -Properties * | select GivenName, Surname, Name, SamAccountName, StreetAddress, City, State, PostalCode, Country, Title, Company, Description, Department, Office, OfficePhone, mail, Manager, Enabled, LastLogonDate, LastKnownParent | Export-Csv -Delimiter ';' $CSVFile -NoTypeInformation
    Import-Csv $CSVFile
  • #62796

    JC Ruiz
    Participant

    But they are wanted characters !!!
    They are UNICODE. Needed to represent most of the characters in many cultures in the world...
    Usually 2 bytes per char.
    And PowerShell writes in UNICODE by default.

    However, if you need to create your file in other encoding, you can use the Out-File cmdlet, instead of the > operator.
    With it you can provide several character encodings.
    (unknown,string,unicode,bigendianunicode,utf8,utf7,utf32,ascii,default,oem)

    Try

    Get-Content D:\Powershell_scripts\AD_into_SQL\Output\DBC |
      ForEach-Object { $_ -replace "`"","" }  | 
        Out-File D:\Powershell_scripts\AD_into_SQL\Output\DBC_T -Encoding ASCII
    
  • #62809

    Thanks JC that is just the solution I needed, rather new to this PowerShell stuff!

  • #62812

    JC Ruiz
    Participant

    Glad to help...

You must be logged in to reply to this topic.