Solved

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

Posted on 2013-01-25
11
761 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 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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

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

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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
After seeing many questions for JRNL_WRAP_ERROR for replication failure, I thought it would be useful to write this article.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

726 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