Replace NULL value in powershell output

Question

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?

Are you asking for empty strings to be changed to "No Data" during export or import? — Benjamin Hubbard
– Benjamin Hubbard, Commented Nov 3, 2015 at 16:21

Benjamin Hubbard · Accepted Answer · 2015-11-03 17:11:29Z

4

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

edited Nov 3, 2015 at 17:11

answered Nov 3, 2015 at 16:36

Benjamin Hubbard

2,92524 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dugas Over a year ago

The custom object was there to demonstrate the creation of the object in the example, not to show it was necessary. After posting I deleted my answer because after some thought, I think there is a better way in which you don't have to make explicit the conversion for each property.

bonneyt Over a year ago

Well, i'm not importing anything... I'm using the get-itemproperty cmdlet to parse a registry key. that data gets exported to CSV, but that's not really the point. Looking at your select-object example, i think i can make it work.

Benjamin Hubbard Over a year ago

Modified the example to reflect that.

dugas · Accepted Answer · 2015-11-03 16:55:19Z

4

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 $_
}

edited Nov 3, 2015 at 16:55

answered Nov 3, 2015 at 16:21

dugas

12.6k3 gold badges48 silver badges54 bronze badges

1 Comment

mbourgon Over a year ago

Thanks for the idea of using $_.PSObject.Properties! Was able to a modified version of that (since I ONLY wanted nulls, and because it doesn't handle an int that is 0 properly). instead of the [string]::empty, used $null. Means you don't need to write something to handle every field that comes through. Definitely The Powershell Way to do this.

Community · Accepted Answer · 2017-05-23 11:58:37Z

1

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Nov 3, 2015 at 16:51

Mathias R. Jessen

178k13 gold badges175 silver badges234 bronze badges

Comments

Nate · Accepted Answer · 2015-11-03 16:47:52Z

0

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".

answered Nov 3, 2015 at 16:47

Nate

8621 gold badge10 silver badges30 bronze badges

Comments

Jeter-work · Accepted Answer · 2015-11-03 16:59:20Z

0

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

answered Nov 3, 2015 at 16:59

Jeter-work

8019 silver badges23 bronze badges

Collectives™ on Stack Overflow

Replace NULL value in powershell output

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related