A CSV file is a text file but is not meant to be read as text files. A CSV file stores the table data in plain text with each file separated by a comma. The file extension for CSV file is “.csv”. Let’s see how we can work with CSVs in Windows PowerShell.
To find all CSV Cmdlets in PowerShell, use Get-Help along with a wildcard character to search for CSV.
PS C:\Users\Sagar> Get-Help *csv* Name Category Module Synopsis ---- -------- ------ -------- epcsv Alias Export-Csv ipcsv Alias Import-Csv ConvertFrom-Csv Cmdlet Microsoft.PowerShell.U... ... ConvertTo-Csv Cmdlet Microsoft.PowerShell.U... ... Export-Csv Cmdlet Microsoft.PowerShell.U... ... Import-Csv Cmdlet Microsoft.PowerShell.U... ... Set-PcsvDeviceNetworkConfigura... Function PcsvDevice ... ......
The Import-Csv cmdlet creates table-like custom objects from the items in CSV files. Each column in the CSV file becomes a property of the custom object and the items in rows become the property values. Import-Csv works on any CSV file, including files that are generated by the Export-Csv cmdlet.
You can use the parameters of the Import-Csv cmdlet to specify the column header row and the item delimiter, or direct Import-Csv to use the list separator for the current culture as the item delimiter.
One can import the CSV rows in PowerShell objects using the Import-Csv command
#Given the following CSV-file String,DateTime,Integer First,2016-12-01T12:00:00,30 Second,2015-12-01T12:00:00,20 Third,2015-12-01T12:00:00,20 > $listOfRows = Import-Csv .\example.csv > $listOfRows String DateTime Integer ------ -------- ------- First 2016-12-01T12:00:00 30 Second 2015-11-03T13:00:00 20 Third 2015-12-05T14:00:00 20 > Write-Host $row[0].String1 Third
By default, Import-CSV imports all values as strings, so to get DateTime- and integer-objects, we need to cast or parse them.
> $listOfRows = Import-Csv .\example.csv > $listOfRows | ForEach-Object { #Cast properties $_.DateTime = [datetime]$_.DateTime $_.Integer = [int]$_.Integer #Output object $_ }
This example shows how to use the Header parameter of Import-Csv to change the names of properties in the resulting imported object.
Start-Job -ScriptBlock { Get-Process } | Export-Csv -Path .\Jobs.csv -NoTypeInformation $Header = 'State', 'MoreData', 'StatusMessage', 'Location', 'Command', 'StateInfo', 'Finished', 'InstanceId', 'Id', 'Name', 'ChildJobs', 'BeginTime', 'EndTime', 'JobType', 'Output', 'Error', 'Progress', 'Verbose', 'Debug', 'Warning', 'Information' # Delete the default header from file $A = Get-Content -Path .\Jobs.csv $A = $A[1..($A.Count - 1)] $A | Out-File -FilePath .\Jobs.csv $J = Import-Csv -Path .\Jobs.csv -Header $Header $J State : Running MoreData : True StatusMessage : Location : localhost Command : Get-Process StateInfo : Running Finished : System.Threading.ManualResetEvent InstanceId : a259eb63-6824-4b97-a033-305108ae1c2e Id : 1 Name : Job1 ChildJobs : System.Collections.Generic.List`1[System.Management.Automation.Job] BeginTime : 12/20/2018 18:59:57 EndTime : JobType : BackgroundJob Output : System.Management.Automation.PSDataCollection`1[System.Management.Automation.PSObject] Error : System.Management.Automation.PSDataCollection`1[System.Management.Automation.ErrorRecord] Progress : System.Management.Automation.PSDataCollection`1[System.Management.Automation.ProgressRecord] Verbose : System.Management.Automation.PSDataCollection`1[System.Management.Automation.VerboseRecord] Debug : System.Management.Automation.PSDataCollection`1[System.Management.Automation.DebugRecord] Warning : System.Management.Automation.PSDataCollection`1[System.Management.Automation.WarningRecord] Information : System.Management.Automation.PSDataCollection`1[System.Management.Automation.InformationRecord]
The Export-CSV cmdlet creates a CSV file of the objects that you submit. Each object is a row that includes a comma-separated list of the object's property values. You can use the Export-CSV cmdlet to create spreadsheets and share data with programs that accept CSV files as input.
Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties. To export only selected properties of an object, use the Select-Object cmdlet.
This example selects Process objects with specific properties, exports the objects to a CSV file.
Get-Process -Name WmiPrvSE | Select-Object -Property BasePriority,Id,SessionId,WorkingSet | Export-Csv -Path .\WmiData.csv -NoTypeInformation Get-Process | Export-Csv -Path .\Processes.csv -Delimiter ';' -NoTypeInformation
NoTypeInformation parameter removes the #TYPE information header from the CSV output
We can convert objects to/from CSV without using the filesystem. To achieve this, there are two PowerShell commands: ConvertTo-Csv and ConvertFrom-Csv.
The ConvertTo-CSV cmdlet returns a series of comma-separated value (CSV) strings that represent the objects that you submit.
Get-Process -Name pwsh | ConvertTo-Csv -NoTypeInformation "Name","SI","Handles","VM","WS","PM","NPM","Path","Parent","Company","CPU","FileVersion", ... "pwsh","8","950","2204001161216","100925440","59686912","67104", ...
The ConvertFrom-Csv cmdlet creates objects from CSV variable-length strings that are generated by the ConvertTo-Csv cmdlet
$P = Get-Process | ConvertTo-Csv $P | ConvertFrom-Csv