How to Read the Content of a CSV File with PowerShell?

I. Introduction

In this chapter, we will see how to use the “Import-CSV” cmdlet to read and utilize the content of a CSV file using PowerShell. In other words, we will import a CSV file to be able to use the data present in this file.

• Click here to watch the video on YouTube

II. What is a CSV File?

Before we start manipulating a CSV file with PowerShell, let's first recall what the CSV file format is. The CSV format, which stands for Comma Separated Values, is a universal file format in text mode, very simple to use and very practical for storing data without applying formatting.

CSV files consist of multiple lines, and each line represents a data record. The first line serves to represent the headers, that is to say the column names, which will correspond to the property names of our future PowerShell object.

Each record contains one or more fields (columns, if you prefer), separated by a character called the delimiter. The official delimiter is the comma, but you can also use another character, such as a semicolon, a space, or a tab.

Today, a CSV file can be read with PowerShell as well as with an application such as Microsoft Excel, which makes it a very practical format. Many business applications allow exporting data in CSV format: the list of personnel, for example.

As a result, when we are required to create PowerShell scripts, it is not uncommon to have to manipulate a CSV file to retrieve the data it contains and use it in a script. For example, we can use a CSV file that contains information about the company's personnel in order to use it as a data source to create Active Directory accounts. This scenario was covered in our course dedicated to Active Directory administration with PowerShell.

III. Using the Import-CSV Command

To use the Import-Csv cmdlet, we will take the following example: a CSV file with two columns, the first column named “SourceFolder” and the second named “DestinationFolder”. We can see this as a correspondence matrix, with data to be transferred from a source folder to a destination folder.

Here's what my CSV file looks like:

SourceFolder;DestinationFolder
C:DataCommercial;D:DataCommercial
C:DataTechnical-Service;D:DataTechnical-Service
C:DataManagement;S:Sensitive-DataManagement

This content is saved in the file “C:TEMPdata.csv”, in “CSV” format, that is, with the extension “.csv”. Now, how are we going to read and use the content of this file?

To import the data from the CSV file, PowerShell includes a ready-to-use cmdlet: “Import-CSV”. In terms of parameters, there are three that are particularly interesting (and especially essential):

• -Path to specify the path to the CSV file
• -Delimiter to specify the column delimiter, which is the semicolon in our case
• -Encoding to specify the file encoding (if necessary, especially for handling accented characters).

Import-CSV -Path “C:TEMPdata.csv” -Delimiter “;”

The content of the CSV file should display in the console. Here's what we get:

• Microsoft Docs - Help - Import-CSV

If you have a CSV file with many columns and you want to import only a few of them, it's possible! To do this, simply use “Select-Object” which we studied in a previous chapter:

Import-CSV -Path “C:TEMPdata.csv” -Delimiter “;” | Select-Object SourceFolder

Contrary to what we might think, the “-Header” parameter of “Import-Csv” does not allow you to select only certain columns. It allows you to define new column names when importing the CSV file. This is particularly useful if there are no headers in the source file.

Here's an example:

Import-CSV -Path “C:TEMPdata.csv” -Delimiter “;” -Header Dossier1

It will only import the first column and it will be named “Dossier1” instead of “DossierSource”.

Note: to determine which delimiter is used by a CSV file, simply open it and look at the syntax of the first line.

IV. Using the CSV File with PowerShell

Displaying the contents of the CSV file in the console is not very useful... To use the contents of the file in a script, we must start by assigning its content to a variable. For example, the variable named “$CSV”:

$CSV = Import-CSV -Path “C:TEMPdata.csv” -Delimiter “;”

Then, if we display the contents of $CSV, we should see the contents of our CSV file.

$CSV

We could filter the contents of the CSV file to import only certain lines. For example, only the lines where the “DossierDestination” field contains the string “D:” corresponding to our D drive. To do this, we will apply a filter with “Where-Object”.

$CSV = Import-CSV -Path “C:TEMPdata.csv” -Delimiter “;” | Where-Object { $_.DossierDestination -like “D:*” }

That was just an example, we will keep our CSV file in its entirety.

To iterate through our CSV file line by line, nothing is better than a “ForEach” loop. Which gives:

Foreach($Ligne in $CSV){
 
}

At each loop iteration, the variable $Ligne will take as its value the complete line of the CSV progressing from the beginning to the end of the CSV file, passing through our variable $CSV.

Then, within the loop, we will be able to read the column we want. To read the value of the “DossierSource” column, we simply specify its name, as it has become a property of our object.

$Ligne.DossierSource

We can try with this loop (no risk because we are only writing a sentence to the console):

Foreach($Ligne in $CSV){
 Write-Host “Data transferred from $($Ligne.DossierSource) to $($Ligne.DossierDestination)”
}

It will return the following result:

# According to the model:
# Data transferred from $($Ligne.DossierSource) to $($Ligne.DossierDestination)
Data transferred from C:DataCommercial to D:DonneesCommercial
Data transferred from C:DataService-Technique to D:DonneesService-Technique
Data transferred from C:DataDirection to S:Donnees-SensiblesDirection

You see, at each loop iteration the values change! We manage to read, line by line, the contents of “$CSV”. This allows us to perform real processing: transferring our data from one folder to another.

To actually transfer the data from the source folder to the destination folder, we will add a line based on the use of the Move-Item command.

Here is the very basic code snippet to iterate through the CSV file and transfer the data:

$CSV = Import-CSV -Path “C:TEMPdata.csv” -Delimiter “;”

Foreach($Ligne in $CSV){
 Write-Host “Data transferred from $($Ligne.DossierSource) to $($Ligne.DossierDestination)”
 Move-Item -Path “$($Ligne.DossierSource)*” -Destination “$($Ligne.DossierDestination)”
}

Of course, this loop should be improved by checking, for example, if the source and destination paths exist (“Test-Path”), before attempting to transfer the data. We could also improve error handling with “Try-Catch”.

In the next chapter, we will see how to export data in CSV format with PowerShell.