PowerShell Import-CSV: Analysis of a CSV text file comma delimited

PowerShell Import-CSV: Analysis of a CSV text file comma delimited

10 November 2019 0 By editeur

Comma delimited text files

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

1
2
3
4
Computername,IPaddress,Office,Owner
ORDI1,192.168.0.1,Grasse,toto
ORDI2,192.168.0.2,Nice,tata
ORDI3,192.168.0.3,Cannes,titi

Using the PowerShell import-csv cmdlet

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

1
import-csv “c:\ordi.csv”

Like other PowerShell cmdlets, you can filter and sort information. The following command displays all computers belonging to foo.

1
import-csv “c:\ordi.csv”| where-object {$_. Owner -eq "toto"}

Add headers and change the default delimiter

The -header parameter allows you to specify a string that adds column headers if they are not present in the text file being imported. Also, while import-csv expects a comma as the default symbol for delimiting data fields, you can specify another symbol using the -delimiter parameter. For example, to import the .csv file below, which does not have a header for each column and uses a semicolon as a delimiter, I used this command:

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

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 added the write-host cmdlet at the end of the loop to export the results to the console. Of course, you can do all that is necessary with the data of your own scripts.

1
2
3
4
5
6
7
8
$computers = import-csv “c:ordi.csv”
ForEach ($computer in $computers){
$computername = $($computer.Computername)
$ipaddress = $($computer.IPaddress)
$office = $($computer.Office)
$owner = $($computer.Owner)
Write-host $computername $ipaddress $office $owner
}

Replace delimiter

It is 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, then export it with export-csv, giving it a different filename and adding the -delimiter parameter with the desired character, as shown here:

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