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?
 
Mauro CazabonnetSenior 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
 
Mauro CazabonnetSenior 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
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.

 
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 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 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 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.