Author Posts

January 27, 2017 at 8:10 am

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

January 27, 2017 at 8:20 am

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

January 27, 2017 at 8:22 am

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

January 27, 2017 at 10:55 am

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