How to Read CSV File Line by Line in PowerShell?

Recently, one of my clients asked me to read CSV file data and store it in a SharePoint list using PowerShell. So, here, I will explain to you how to read CSV file line by line in PowerShell with a few examples.

To read a CSV file line by line in PowerShell, you can use the Import-Csv cmdlet combined with a foreach loop to process each row as an object. For larger files, consider using the .NET StreamReader class to read the file more efficiently, handling each line as a string and processing it accordingly. These methods allow you to handle data row by row, managing memory usage effectively, especially with large datasets.

For this particular tutorial, I have taken a CSV file having a few columns like:

Index
Customer ID
First Name
Last Name
Company, etc.

Here is what the CSV file looks like and if you want to use this sample CSV file, then you can download it.

Read CSV File Line by Line in PowerShell

Table of Contents

Read CSV File using Import-Csv Cmdlet in PowerShell

In PowerShell, to read CSV files you can use the Import-Csv cmdlet. This cmdlet reads the CSV file and converts it into a table-like custom object where each column becomes a property of the object. Here’s a basic example of how to use Import-Csv:

$csvData = Import-Csv -Path "C:\MyFolder\Customers.csv"
foreach ($line in $csvData) {
    # Process each line here
    Write-Host $line.'Customer Id', $line.'First Name'

In this example, Customer Id and First Name are the headers of the columns in the CSV file. The foreach loop iterates through each line of the CSV as an object, and you can access each column’s value using the $line.ColumnName syntax.

You can see the output in the screenshot below:

How to Read CSV File Line by Line in PowerShell

Read Create CSV Files with Headers in PowerShell

Read CSV Line by Line in PowerShell

While Import-Csv reads the entire CSV into memory in PowerShell, which is fine for small files, you might want to process each line individually, especially when dealing with large files. To do this, you can combine Import-Csv with the ForEach-Object cmdlet like below:

Import-Csv -Path "C:\MyFolder\Customers.csv" | ForEach-Object {
    # Process each line here
    Write-Host $_.'Customer Id' $_.'First Name'
}

Here, $_ represents the current object in the pipeline, allowing you to access each line’s column data as it’s processed.

Here, you can see the output after I executed the PowerShell script using VS code.

Read Get the First and Last Line of a CSV File in PowerShell

Using StreamReader for Large Files

For very large CSV files, Import-Csv might not be efficient as it loads the entire file into memory. In such cases, you can use the .NET StreamReader class to read the file line by line in PowerShell. Here is the complete script.

$streamReader = [System.IO.StreamReader] "C:\MyFolder\Customers.csv"
while ($line = $streamReader.ReadLine()) {
    # Skip the header line
    if ($streamReader.BaseStream.Position -eq 0) {
        continue
    }

    # Process the line here, splitting by comma for CSV fields
    $fields = $line.Split(',')
    Write-Host $fields[0] $fields[1]
}
$streamReader.Close()

This script opens the CSV file for reading and processes each line, excluding the header. It splits each line by commas to access individual fields.

Read Convert CSV to HTML Table in PowerShell

Custom Delimiters and the Import-Csv Cmdlet

Sometimes, CSV files may use delimiters other than commas. PowerShell’s Import-Csv cmdlet can handle custom delimiters using the -Delimiter parameter:

$csvData = Import-Csv -Path "C:\MyFolder\Customers.csv" -Delimiter ";"
foreach ($line in $csvData) {
    # Process each line here
    Write-Host $line.ColumnName1 $line.ColumnName2
}

Replace ";" with the appropriate delimiter character used in your CSV file.

Handling Special Characters and Enclosures

CSV files can contain special characters or enclosures, such as quotation marks to handle commas within a field. PowerShell automatically manages these when using Import-Csv:

$csvData = Import-Csv -Path "C:\MyFolder\Customers.csv"
foreach ($line in $csvData) {
    # PowerShell handles fields with commas enclosed in quotes
    Write-Host $line.ColumnName1 $line.ColumnName2
}

Conclusion

In this PowerShell tutorial, I have explained how to read csv file line by line in PowerShell. The best way to read CSV files in PowerShell is to use the Import-Csv Cmdlet.