Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need to read from a CSV file and AD but create two CSV files

Posted on 2013-01-25
11
Medium Priority
?
792 Views
Last Modified: 2013-01-28
@subsun or @footech,
This is kind of similar of what we have worked in the past.

1. There is a CSV file0
2. There is AD

So, we need to compare first names and last name from AD against the CSV file0, if found a match, create a CSV file1 for the record from AD and second CSV file2 for the record found in the CSV file2. So, the correlation between CSV File1 and CSV file2 is easier.

What i have so far:
$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

Foreach ($AD_User in $Ad) {
[array]$Search = $File | ? {(($_."Last Name" -eq $AD.sn) -and ($_."First Name" -eq $AD_User.givenName))}

Open in new window


Sorry guys, I am still trying to learn this language...
0
Comment
Question by:namerg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 2000 total points
ID: 38820860
CSV for only matching records?? If yes.. Try this..
$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

$CSVmatch = @()
$ADmatch = @()

Foreach ($AD_User in $Ad){
If (($File | ?{$AD_User.sn -eq $_."Last Name" -and $AD_User.givenName -eq $_."First Name"})){$ADmatch += $AD_User}
}

Foreach ($CSV_User in $File){
If (($AD | ?{$_.sn -eq $CSV_User."Last Name" -and $_.givenName -eq $CSV_User."First Name"})){$CSVmatch += $CSV_User}
}
$ADmatch | Select * | Export-Csv C:\ADmatch.csv -NoTypeInformation
$CSVmatch | Select * | Export-Csv C:\CSVmatch.csv -NoTypeInformation

Open in new window

0
 

Author Comment

by:namerg
ID: 38820900
Hmm, before I start debugging, there is a lot of mismatch on Department filed from AD against the CSV file. But the department on AD, is the correct one.

So, if the created AD_CSV has user1 correct and the created File_CSV has also the same user1 but it should have the wrong department, which is what we want...

So, those two resultant CSV files will be easier to compare. record by record.

I will get to to you on Saturday night US Time.

Thanks a lot.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 38820911
Above code will compare only using first name and last name, if it match then save them to CSV..
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 

Author Comment

by:namerg
ID: 38820924
I think it will help, will let you know
0
 
LVL 41

Expert Comment

by:footech
ID: 38821886
Here's another method that does the same thing. Shouldn't give any different results than Subsun's, more to demonstrate a different method.  For me it's a little easier to read.  One thing I did add was a Sort-Object to all the results, since when querying the file and AD they're not likely to be in the same order.
One thing to mention about the Compare-Object used below, the property names have to be the same for both objects, so need to either modify the Import-CSV "-header" parameter as appropriate or make sure the .CSV to import has the correct headers.
Import-Module ActiveDirectory
$file = Import-Csv c:\temp\userlist.csv -header sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
$ad = Get-ADUser -filter * -Properties sAMAccountName,sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber  -SearchBase "OU=ou,DC=domain,DC=com" | Select-Object  sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber,sAMAccountName
Compare-Object $file $ad -property sn,givenname -includeEqual -excludeDifferent -passthru | Select-Object -property * -excludeProperty SideIndicator | Sort-Object -property sn, givenName | Export-CSV -notype c:\temp\CSVusers.csv
Compare-Object $ad $file -property sn,givenname -includeEqual -excludeDifferent -passthru | Select-Object -property * -excludeProperty SideIndicator | Sort-Object -property sn, givenName | Export-CSV -notype c:\temp\ADusers.csv

Open in new window

One further refinement that could be made if desired is to remove the sAMAccountName (I just moved it to the last column of the AD results) from the ADusers.csv if the file you're importing doesn't have that info.
0
 

Author Comment

by:namerg
ID: 38827296
@Subsun's code, works but it is not sorted.
@Footech's code: I get the following error:
Import-Csv : Cannot bind parameter 'Delimiter'. Cannot convert value "c:\scripts\ceridian\upiemps_Test.csv" to type "System.Char". Error: "String must be exactly one character long."
At C:\scripts\ceridian\subsun.ps1:19 char:19
+ $file = Import-Csv <<<<  Import-Csv "c:\scripts\ceridian\upiemps_Test.csv" -header sn,givenName,department,title,telephoneNumber,employeeID,employeeNumber
    + CategoryInfo          : InvalidArgument: (:) [Import-Csv], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.ImportCsvCommand

Compare-Object : Cannot bind argument to parameter 'ReferenceObject' because it is null.
At C:\scripts\ceridian\subsun.ps1:21 char:15
+ Compare-Object <<<<  $file $ad -property sn,givenname -includeEqual -excludeDifferent -passthru | Select-Object -property * -excludeProperty SideIndicator | Sort-Object -property sn, givenName | Export-CSV -notype c:\scripts\ceridian\CSVusers.csv
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand

Compare-Object : Cannot bind argument to parameter 'DifferenceObject' because it is null.
At C:\scripts\ceridian\subsun.ps1:22 char:15
+ Compare-Object <<<<  $ad $file -property sn,givenname -includeEqual -excludeDifferent -passthru | Select-Object -property * -excludeProperty SideIndicator | Sort-Object -property sn, givenName | Export-CSV -notype c:\scripts\ceridian\ADusers.csv
    + CategoryInfo          : InvalidData: (:) [Compare-Object], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CompareObjectCommand

Open in new window

0
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 2000 total points
ID: 38827386
You can add sort-object before exporting $ADmatch $CSVmatch to csv file..

For example..
$ADmatch | Sort-Object -property sn, givenName | Select * | Export-csv.......
0
 

Author Comment

by:namerg
ID: 38827454
Hmm, not quite.
In the ADmatch.csv i get:
sAMAccountName	sn	givenName	department	title	telephoneNumber	employeeID	employeeNumber
aalfonso	Alfonso	Ariel	Health Plan Development	Business Intelligence Manager	303-333-3333		2062

Open in new window

and on CSVmatch.csv
Last Name	First Name	Dept Descrip	SSN	Employment Category	Support	Job Title	Work Phone	Room	Clock Number	DOB
Nguyen	Anh	1.A6800 - Revenue Operations	0000	Full	X	Cash Application Coord	3033333333	A069	1963	3-Dec

Open in new window

Those are the first rows
0
 

Author Comment

by:namerg
ID: 38827566
@Subsun, I think I got it.
0
 
LVL 40

Accepted Solution

by:
Subsun earned 2000 total points
ID: 38827674
In csv you need sort by "first name","last name"
0
 

Author Comment

by:namerg
ID: 38827708
You got it, that was it.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
Wouldn't it be nice if objects in Active Directory automatically moved into the correct Organizational Units? This is what AutoAD aims to do and as a plus, it automatically creates Sites, Subnets, and Organizational Units.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question