CODEWITHSUNDEEP
csvpowershell
bonneyt
bonneyt

Reputation: 99

Replace NULL value in powershell output

I'm really struggling with what seems to be a simple thing. Any help is appreciated...

tldr; i'm trying to find and replace blank or NULL values from powershell output to "No Data"

I am using the following powershell script to obtain installed application information

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\*

Below is sample output of the above script (filtered to my liking). I am taking this data, exporting it to CSV, in order to import into another application for analysis at a later time.

Host             : Computer1
DisplayName      : AutoDWG DWG DXF Converter 2015
Version          : 
Publisher        : 
InstallDate      : 
arrival_datetime : 2015-11-03T09:42:18

Host             : Computer2
DisplayName      : Beyond Compare Version 3.1.11
Version          : 
Publisher        : Scooter Software
InstallDate      : 20150218
arrival_datetime : 2015-11-03T09:42:18

the CSV export puts the data in the correct format, but where items have no data for version, publisher, etc..., it represents it as ",," (see below)

"Computer1","AutoDWG DWG DXF Converter 2015",,,,"2015-11-03T09:54:21"
"Computer2","Beyond Compare Version 3.1.11",,"Scooter Software","20150218","2015-11-03T09:54:21"

When importing the CSV, the blank values are re-interpreted as NULL, which throws the program for a loop, as it is expecting a string. I am attempting to change those to the string "No Data" but am having lots of trouble...

What would be the best way to handle this?

Upvotes: 4

Views: 21002

Answers (5)

Benjamin Hubbard
Benjamin Hubbard

Reputation: 2917

Using Select-Object would be your best bet. Just pipe the data to Select-Object, but customize each desired property as follows:

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\* | 
  Select-Object Host, DisplayName, @{
    Label = "Version"
    Expression = { if ($_.Version) { $_.Version } else { "No Data" } }
  }, Other Properties

Upvotes: 4

Jeter-work
Jeter-work

Reputation: 801

Another option that might work:

$TargetFile = "C:\temp\replaceNull.csv"
$File = Get-Content $TargetFile
$Output = @()
foreach ($Line in $File) {
    $Output += $Line -replace ",,",",No Data,"
}
$Output | Set-Content $TargetFile

Upvotes: 0

dugas
dugas

Reputation: 12433

You could inspect the property values as they are piped from the Import-Csv cmdlet, and change them. Example:

Get-ItemProperty HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall\* | 
ForEach-Object { 
foreach ($p in $_.PSObject.Properties) 
{ 
 if ($p.Value -eq [string]::Empty)
 {
  $p.Value = 'No Data'
 }
} 
 Write-Output $_
}

Upvotes: 4

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174445

Building off of this answer, you could generate the calculated properties like this:

$SelectProperties = "Host","DisplayName"
$NoDataFields = "Version","Publisher","InstallDate"

$SelectProperties += $NoDataFields|ForEach-Object {
    @{
      Label = $_
      Expression = [scriptblock]::Create("if(`$_.""$_""){ `$_.""$_"" } else { ""No Data"" }")
    }
}

$Data = Import-Csv -Path "C:\SomePath.csv" | 
  Select-Object $SelectProperties

Upvotes: 1

Nate
Nate

Reputation: 862

Do your columns have headers? This was how I did it, when I exported a CSV of groups and their owners. For any group that didn't have an owner in the "ManagedBy" column, it fills the field with "No Owner" instead of a blank space:

$CSVData = Import-Csv $TempCSV -Header "samAccountName", "ManagedBy"  
$CSVData | %{
if($_.ManagedBy -eq "") {$_.ManagedBy="No Owner"}
} 
$CSVData | Export-Csv $Filename -NoTypeInformation

You could just change the "ManagedBy" to your header name and the "$_.ManagedBy" to what you need, then obviously "No Owner" would be "No Data".

Upvotes: 0

Related Questions