CSV import returns null string

Question

I've currently got the code down below, however I get $null returned on the $userpass variable. I've checked the CSV file and all should be correct.

Import-Module activedirectory

#Dit deel van het script is voor het verzamelen van informatie die in de commands gestopt wordt.

$csvpad = Read-Host -Prompt 'Geef het pad naar het CSV bestand op (zonder bestandsnaam)
ZONDER backslash, bijvoorbeeld: D:\Gebruikers mappen'
$csvbestand = Read-Host -Prompt 'Geef het CSV bestandsnaam op'

#Voor het opslaan van de data uit het opgegeven .CSV bestand in de $users variable

Write-Host "CSV bestand importeren!"

$users = Import-Csv -Path "$csvpad\$csvbestand"

#Loop om door elke rij in het CSV bestand te gaan.

Foreach ($user in $users)
{
  #Leest de data van elk veld in elke rij en geeft de juiste data aan de juiste variable.
  $displayname = $user.Voornaam + " " + $user.Achternaam
  $userfirstname = $user.Voornaam
  $userlastname = $user.Achternaam
  $username = $user.Gebruikersnaam
  $usermail = $user.Voornaam + "." + $user.Achternaam + "@" + $user.Domein
  $ou = $user.OU
  $userpass = $user.Wachtwoord

    #Als de user niet in AD bestaat gaat deze door met het maken van een nieuwe account.
    New-ADUser -Name "$displayname" -DisplayName "$displayname" -GivenName "$userfirstname" -Surname "$userlastname" -Enabled $true -SamAccountName "$username" -EmailAddress "$usermail" -Path "$ou" -AccountPassword (ConvertTo-SecureString $userpass -AsPlainText -Force) -ChangePasswordAtLogon $true -PasswordNeverExpires $true
  }

Write-Host "Users uit het CSV bestand zijn aangemaakt!"

Here is the CSV I am using (data is as random as it is just a learning project of school).

Voornaam;Achternaam;Domein;Gebruikersnaam;Wachtwoord;OU;Berekening usernames
Test;Account1;01testict.nl;Te.Account1;P@ssw0rd;OU=Users,DC=01testict,DC=nl;Te
Testing;Account2;01testict.nl;Te.Account2;P@ssw0rd;OU=Users,DC=01testict,DC=nl;Te
Test123;Account3;01testict.nl;Te.Account3;P@ssw0rd;OU=Users,DC=01testict,DC=nl;Te
Test971;Account4;01testict.nl;Te.Account4;P@ssw0rd;OU=Users,DC=01testict,DC=nl;Te
Test652;Account5;01testict.nl;Te.Account5;P@ssw0rd;OU=Users,DC=01testict,DC=nl;Te

Any ideas?

Give an example of what your CSV file looks like. Use random (not actual) data — Hassan Voyeau
– Hassan Voyeau, Commented Oct 9, 2018 at 18:50
Can you show a sample of your CSV? Also, when you call ConvertTo-SecureString at the end of the loop you are missing a space between $userpass and the -AsPlainText parameter. — Lance U. Matthews
– Lance U. Matthews, Commented Oct 9, 2018 at 18:51
Does your csv have a column named Wachtwoord? Are there any spaces in that column name? — BenH
– BenH, Commented Oct 9, 2018 at 18:52
Hi there, thank you for the responses, I've uploaded the CSV I am using to test the script. Data is all the same as I am using with the script, as it's just a school assignment to learn and I can't seem to figure it out. — RSDavyy
– RSDavyy, Commented Oct 10, 2018 at 7:50
@BACON Thank you, a few minutes after posting I did indeed see that, I've changed it but unfortunately that doesn't resolve my issue, the CSV I use can be found at the main post or at this link: i.sstatic.net/Jrfau.png — RSDavyy
– RSDavyy, Commented Oct 10, 2018 at 7:52

Lance U. Matthews · Accepted Answer · 2018-10-10 18:26:17Z

Firstly, certainly your screenshot but also your snippet text and non-snippet text appear to be copied from a GUI CSV reader because all we get is the data. The data (the "V" in "CSV") is not so important here as the structure, the encapsulation of the data (the "CS"). That is, the text you would see if you opened your CSV file in Notepad. Your OneDrive link turned out to be what I was looking for, although even that for some reason appeared to be a screenshot of the raw CSV until I downloaded the file.

Also, just a tip for future StackOverflow usage, if you have text to share it's always better to do so as text and not an image as that makes it accessible to copy-and-paste, search engines, and screen readers.

Anyways, on to solving your problem. $userpass should not be the only variable that's unexpectedly empty because none of the properties of $user have a value, either. You can see why if we take a look at what Import-Csv is producing...

PS> Import-Csv -Path "$Env:Temp\userlist.csv"

Voornaam;Achternaam;Domein;Gebruikersnaam;Wachtwoord;OU;Berekening usernames
----------------------------------------------------------------------------
Test;Account1;01testict.nl;Te.Account1;P@ssw0rd;OU=Users
Testing;Account2;01testict.nl;Te.Account2;P@ssw0rd;OU=Users
Test123;Account3;01testict.nl;Te.Account3;P@ssw0rd;OU=Users
Test971;Account4;01testict.nl;Te.Account4;P@ssw0rd;OU=Users
Test652;Account5;01testict.nl;Te.Account5;P@ssw0rd;OU=Users

That is outputting objects with a single property named Voornaam;Achternaam;Domein;Gebruikersnaam;Wachtwoord;OU;Berekening usernames and all the values for that record stuffed together. The reason for this is because Import-Csv expects the delimiter to be a comma by default, whereas your file uses a semicolon. The fix is to use the -Delimiter parameter to specify semicolon as the delimiter to be used by Import-Csv, which you can see now outputs complete objects with the correct properties...

PS> Import-Csv -Path "$Env:Temp\userlist.csv" -Delimiter ';'


Voornaam             : Test
Achternaam           : Account1
Domein               : 01testict.nl
Gebruikersnaam       : Te.Account1
Wachtwoord           : P@ssw0rd
OU                   : OU=Users,DC=01testict,DC=nl
Berekening usernames : Te

Voornaam             : Testing
Achternaam           : Account2
Domein               : 01testict.nl
Gebruikersnaam       : Te.Account2
Wachtwoord           : P@ssw0rd
OU                   : OU=Users,DC=01testict,DC=nl
Berekening usernames : Te

Voornaam             : Test123
Achternaam           : Account3
Domein               : 01testict.nl
Gebruikersnaam       : Te.Account3
Wachtwoord           : P@ssw0rd
OU                   : OU=Users,DC=01testict,DC=nl
Berekening usernames : Te

Voornaam             : Test971
Achternaam           : Account4
Domein               : 01testict.nl
Gebruikersnaam       : Te.Account4
Wachtwoord           : P@ssw0rd
OU                   : OU=Users,DC=01testict,DC=nl
Berekening usernames : Te

Voornaam             : Test652
Achternaam           : Account5
Domein               : 01testict.nl
Gebruikersnaam       : Te.Account5
Wachtwoord           : P@ssw0rd
OU                   : OU=Users,DC=01testict,DC=nl
Berekening usernames : Te

...and so, with that change, your $user iterator variable in the foreach block would have the expected properties, too.

thankyou @bacon that did the trick! Handi trick to know about the Delimiter. This will certainly help in the future!

Collectives™ on Stack Overflow

CSV import returns null string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related