Import data from multiple CSV files using Powershell

Hello.

I have a PowerShell script that imports user information from a CVS file to Active Directory. I need help creating a script that can read from different columns from multiple CSV files and then import the data collected to Active Directory. I am thinking that I can use the Import-CSV -header cmdlet (or Merge-CSV Files or get-chilItem?), store the data in memory and have my import script read from it. Or combine all the data collected into one CSV file and have my import script read it. What would be a clean and efficient way of doing this? And does anybody have a sample script that I could use?

My import script is shown below:

Import-Module activedirectory
$Userlist = Import-Csv "C:\Test2.csv"

ForEach ($Person in $Userlist) {
$Person.Name
$Person.gn
$Person.sn
$Person.displayName
$Person.userName

New-ADUser -Name $Person.Name -GivenName $Person.gn -Surname $Person.sn -DisplayName 

$Person.displayName -SamAccountName $Person.username -Path "OU=Domain 

Users,DC=source,DC=local" -UserPrincipalName ("{0}@{1}" -f $Person.userName,"source.local") 

-PasswordNotRequired $true -Enabled $true -ChangePasswordAtLogon $false
}

Exit

Open in new window


From the import script above, for example, the Name column is located in CSV1. The gn and sn columns are located in CSV2. While the displayName and userName columns are located in CSV3.

Thank you.
LVL 2
petaganayrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mauro CazabonnetSenior .NET Software EngineerCommented:
can you post a sample csv1, csv2 and csv3?
0
David Johnson, CD, MVPOwnerCommented:
#Name column is located in CSV1
#The gn and sn columns are located in CSV2. 
#While the displayName and userName columns are located in CSV3.
#Import-Module activedirectory
$csv1 = "C:\Test1.csv"
$csv2 = "c:\test2.csv"
$csv3 = "c:\test3.csv"
if ((test-path -Path $csv1) -eq $false) 
  {
  write-host ("Can't Find:",$csv1)
  break
  }
$name = Import-Csv $csv1
if ((test-path -Path $csv2) -eq $false) { 
  write-host ("Can't Find:",$csv2)
  break
  }
$sn = import-csv "c:\test2.csv"
if ((test-path -Path $csv3) -eq $false) {  
    write-host ("Can't Find:",$csv3)
    break
    }
$dn = import-csv $csv3
#
# ensure that there are the same # of records
#
if ( ($name.count -ne $sn.count) -or ($name.count -ne $dn.count))
    {
    write-output ("Not all input files have the same # of records")
    }
else
    {
    for($counter=0; $counter -le $r1;$counter++){
        $Name[$counter].Name
        $sn[$counter].gn
        $sn[$counter].sn
        $dn[$counter].displayName
        $dn[$counter].userName

        New-ADUser -Name $Name[$counter].Name -GivenName $sn[$counter].gn -Surname $sn[$counter].sn `
         -DisplayName $dn[$counter].displayName -SamAccountName $dn[$counter].username `
         -Path "OU=Domain Users,DC=source,DC=local" -UserPrincipalName ("{0}@{1}" -f $dn[$counter].username,"source.local") `
         -PasswordNotRequired $true -Enabled $true -ChangePasswordAtLogon $false
    }
}

Open in new window

0
Mauro CazabonnetSenior .NET Software EngineerCommented:
The sample code assumes the source files are not sorted and have an associated id for the Name
Sample csv's
test1.csv                  test2.csv                        test3.csv
id,Name                  id,gn,sn                        id,displayName,userName
8,John Doe            6,gn_something,sn_something      5,John T. Yaya,jd00123
7,Mary Doe            7,gn_something,sn_something      6,John P. Colt,jd00456
1,John Smith            8,gn_something,sn_something      7,Mary F. Doe,jd00789
2,Joe Black            1,gn_something,sn_something      8,John S. Doe,jd03456
3,John Bigbooty            2,gn_something,sn_something      1,John Q. Smith,jd12345
4,John Smallberries            3,gn_something,sn_something      2,Joe L. Black,jd54321
5,John Yaya            4,gn_something,sn_something      3,John O. Bigbooty,jd67891
6,John Colt            5,gn_something,sn_something      4,John H. Smallberries,jd99911

#Name column is located in CSV1
#The gn and sn columns are located in CSV2. 
#While the displayName and userName columns are located in CSV3.
#Import-Module activedirectory
$csv1 = "c:\test\test1.csv"
$csv2 = "c:\test\test2.csv"
$csv3 = "c:\test\test3.csv"

if ((test-path -Path $csv1) -eq $false) 
  {
  write-host ("Can't Find:",$csv1)
  break
  }
$name = Import-Csv $csv1
#Import names into hashtable
$nameHash = @{}
foreach ($item in $name){
    if ($item -eq $null) {break}
    if (!($nameHash.ContainsKey($item.id))) {$nameHash.add($item.id,$item.name)}
}    

if ((test-path -Path $csv2) -eq $false) { 
  write-host ("Can't Find:",$csv2)
  break
  }
$sn = import-csv $csv2
#Import sn's into hashtable
$snHash = @{}
foreach ($item in $sn){
    if ($item -eq $null) {break}
    if (!($snHash.ContainsKey($item.id))) {$snHash.add($item.id,$item.gn + "," + $item.sn)}
}    

if ((test-path -Path $csv3) -eq $false) {  
    write-host ("Can't Find:",$csv3)
    break
    }
$dn = import-csv $csv3
#Import $dn into hashtable
$dnHash = @{}
foreach ($item in $dn){
    if ($item -eq $null) {break}
    if (!($dnHash.ContainsKey($item.id))) {$dnHash.add($item.id,$item.displayName + "," + $item.userName)}
}    
$counter = 0
foreach($item in $nameHash.GetEnumerator()){
    $counter++
    $id = $item.Name
    $Name = $item.Value
    $snItems = [string]$snHash.$id
    $dnItems = [string]$dnHash.$id
    ($gn,$sn) = $snItems.split(",",2)
    ($displayName,$userName) = $dnItems.split(',',2)
    if ($counter-le ($nameHash.count - 1)){
        $Name + " : " + $gn + " : " + $sn + " : " + $displayName + " : " + $userName
    }
    New-ADUser -Name $Name -GivenName $gn -Surname $sn `
    -DisplayName $displayName -SamAccountName $userName `
    -Path "OU=Domain Users,DC=source,DC=local" -UserPrincipalName ("{0}@{1}" -f $userName,"source.local") `
    -PasswordNotRequired $true -Enabled $true -ChangePasswordAtLogon $false
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

petaganayrAuthor Commented:
I see what you guys did. Thank you. Please give me some time to go over the scripts and do a few test runs. Thanks again.
0
petaganayrAuthor Commented:
David,

I don't know what this piece does, besides checking if the records are the same. I am not sure what count and counter do:
if ( ($name.count -ne $sn.count) -or ($name.count -ne $dn.count))
    {
    write-output ("Not all input files have the same # of records")
    }
else
    {
    for($counter=0; $counter -le $r1;$counter++){
        $Name[$counter].Name...

Open in new window


mcazabonnet,

Same question, I don't know what this piece does:
$counter = 0
foreach($item in $nameHash.GetEnumerator()){
    $counter++
    $id = $item.Name~~
if ($counter-le ($nameHash.count - 1))

Open in new window

0
Mauro CazabonnetSenior .NET Software EngineerCommented:
The counter piece can be omitted, not needed

#Name column is located in CSV1
#The gn and sn columns are located in CSV2. 
#While the displayName and userName columns are located in CSV3.
#Import-Module activedirectory
$csv1 = "c:\test\test1.csv"
$csv2 = "c:\test\test2.csv"
$csv3 = "c:\test\test3.csv"

if ((test-path -Path $csv1) -eq $false) 
  {
  write-host ("Can't Find:",$csv1)
  break
  }
$name = Import-Csv $csv1
#Import names into hashtable
$nameHash = @{}
foreach ($item in $name){
    if ($item -eq $null) {break}
    if (!($nameHash.ContainsKey($item.id))) {$nameHash.add($item.id,$item.name)}
}    

if ((test-path -Path $csv2) -eq $false) { 
  write-host ("Can't Find:",$csv2)
  break
  }
$sn = import-csv $csv2
#Import sn's into hashtable
$snHash = @{}
foreach ($item in $sn){
    if ($item -eq $null) {break}
    if (!($snHash.ContainsKey($item.id))) {$snHash.add($item.id,$item.gn + "," + $item.sn)}
}    

if ((test-path -Path $csv3) -eq $false) {  
    write-host ("Can't Find:",$csv3)
    break
    }
$dn = import-csv $csv3
#Import $dn into hashtable
$dnHash = @{}
foreach ($item in $dn){
    if ($item -eq $null) {break}
    if (!($dnHash.ContainsKey($item.id))) {$dnHash.add($item.id,$item.displayName + "," + $item.userName)}
}    

foreach($item in $nameHash.GetEnumerator()){
    $id = $item.Name
    $Name = $item.Value
    $snItems = [string]$snHash.$id
    $dnItems = [string]$dnHash.$id
    ($gn,$sn) = $snItems.split(",",2)
    ($displayName,$userName) = $dnItems.split(',',2)
	$Name + " : " + $gn + " : " + $sn + " : " + $displayName + " : " + $userName
    New-ADUser -Name $Name -GivenName $gn -Surname $sn `
    -DisplayName $displayName -SamAccountName $userName `
    -Path "OU=Domain Users,DC=source,DC=local" -UserPrincipalName ("{0}@{1}" -f $userName,"source.local") `
    -PasswordNotRequired $true -Enabled $true -ChangePasswordAtLogon $false
}

Open in new window

0
David Johnson, CD, MVPOwnerCommented:
when you import the csv into a variable it goes into an array
$array[0] is the first line
$array[1] is the second line and so forth.

$counter is the numeric part of the array .. the for / next loop sets the counter at 0
so on the first pass it will use the first element of the array (the first line of the csv you imported) the counter is then incremented and now counter is 1 so now it uses $array[1] $array2[1] and so forth.
0
petaganayrAuthor Commented:
I tried the scripts an I have a few more questions.

David,

What is "$r1" for? I tried giving it value of "$r1 = @()" because the script doesn't seem like its importing the data to Active Directory. When I tried to removed it, the script said that the "$counter -le" needs a  value.

mcazabonnet,
What if csv1 has one more column in addition to the Name column, for example, password column. How would the foreach statement work?

Also, if there are three columns in the csv files, would the ".split" need "3" ? Is that how that line works?

Thank you.
0
Mauro CazabonnetSenior .NET Software EngineerCommented:
For the nameHash table we would need to append a comma delimited string as detailed in line 19
and line 49 we would have to split out the Name and the second column, same would apply to snItems and dnItems if extra columns are added. The imports for Name, sn and dn can be written to be more generic and detect the incoming number of columns. This would take a bit more coding...

#Name column is located in CSV1
#The gn and sn columns are located in CSV2. 
#While the displayName and userName columns are located in CSV3.
#Import-Module activedirectory
$csv1 = "c:\test\test1.csv"
$csv2 = "c:\test\test2.csv"
$csv3 = "c:\test\test3.csv"

if ((test-path -Path $csv1) -eq $false) 
  {
  write-host ("Can't Find:",$csv1)
  break
  }
$name = Import-Csv $csv1
#Import names into hashtable
$nameHash = @{}
foreach ($item in $name){
    if ($item -eq $null) {break}
    if (!($nameHash.ContainsKey($item.id))) {$nameHash.add($item.id,$item.name + "," + $item.secondcolumn)}
}    

if ((test-path -Path $csv2) -eq $false) { 
  write-host ("Can't Find:",$csv2)
  break
  }
$sn = import-csv $csv2
#Import sn's into hashtable
$snHash = @{}
foreach ($item in $sn){
    if ($item -eq $null) {break}
    if (!($snHash.ContainsKey($item.id))) {$snHash.add($item.id,$item.gn + "," + $item.sn)}
}    

if ((test-path -Path $csv3) -eq $false) {  
    write-host ("Can't Find:",$csv3)
    break
    }
$dn = import-csv $csv3
#Import $dn into hashtable
$dnHash = @{}
foreach ($item in $dn){
    if ($item -eq $null) {break}
    if (!($dnHash.ContainsKey($item.id))) {$dnHash.add($item.id,$item.displayName + "," + $item.userName)}
}    

foreach($item in $nameHash.GetEnumerator()){
    $id = $item.Name
    $nameItems = $item.Value
	($Name,$secondcolumn) = $nameItems.split(",",2)
    $snItems = [string]$snHash.$id
    $dnItems = [string]$dnHash.$id
    ($gn,$sn) = $snItems.split(",",2)
    ($displayName,$userName) = $dnItems.split(',',2)
	$Name + " : " + $gn + " : " + $sn + " : " + $displayName + " : " + $userName
    New-ADUser -Name $Name -GivenName $gn -Surname $sn `
    -DisplayName $displayName -SamAccountName $userName `
    -Path "OU=Domain Users,DC=source,DC=local" -UserPrincipalName ("{0}@{1}" -f $userName,"source.local") `
    -PasswordNotRequired $true -Enabled $true -ChangePasswordAtLogon $false
}

Open in new window

0
Mauro CazabonnetSenior .NET Software EngineerCommented:
If there were 3 items in the incoming csv the split value would be set to 3, technically you can omit the 2

So it can be coded as such ($Name,$secondcolumn) = $nameItems.split(",")
0
David Johnson, CD, MVPOwnerCommented:
don't know where the $r1 came from! replace
  for($counter=0; $counter -le $r1;$counter++){
with
  for($counter=0; $counter -le $csv1.count;$counter++){

or before the loop add
$r1 = $csv1.count
0
David Johnson, CD, MVPOwnerCommented:
What if csv1 has one more column in addition to the Name column, for example, password column. How would the foreach statement work?
debug code can be omitted
  $Name[$counter].Name
        $sn[$counter].gn
        $sn[$counter].sn
        $dn[$counter].displayName
        $dn[$counter].userName

Open in new window


  New-ADUser -Name $Name[$counter].Name -GivenName $sn[$counter].gn -Surname $sn[$counter].sn `
         -DisplayName $dn[$counter].displayName -SamAccountName $dn[$counter].username `
         -Path "OU=Domain Users,DC=source,DC=local" -UserPrincipalName ("{0}@{1}" -f $dn[$counter].username,"source.local") `
         -PasswordNotRequired $true -Enabled $true -ChangePasswordAtLogon $false `
-AccountPassword (ConvertTo-SecureString $Name.Password -AsPlainText -force)

Open in new window

0
petaganayrAuthor Commented:
Both scripts are great. And thank you for answering all of my questions. I picked mcazabonnet's script for Best Solution because I can only pick one and it has the sort by ID.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.