Read a CSV file with PowerShell using the Import-CSV function

“Stay humble

Work hard

Be kind.”

🙂

Hello Everyone,

Hope you all are doing well.

In this article, we are going to look at how to read a CSV file with PowerShell, using the Import-CSV function, and how to read each line with the ForEach cmdlet.

We have good list of articles on PowerShell, please have a look.

https://knowledge-junction.in/category/technology-articles/powershell-cmdlets/

So without getting late, let’s get started.

Background

• This is very repetitively requirement – use of CSV files in PowerShell script to perform various operation / bulk operations
• When working with PowerShell we can use CSV files to import data into systems or to use it as a reference list, of users, for example, to update or get settings.
• To do this we use the Import-CSV CMDLET in PowerShell.
• The Import-CSV CMDLET converts the CSV data into a custom object in PowerShell.
• This way we can easily walk through each row of the CSV file and use the data in our scripts.
• Here are some tips on how to use the Import CSV function.

Details

• Open PowerShell ISE.
• Now ran the PowerShell with the following cmdlet.

Prerequisites

• Install PowerShell ISE / PowerShell / Visual Studio Code if its not installed.

Import-CSV

The Import-CSV cmdlet is pretty straightforward and only has a few properties that are useful:

• Path – (Required) Location of the CSV file
• Delimiter – Default comma, but this allows us to change it
• Header – Allows us to define custom headers for the columns. Used as property names
• UseCulture – Use the default delimiter of our system
• Encoding – Specify the encoding of the imported CSV file

We are going to start with a simple list of users that we can import in PowerShell. We have created the following CSV file that we will use in the examples below.

This CSV file already had headers for each column, so we don’t need to worry about that for now. To import this CSV file into PowerShell we can simply use the following command.

Import-CSV -Path E:\PowerShellScripts/osinfotech.csv | ft

In this case, we don’t store the results into a variable, but immediately output it into a table with ft. As we can see, now we have all our users with all columns nicely in PowerShell.

If we store the results of the import cmdlet into a variable, then we can reference each column as a property using the column name (header):

$users = Import-CSV -Path E:\PowerShellScripts/osinfotech.csv
$users.DisplayName

Specifying the Delimiter

A common issue when it comes to import a CSV file with PowerShell is that the delimiter is not recognized. By default, the Import-CSV cmdlet uses the , as the delimited.

But in some countries there is different delimiters in CSV file. For example – in the Netherlands the default delimiter in Excel for CSV files is ";“.

So when I create in CSV file in Excel that I want to use in PowerShell, I will need to either change the delimiter in the CSV file or specify the correct delimiter in PowerShell. There are two options for this.

We can use the -delimiter parameter to specify the correct delimiter:

$users = Import-Csv -Path  E:\PowerShellScripts/osinfotech.csv  -Delimiter ,

Or use the system default delimiter with the -UseCulture parameter:

$users = Import-Csv -Path  E:\PowerShellScripts/osinfotech.csv  -UseCulture

If we want to know what the default delimiter is on our computer, then we can use the following PowerShell command to get the culture information:

(Get-Culture).TextInfo.ListSeparator

PowerShell Import CSV ForEach

Most of the time when we import a CSV file in PowerShell we want to walk through each line of the CSV file. For example to get the mailbox permissions or OneDrive folder size.

To do this we can combine the Import CSV cmdlet with ForEach. Inside the ForEach block, we can reference each column of the CSV file as a property of the user object in this case.

Taken the CSV file example that we have added at the beginning of the article, we can get the mailbox of each user using the Email like this:

Import-CSV -Path E:\PowerShellScripts/osinfotech.csv | ForEach {
# Get mailbox of each user
Get-ExoMailbox -identity $_.Email
}

For this to work our CSV file will need to have headers. That is not always the case, sometimes CSV files only contain values and no headers at all.

To still be able to reference each value in the CSV file we will need to add headers to the Import-CSV file cmdlet.

Adding Headers to Import-CSV

Let’s take the following CSV file.

As we can see, this CSV file doesn’t have headers (column names). So when we import this CSV file directly in PowerShell we will see that the first row is used as the header. This is not going to work.

So what we need to do is define the headers for the CSV file. We will need to list all headers in a comma-separated string, enclosing each header name with single quotes ' .

Import-Csv -Path E:\PowerShellScripts/osinfotech.csv -Header 'DisplayName','EmployeeId','Join Date','JobTitle','Email','Company' | ft

Important to know is that if we don’t specify all headers, the remaining columns are not imported. So if we expand our CSV file with the following columns.

And only specify some column headers, then the other columns in this case, won’t be imported:

Import-Csv -Path E:\PowerShellScripts/osinfotech.csv -Header 'DisplayName','EmployeeId','Join Date' | ft

If we define more headers than that there are columns, then an additional empty property is created for each object. This can be useful if we want to create an object with additional fields that we want to fill later on. For example, if we want to look up the address of each employee, we can already add the address column to the imported data:

Import-Csv -Path E:\PowerShellScripts/osinfotech.csv -Header 'DisplayName','EmployeeId','Join Date','JobTitle','Email','Company','Address'| ft

How to Import only specific fields with Import-CSV

When we import a CSV file into PowerShell it will import all the columns by default. But when working with large files it can be useful to only select the data that we really need.

There are two ways to do this, if our CSV file has headers, then we can select only the column that we need:

Import-CSV -Path E:\PowerShellScripts/osinfotech.csv |Select 'DisplayName' , 'Email' , 'EmployeeId'| ft

Another option is to use the -header parameter, but this only works well when we only need the first column (or the first couple of columns). Because we can’t skip a column with this method:

Import-Csv -Path E:\PowerShellScripts/osinfotech.csv -Header id

Wrapping Up

The Import-CSV cmdlet in PowerShell is really useful when we need to work with lists or want to look up multiple entities. Make sure that we check if the CSV file comes with headings and that we use the correct delimiter.

Hope this article will help us how to read a CSV file with PowerShell, using the Import-CSV function, and how to read each line with the ForEach cmdlet.

Also get my article updates on my social media handles.

LinkedIn – https://www.linkedin.com/in/khasim-shaik-8784a1232/

Twitter – https://twitter.com/KhasimShaik2009

Facebook – https://www.facebook.com/profile.php?id=100078255554660

Thank you for your support, will catch up with new article soon.

Keep learning and keep smiling.

Thanks.