How to Use PowerShell Import-Csv Cmdlet?

by

Recently, I needed to process a large CSV file containing customer data for a marketing campaign targeting different regions in the USA. In this tutorial, I will explain how to use the PowerShell Import-Csv cmdlet to read and manipulate CSV data efficiently. This tutorial will help you understand how to handle similar tasks using the Import-CSV PowerShell cmdlet.

PowerShell Import-CSV cmdlet

The Import-Csv cmdlet in PowerShell is a powerful cmdlet that allows you to read data from a CSV file and convert it into PowerShell objects. This makes it easy to manipulate and analyze the data.

Now, let me show you some usage of the import-csv PowerShell cmdlet.

Basic Usage

To start, let’s look at the basic usage of the Import-Csv cmdlet. Suppose you have a CSV file named customers.csv with the following content:

FirstName,LastName,Email,City,State
John,Doe,john.doe@example.com,New York,NY
Jane,Smith,jane.smith@example.com,Los Angeles,CA
Robert,Brown,robert.brown@example.com,Chicago,IL

I am using this CSV file for all the examples, and you can download it to do all these demos.

You can import this CSV file into PowerShell using the following command:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv"
$customers

This command reads the CSV file and stores each row as an object in the $customers variable.

I executed the above PowerShell script, and you can see the exact output in the screenshot below:

PowerShell import-csv

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

Filter Data

Once you have imported the data, you can easily filter it. For example, to get all customers from New York, you can use:

$nyCustomers = $customers | Where-Object { $_.State -eq "NY" }
$nyCustomers

This command filters the customers and returns only those who are from New York.

The exact output can be seen in the screenshot below.

PowerShell import-csv examples

Export Data

After filtering the data, you might want to export it to a new CSV file. You can do this using the Export-Csv cmdlet in PowerShell.

$nyCustomers | Export-Csv -Path "C:\MyNewFolder\ny_customers.csv" -NoTypeInformation

This command exports the filtered data to a new CSV file named ny_customers.csv.

Check out Convert CSV to HTML Table in PowerShell

Sort Data

You can also sort the imported data using the Sort-Object cmdlet. For example, to sort the customers by their last name:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv"
$sortedCustomers = $customers | Sort-Object LastName
$sortedCustomers

This command sorts the customers alphabetically by their last names.

You can see the exact output in the screenshot below:

import-csv in PowerShell

Remove Duplicates

If your CSV file contains duplicate entries, you can remove them using the Select-Object cmdlet with the -Unique parameter:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv"
$uniqueCustomers = $customers | Select-Object -Unique Email
$uniqueCustomers

This command removes duplicate entries based on the Email column.

Check out How to Export Table to CSV in PowerShell?

Combine Multiple CSV Files

If you have multiple CSV files and you want to combine them into a single file, you can use the Import-Csv and Export-Csv cmdlets together in PowerShell. For example, if you have two files, customers1.csv and customers2.csv:

$customers1 = Import-Csv -Path "C:\MyNewFolder\customers1.csv"
$customers2 = Import-Csv -Path "C:\MyNewFolder\customers2.csv"
$allCustomers = $customers1 + $customers2
$allCustomers | Export-Csv -Path "C:\MyNewFolder\all_customers.csv" -NoTypeInformation

This command combines the data from both files and exports it to a new file named all_customers.csv.

Using Custom Delimiters

If your CSV file uses a delimiter other than a comma, you can specify it using the -Delimiter parameter. For example, if your file uses a semicolon (;) as a delimiter:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv" -Delimiter ";"

Handle Large Files

For large CSV files, you might want to process the data in chunks to avoid consuming too much memory. You can read and process the file line by line using a combination of Get-Content and ConvertFrom-Csv:

Get-Content -Path "C:\MyNewFolder\customers.csv" | ForEach-Object {
    $line = $_ | ConvertFrom-Csv
    # Process each line here
}

Check out Convert JSON to CSV in PowerShell

Remove Unnecessary Columns

Sometimes, you might need to remove certain columns from the CSV data. You can do this by selecting only the columns you need:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv"
$selectedColumns = $customers | Select-Object FirstName,LastName,Email

This command selects only the FirstName, LastName, and Email columns from the imported data.

Add New Columns

You can also add new columns to your imported data. For example, to add a FullName column:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv"
$customers | ForEach-Object {
    $_ | Add-Member -MemberType NoteProperty -Name FullName -Value "$($_.FirstName) $($_.LastName)"
}

This command adds a FullName column that concatenates the FirstName and LastName columns.

Check out Get Unique Values from CSV Using PowerShell

Convert Data Types

If you need to convert data types, such as converting a string to a date, you can do this after importing the CSV data. For example:

$customers = Import-Csv -Path "C:\MyNewFolder\customers.csv"
$customers | ForEach-Object {
    $_.DateOfBirth = [datetime]::Parse($_.DateOfBirth)
}

This command converts the DateOfBirth column from a string to a DateTime object.

Conclusion

In this tutorial, I explained how to use the Import-CSV PowerShell cmdlet using real examples and how to filter, manipulate, or export data using it using PowerShell Import-CSV cmdlet.

You may also like the following tutorials:

100 PowerShell cmdlets download free

100 POWERSHELL CMDLETS E-BOOK

FREE Download an eBook that contains 100 PowerShell cmdlets with complete script and examples.