Import CSV delimiter PowerShell

Why do we need to use the import CSV (comma-separated values) delimiter in PowerShell? By default, PowerShell uses a comma as a delimiter. Are you using a different delimiter then a comma in the CSV file? If yes, you need to make use of the delimiter parameter in PowerShell. In this article, you will learn how to work with the Import-Csv delimiter in PowerShell.

Information

A CSV file is an excellent combination with PowerShell. The Import-Csv cmdlet creates table-like custom objects from the items in CSV files. Each column in the CSV file becomes a property of the custom object, and the items in rows become the property values. Import-Csv works on any CSV file, including files that are generated by the Export-Csv cmdlet.

We like to run a PowerShell script to Create Active Directory Users from CSV with PowerShell. After running the script, an error is showing up.

PS C:\Scripts> .\Add-NewUsers.ps1
Get-ADUser : Variable: 'Username' found in expression: $Username is not defined.
At C:\scripts\Add-NewUsers.ps1:31 char:6
+     if (Get-ADUser -F {SamAccountName -eq $Username})
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [Get-ADUser], ArgumentException

Why is it happening?

Some European spreadsheet programs use semicolons rather than commas to separate (delimit) data fields in CSV files. It can cause problems with CSV imports.

Separating characters:

• Semicolon: ;
• Comma: ,

Check Import-Csv cmdlet in PowerShell

Download NewUsersFinal.csv and place it in C:\Temp\ folder. Make use of the Import-Csv cmdlet. If you don’t see the columns in PowerShell, it means that it can’t read the CSV file properly.

PS C:\> Import-Csv "C:\Temp\NewUsersFinal.csv" | Format-Table

FirstName;Initials;Lastname;Username;Email;StreetAddress;City;ZipCode;State;Country;Department;Password;Telephone;JobTitle;Company;OU                
-------------------------------------------------------------------------------------------------------------------------------------                
Max;MF;Fraser;Max.Fraser;Max.Fraser@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;Q+/7_]Tc;44123456780;Engineer;EXOIP;OU=IT                
Piers;PB;Bower;Piers.Bower;Piers.Bower@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;RW-cn3N);44123456781;Manager;EXOIP;OU=IT              
Kylie;KD;Davidson;Kylie.Davidson;Kylie.Davidson@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;3VKr2.Wm;44123456782;Engineer;EXOIP;OU=IT    
Richard;RG;Grant;Richard.Grant;Richard.Grant@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;)N3ZYJvS;44123456783;Teamleader;EXOIP;OU=IT     
Boris;BC;Campbell;Boris.Campbell;Boris.Campbell@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;9ZesQ]pq;44123456784;Engineer;EXOIP;OU=IT    
Nicholas;NM;Murray;Nicholas.Murray;Nicholas.Murray@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;KX*rB72p;44123456785;Manager;EXOIP;OU=IT  
Leonard;LC;Clark;Leonard.Clark;Leonard.Clark@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;AJ+(}c3$;44123456786;Engineer;EXOIP;OU=IT       
Ruth;RD;Dickens;Ruth.Dickens;Ruth.Dickens@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;Jgv4{Bb$;44123456787;Engineer;EXOIP;OU=IT          
Jonathan;JF;Fisher;Jonathan.Fisher;Johnathan.Fisher@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;u*PQJAx5;44123456788;Engineer;EXOIP;OU=IT
Grace;GR;Rees;Grace.Rees;Grace.Rees@exoip.com;21 Baker St;London;NW1 6XE;;United Kingdom;IT;w([6p&Kt;44123456789;Engineer;EXOIP;OU=IT

If you don’t see all the information in the output, make use of the Out-GridView cmdlet.

PS C:\> Import-Csv "C:\Temp\NewUsersFinal.csv" | Out-GridView

It will show as below screen.

Add the delimiter parameter -Delimiter “;” to the Import-Csv cmdlet.

PS C:\> Import-Csv "C:\Temp\NewUsersFinal.csv" -Delimiter ";" | Format-Table

FirstName Initials Lastname Username        Email                      StreetAddress City   ZipCode State Country       
--------- -------- -------- --------        -----                      ------------- ----   ------- ----- -------       
Max       MF       Fraser   Max.Fraser      Max.Fraser@exoip.com       21 Baker St   London NW1 6XE       United Kingdom
Piers     PB       Bower    Piers.Bower     Piers.Bower@exoip.com      21 Baker St   London NW1 6XE       United Kingdom
Kylie     KD       Davidson Kylie.Davidson  Kylie.Davidson@exoip.com   21 Baker St   London NW1 6XE       United Kingdom
Richard   RG       Grant    Richard.Grant   Richard.Grant@exoip.com    21 Baker St   London NW1 6XE       United Kingdom
Boris     BC       Campbell Boris.Campbell  Boris.Campbell@exoip.com   21 Baker St   London NW1 6XE       United Kingdom
Nicholas  NM       Murray   Nicholas.Murray Nicholas.Murray@exoip.com  21 Baker St   London NW1 6XE       United Kingdom
Leonard   LC       Clark    Leonard.Clark   Leonard.Clark@exoip.com    21 Baker St   London NW1 6XE       United Kingdom
Ruth      RD       Dickens  Ruth.Dickens    Ruth.Dickens@exoip.com     21 Baker St   London NW1 6XE       United Kingdom
Jonathan  JF       Fisher   Jonathan.Fisher Johnathan.Fisher@exoip.com 21 Baker St   London NW1 6XE       United Kingdom
Grace     GR       Rees     Grace.Rees      Grace.Rees@exoip.com       21 Baker St   London NW1 6XE       United Kingdom

Again, this time with the Out-GridView cmdlet.

Add delimiter parameter to Import-Csv cmdlet

As seen in the previous step, the CSV file got the character semicolon. We have to add the delimiter parameter -Delimiter “;” to the Import-Csv cmdlet.

Before

$ADUsers = Import-csv "C:\scripts\NewUsersFinal.csv"

After

$ADUsers = Import-csv "C:\scripts\NewUsersFinal.csv" -Delimiter ";"

Run the script, it will work as expected. Did this help you?

Keep reading: Export a list of mailboxes to CSV in Exchange »

Conclusion

In this article, you learned how to import CSV delimiter with PowerShell. Check your CSV file and adjust your Import-Csv cmdlet with the delimiter parameter. It’s always good to import the CSV in PowerShell before running the script. This way, you know if the CSV file is readable!

Did you enjoy this article? If so, you may like the article New-MailboxExportRequest is not recognized in Exchange Server. Don’t forget to follow us and share this article.