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”
Using Windows PowerShell import-csv to parse a comma-delimited text file (Image: Russell Smith)
Using Windows PowerShell import-csv to parse a comma-delimited text file (Image: Russell Smith)

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"}
Filtering and sorting information with PowerShell's import-csv cmdlet. (Image: Russell Smith)
Filtering and sorting information with PowerShell’s import-csv cmdlet. (Image: Russell Smith)

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 :