Last Update: Sep 04, 2024 | Published: Jun 16, 2015
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.
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
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"}
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
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 :