Solved

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

Posted on 2013-01-25
11
747 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Disable network adapter without DHCP IP address 1 23
Powershell help in Exchange 2013 4 39
Need to export the list of AD users with below attrubutes 2 21
Script Error 3 18
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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 to another domain controller. Log onto the new domain controller with a user account t…
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 …

730 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