Get-Contents command inserting unwanted characters

This topic contains 4 replies, has 3 voices, and was last updated by Profile photo of JC Ruiz JC Ruiz 5 months, 4 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
    Profile photo of Olaf Soyk
    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
    Profile photo of JC Ruiz
    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
    Profile photo of JC Ruiz
    JC Ruiz
    Participant

    Glad to help...

You must be logged in to reply to this topic.