namerg
asked on
How to export into CSV after comparing info between CSV and AD users info ?
Hello,
I would like to create a csv with the following values:
Last Name,First Name,Dept Descrip,Job Title,Clock Number
The created CSV is the resultant of a comparison of a File.csv against AD.
Here is the code
I would like to create a csv with the following values:
Last Name,First Name,Dept Descrip,Job Title,Clock Number
The created CSV is the resultant of a comparison of a File.csv against AD.
Here is the code
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($File_User in $FILE){
$i = 0
[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
If ($Search){
:userloop Foreach ($AD_User in $Search) {
$i++
Clear-Host
write-output ""
write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
#write-output " FLEX ID: $($File_User.employeeID)"
write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-output ""
write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
#write-output " FLEX ID: $($AD_User.employeeID)"
write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-output ""
Edit_AD_User
}
}
#SO RIGHT HERE IT NEEDS TO CREATE THE CSV FILE FOR THE
#AD USERS THAT DID NOT PASS THE CONDITIONAL IF ON
#If ($Search) AS MENTIONED ABOVE
Else {" Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"}
}
Thanks for your help,
ASKER
@subsun,
I think your code exports users from $File_User and not $Ad
Thanks,
I think your code exports users from $File_User and not $Ad
Thanks,
You are looking to export the users which are not in AD but CSV right?
ASKER
The logic is this:
For example:
File has lastname, firstname
AD has lastname, firstname
So,IF (file.lastname and file.firstname) equals (AD.lastname and AD.firstname) THEN
"Bingo"
ELSE
"Create a csv file with AD.lastname and AD.firstname
Thanks for your help,
For example:
File has lastname, firstname
AD has lastname, firstname
So,IF (file.lastname and file.firstname) equals (AD.lastname and AD.firstname) THEN
"Bingo"
ELSE
"Create a csv file with AD.lastname and AD.firstname
Thanks for your help,
In that case try...
$Result = @()
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($File_User in $FILE){
$i = 0
[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
If ($Search){
:userloop Foreach ($AD_User in $Search) {
If ($File_User."Last Name" -ne $AD_User.sn -or $File_User."First Name" -ne $AD_User.givenName){$Result += $AD_User}
<Code>
}
}
#SO RIGHT HERE IT NEEDS TO CREATE THE CSV FILE FOR THE
#AD USERS THAT DID NOT PASS THE CONDITIONAL IF ON
#If ($Search) AS MENTIONED ABOVE
}
$Result | Select * | Export-csv c:\Result.csv
ASKER
@Subsun,
On line 6, if that statement is not true it will not continue to the next line, in other words.
If file.lastname equals ad.lastname {
"TRUE, YOU BOTH ARE EQUAL
ELSE
"YOU ARE NOT EQUAL, LET's CREATE a CSV FILE with the AD. LASTNAME.
Thanks,
PS: By the way, do not take the uppercase offensive or personal.
On line 6, if that statement is not true it will not continue to the next line, in other words.
If file.lastname equals ad.lastname {
"TRUE, YOU BOTH ARE EQUAL
ELSE
"YOU ARE NOT EQUAL, LET's CREATE a CSV FILE with the AD. LASTNAME.
Thanks,
PS: By the way, do not take the uppercase offensive or personal.
Well.. I didn't notice that..
Or are you looking for the users which match Last name but no first name and First Name but not last name?
For example, we have following users in AD and you are going to Edit John cash and you want the other three member (Matching either First Name or Last name) Except Sub Sun added to CSV file. Am I correct?
John cash
john cusack
John Cena
Nick cash
Sub Sun
Sorry, I am confused and sleepy.. :-)
If I am not mistaken above condition result will include all users from AD
So,IF (file.lastname and file.firstname) equals (AD.lastname and AD.firstname) THEN
"Bingo"
ELSE
"Create a csv file with AD.lastname and AD.firstname
Or are you looking for the users which match Last name but no first name and First Name but not last name?
For example, we have following users in AD and you are going to Edit John cash and you want the other three member (Matching either First Name or Last name) Except Sub Sun added to CSV file. Am I correct?
John cash
john cusack
John Cena
Nick cash
Sub Sun
Sorry, I am confused and sleepy.. :-)
ASKER
Jajaja, you make me laugh.
Yes, eventually, I will the list of all AD users that does not matc first name, lastname, title,etc,etc, and the CSV will be given to HR, ohh it will be nice if we dump the samaccountname into CSV also.
So, get some rest so you are not confused or sleepy.
Are you from the Ganesh country ? :)
Yes, eventually, I will the list of all AD users that does not matc first name, lastname, title,etc,etc, and the CSV will be given to HR, ohh it will be nice if we dump the samaccountname into CSV also.
So, get some rest so you are not confused or sleepy.
Are you from the Ganesh country ? :)
Try this.. This will give you the list of users which are there in AD but not in CSV..
Ganesh country ???
$Result = @()
$File = Import-Csv "c:\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber -SearchBase "OU=OU,DC=domain,DC=com" | Select-Object sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName})){$Result += $AD_User}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation
Ganesh country ???
ASKER
Hmmm,
Do i need to modify the existing code?
This is what I have and want to keep the existing functionality while I add yours.
Ganesh Country = India
Do i need to modify the existing code?
This is what I have and want to keep the existing functionality while I add yours.
$File = Import-Csv "c:\scripts\ceridian\upiemps_Test.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($File_User in $FILE){
$i = 0
[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
If ($Search){
:userloop Foreach ($AD_User in $Search) {
$i++
Clear-Host
write-output ""
write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
#write-output " FLEX ID: $($File_User.employeeID)"
write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-output ""
write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
#write-output " FLEX ID: $($AD_User.employeeID)"
write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-output ""
Edit_AD_User
}
}
Else {" Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"}
}
Where do i put your code?Ganesh Country = India
You can put it anywhere it's interdependent.. Yes I am from India.. Where are you from?
$File = Import-Csv "c:\scripts\ceridian\upiemps_Test.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName})){$Result += $AD_User}
}
$Result | Select * | Export-Csv C:\report.csv -NoTypeInformation
Foreach ($File_User in $FILE){
$i = 0
[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
If ($Search){
:userloop Foreach ($AD_User in $Search) {
$i++
Clear-Host
write-output ""
write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
#write-output " FLEX ID: $($File_User.employeeID)"
write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-output ""
write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
#write-output " FLEX ID: $($AD_User.employeeID)"
write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-output ""
Edit_AD_User
}
}
Else {" Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"}
}
ASKER
I'm from Colombia.
I think the code is in a loop.
It created the file with no contents, but still spitting "Let's create a file"
I think the code is in a loop.
It created the file with no contents, but still spitting "Let's create a file"
$File = Import-Csv "c:\scripts\file.csv"
$Ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber -SearchBase "OU=OU,DC=COMPANY,DC=com" | Select-Object sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$Result = @()
Foreach ($File_User in $FILE){
$i = 0
[array]$Search = $Ad | ? {(($_.EmployeeNumber -eq $File_User."Clock Number") -and ($_.sn -eq $File_User."Last Name") -and ($_.givenName -eq $File_User."First Name") -and ($_.department -eq $File_User."department") -and ($_.title -eq $File_User."title"))}
If ($Search){
:userloop Foreach ($AD_User in $Search) {
$i++
Clear-Host
write-output ""
write-host " %%%%%%%%%%%% FOUND THE FOLLOWING EMPLOYEE IN FILE %%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-host " LASTNAME: $($File_User."Last Name")" -BackgroundColor green -ForegroundColor black
write-host " FIRST NAME: $($File_User."First Name")" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($File_User."Dept Descrip")" -BackgroundColor green -ForegroundColor black
write-host " TITLE: $($File_User."Job title")" -BackgroundColor green -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($File_User.telephoneNumber)"
#write-output " FLEX ID: $($File_User.employeeID)"
write-host " CLOCK NUMBER: $($File_User."Clock Number")" -BackgroundColor red -ForegroundColor black
write-host " %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" -BackgroundColor green -ForegroundColor black
write-output ""
write-host " &&&&&&&&&&& FOUND THE FOLLOWING EMPLOYEE IN ACTIVE DIRECTORY &&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-host " LASTNAME: $($AD_User.sn)" -BackgroundColor cyan -ForegroundColor black
write-host " FIRST NAME: $($AD_User.givenName)" -BackgroundColor red -ForegroundColor black
write-host " DEPARTMENT: $($AD_User.department)" -BackgroundColor cyan -ForegroundColor black
write-host " TITLE: $($AD_User.title)" -BackgroundColor cyan -ForegroundColor black
#write-output " TELEPHONE NUMBER: $($AD_User.telephoneNumber)"
#write-output " FLEX ID: $($AD_User.employeeID)"
write-host " CLOCK NUMBER: $($AD_User.employeeNumber)" -BackgroundColor red -ForegroundColor black
write-host " &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&" -BackgroundColor cyan -ForegroundColor black
write-output ""
Edit_AD_User
}
}
Else {
Write-output " Lets create a file. No EMPLOYEE Found in ACTIVE DIRECTORY for $($File_User.SN)"
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName })) {
$Result += $AD_User
}
}
$Result | Select * | Export-Csv c:\scripts\ceridian\report.csv -NoTypeInformation
}
}
Don't put it inside the loop.. Did you try my code?
ASKER
Yes, your code is in there...So, where will i put it.
I mean what if you run this code #a38814684.. Are you getting the result?
ASKER
Nada..the report.csv is blank.
Why are you putting -eq and not -ne ?
Why are you putting -eq and not -ne ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is what I have:
$Result = @()
Foreach ($AD_User in $Ad){
If (!($File | ?{$AD_User."Last Name" -eq $_.sn -and $AD_User."First Name" -eq $_.givenName})){$Result += $AD_User}
}
$Result | Select * | Export-Csv c:\scripts\ceridian\report.csv -NoTypeInformation
Nada, report is blank.
Try the code from #a38816537
Open in new window