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.

Microsoft 365: CSV file with headers
fig: Microsoft 365: CSV file with headers

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.

Microsoft 365: Import-CSV  - Output1
fig: Microsoft 365: Import-CSV – Output1

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

Microsoft 365: Import-CSV  - Output2
fig: Microsoft 365: Import-CSV – Output2

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

Microsoft 365: Import-CSV  - Output3
fig: Microsoft 365: Import-CSV – Output3

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.

Microsoft 365: CSV file without headers
fig: Microsoft 365: CSV file without headers

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.

Microsoft 365: Import-CSV  - Output4
fig: Microsoft 365: Import-CSV – Output4

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

Microsoft 365: Import-CSV  - Output5
fig: Microsoft 365: Import-CSV – Output5

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.

Microsoft 365: CSV file expand
fig: Microsoft 365: CSV file expand

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

Microsoft 365: Import-CSV  - Output6
fig: Microsoft 365: Import-CSV – Output6

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

Microsoft 365: Import-CSV  - Output7
fig: Microsoft 365: Import-CSV – Output7

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

Microsoft 365: Import-CSV  - Output8
fig: Microsoft 365: Import-CSV – Output8

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

Microsoft 365: Import-CSV  - Output9
fig: Microsoft 365: Import-CSV – Output9

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.

Khasim Shaik

SharePoint & Power Platform Developer at OS InfoTech

You may also like...

2 Responses

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: