Solved

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

Posted on 2013-01-25
11
730 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
  • 6
  • 4
11 Comments
 
LVL 40

Assisted Solution

by:Subsun
Subsun earned 500 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
 

Author Comment

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

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 500 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 500 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

Join & Write a Comment

Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now