PowerShell Import-CSV Cmdlet: Parse a Comma-Delimited CSV Text File

In this Ask the Admin, I’ll show you how to go beyond what is capable with the get-content cmdlet and parse a comma-delimited text file.

PowerShell has two cmdlets that read and parse text files that are commonly encountered by IT professionals, such as logs that need to be analyzed or files that are used as part of automated processes, such as spreadsheets with a list of computer names and IP addresses.

PowerShell’s get-content cmdlet can read and extract information from simple files where there is only one piece of information per line. For more on how to use get-content, see How to Copy Files to Multiple Computers by Reading Simple Text Input Using PowerShell on Petri.

Comma-Delimited Text Files

The following is an example of a typical comma-delimited text file (.csv) file. You’ll notice at the top that there are headings for each column: Computername, IPaddress, Office, and Owner. The PowerShell import-csv cmdlet understands this default format and expects to see the column headers before parsing the main data.

​ Computername,IPaddress,Office,Owner
computer1,192.168.0.1,London,JoeSmith
computer2,192.168.0.2,London,BobJones
computer3,192.168.0.3,London,JohnSutton
computer4,192.168.0.4,London,DavidSouth
computer5,192.168.0.5,London,RussellSmith
computer6,192.168.0.6,London,RussellSmith

Using PowerShell’s import-csv cmdlet

Import-csv can read the text file and display the data in the console as shown below. The file extension can be .csv or .txt.

​import-csv “c:tempcomputers.csv”

Just like other PowerShell cmdlets, you can filter and sort information. The following command shows all computers owned by Russell Smith.

​import-csv “c:tempcomputers.csv”| where-object {$_.owner -eq "russellsmith"}

Adding Headers and Changing the Default Delimiter

The –header parameter allows you to specify a string, which adds column headers if they are not present in the text file being imported. Additionally, while import-csv expects a comma as the default symbol for delimitation of data fields, you can specify a different symbol using the –delimiter parameter. For example to import the .csv file below, which doesn’t have headers for each column and uses a semi-colon as the delimiter, I used this command:

​import-csv “c:tempcomputers.csv” –header Name, IP, Location, User –delimiter :

​"computer1":"192.168.0.1":"London":"JoeSmith"
"computer2":"192.168.0.2":"London":"BobJones"
"computer3":"192.168.0.3":"London":"JohnSutton"
"computer4":"192.168.0.4":"London":"DavidSouth"
"computer5":"192.168.0.5":"London":"RussellSmith"
"computer6":"192.168.0.6":"London":"RussellSmith"

More complex processing can be achieved by parsing the file using import-csv as part of a foreach loop. In the example below, each line of the file is parsed and the field data separated as variables. Just to prove that it works, I’ve added the write-host cmdlet at the end of the loop to output the results to the console. Naturally, you can do whatever is required with the data in your own scripts.

​$computers = import-csv “c:tempcomputers.csv”
ForEach ($computer in $computers){
$computername = $($computer.Computername)
$ipaddress = $($computer.IPaddress)
$office = $($computer.Office)
$owner = $($computer.Owner)
Write-host $computername $ipaddress $office $owner
}

The script above parses all the lines of the text file in turn. In the same way as is possible with the get-content cmdlet, you can determine the length of the text file, process individual lines, or a range of lines. The following snippet of code determines the length of the text file and outputs the result to the console:

​$computers = “c:tempcomputers.txt” $output = (import-csv $computers).length write-host $output

The next piece of code writes only the third line of the text file to the console:

$computers = “c:tempcomputers.txt” $output = (import-csv $computers)[3] write-host $output

Replace the Delimiter

It’s easy to replace the delimiter in a file, for example if you need to use the file in a system that only supports a specific delimiter. Start by importing the text file using the import-csv cmdlet, and then export it with export-csv, giving it a different file name and adding the –delimiter parameter with the desired character as shown here:

​$computers = import-csv “c:tempcomputers.txt” $computers | export-csv “c:tempcomputers2.txt” -delimiter :

FAQs

Can I use PowerShell to parse CSV files with special characters or Unicode content?

Yes, PowerShell parse CSV file operations support special characters and Unicode content by using the -Encoding parameter with Import-CSV. You can specify encodings like UTF8, Unicode, or ASCII to properly handle international characters and special symbols.

How do I handle errors when using PowerShell to parse CSV files with missing or corrupted data?

When using PowerShell to parse CSV file data with errors, you can implement try-catch blocks and use the -ErrorAction parameter. Additionally, you can validate data using if-else statements and create error logs for problematic entries.

Can I use PowerShell to parse CSV files that are password protected or encrypted?

Yes, PowerShell can parse CSV file content that’s protected by implementing the System.Security.Cryptography namespace and using additional modules like Import-Excel. You’ll need to handle decryption before parsing the data.

Is it possible to parse multiple CSV files simultaneously using PowerShell?

PowerShell can parse CSV file collections in parallel using ForEach-Object -Parallel or workflow jobs. You can process multiple files concurrently and merge the results into a single output for enhanced performance.

How can I optimize PowerShell CSV parsing for large files?

To optimize PowerShell parse CSV file operations for large datasets, use streaming techniques with Get-Content -ReadCount, implement garbage collection, and consider using .NET StreamReader for better memory management with extensive files.