Share via

Powershell query to replace NULLs/empty cells in CSV with 0

Manny 151 Reputation points
2022-03-08T15:30:07.317+00:00

Hi everyone
cc @Andreas Baumgarten , @Rich Matheisen

I need to revise the powershell script so that any time the CSV has an empty cell then replace that empty cell with a 0. How can I do this? Script is below: 

$Source = "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/daily-treasury-rates.csv/all/all?type=daily_treasury_yield_curve&field_tdr_date_value=all&page&_format=csv"  
$DestinationTemp1 = "C:\Users\Me\Discount_Rates\ratestemp1.csv"  
$DestinationTemp2 = "C:\Users\Me\Discount_Rates\ratestemp2.csv"  
$Destination = "C:\Users\Me\Discount_Rates\rates.csv"  
  
Invoke-WebRequest -Uri $source -OutFile $DestinationTemp1  
  
$oneshot = $true  
Import-Csv $DestinationTemp1 |  
         ForEach-Object {  
             if ($oneshot){  
                 $oneshot = $false  
                 "QUOTE_DATE,RATE_MONTH_1,RATE_MONTH_2,RATE_MONTH_3"   # header  
             }  
             "{0},{1},{2},{3}" -f ([datetime]::ParseExact($_.Date, "M/d/yyyy", $null)).ToShortDateString(), $_."1 Mo", $_."2 Mo", $_."3 Mo"  
         } | Out-File $DestinationTemp2 -Encoding Utf8  
  
Get-Content $DestinationTemp2 -Encoding UTF8 | ForEach-Object {$_ -replace '"',''} | Out-File $Destination -Encoding UTF8  
  
Remove-Item $DestinationTemp1  
Remove-Item $DestinationTemp2

Thank you

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,462 questions
0 comments
Accepted answer
  1. Rich Matheisen 45,906 Reputation points
    2022-03-08T19:17:40.92+00:00

    This should do nicely: 

    $Source = "https://home.treasury.gov/resource-center/data-chart-center/interest-rates/daily-treasury-rates.csv/all/all?type=daily_treasury_yield_curve&field_tdr_date_value=all&page&_format=csv"
$Destination = "C:\Users\Me\Discount_Rates\rates.csv"

$oneshot = $true       
ConvertFrom-CSV (Invoke-WebRequest -Uri $source) |
    ForEach-Object {
        if ($oneshot) {
            $oneshot = $false
            $props = $_.psobject.properties.name
            "QUOTE_DATE,RATE_MONTH_1,RATE_MONTH_2,RATE_MONTH_3"   # header
        }
        ForEach ($prop in $props) {
            if ($_.$prop.length -lt 1) {
                $_.$prop = 0
            }
        }
        "{0},{1},{2},{3}" -f ([datetime]::ParseExact($_.Date, "M/d/yyyy", $null)).ToShortDateString(), $_."1 Mo", $_."2 Mo", $_."3 Mo"
    } | Out-File $Destination -Encoding Utf8

    Note: The script doesn't use any intermediate files. Nor does it need to remove quotation marks.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. SChalakov 10,371 Reputation points MVP
    2022-03-08T18:53:44.207+00:00

    Hi @Manny ,

    I am currently unable to test the script itself, but here are some nice and easy to follow suggestions. First a suggestion from jrv, who was the "owner" of the Technet PowerShell forums :)

    How to work with a CSV with blank field
    https://social.technet.microsoft.com/Forums/en-US/35edd1cb-6972-4fae-9dde-126c1eb9e747/how-to-work-with-a-csv-with-blank-fields?forum=winserverpowershell

    and also a nice one from Stack Overflow:

    Replace empty fields in CSV with 0
    https://stackoverflow.com/questions/31308261/replace-empty-fields-in-csv-with-0

    Maybe those will help you do meet the requirements.

    Regards,

    ----------

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards
    -Stoyan Chalakov

    0 comments