Replacing empty values in a CSV with PowerShell-CodePudding

my CSV contains the following data:

CSV in plain text:

System Speed Dialling Number,"Name (20 characters)","CO Line Access Number   Telephone Number (32 digits)","CLI Destination" 
0,"USER1","Dial=00123456300","Dial=" 
1,"USER2","Dial=00123456400","Dial=" 
2,"","","" 
3,"","","" 
4,"","","" 
5,"","","" 
6,"","","" 
7,"","","" 
8,"","","" 
9,"","","" 
10,"","",""

Up to 1000 rows.

I tried using the following script to replace the empty values in the "CO Line Access Number Telephone Number (32 digits)" and "CLI Destination" columns:

$dial1 = import-csv C:\TEMP\dial.csv
$dial1 | foreach-object {
$_.'CO Line Access Number   Telephone Number (32 digits)' = $_.'CO Line Access Number   Telephone Number (32 digits)'.replace("","Dial=") 
}
$dial1 | export-csv C:\TEMP\dial.csv –NoTypeInformation

Error: The string cannot be zero-length

Question: Is there a way to fill the columns mentioned using a script and the value "Dial="

Sorry I have no experience with this...

Alternatively, I thought of a script that writes only the lines with users from one CSV to another CSV (ex. where "Name (20 Characters)" isn´t empty). Unfortunately I couldn't find anything about this.

CodePudding user response：

You can follow this logic to accomplish it, check if the value on the column CO Line Access Number Telephone Number (32 digits) is a null or whitespace string using String.IsNullOrWhiteSpace, if the condition evaluates to $true, re-assign the desired value (Dial=) to that property.

Import-Csv path\to\csv.csv | ForEach-Object {
    if([string]::IsNullOrWhiteSpace($_.'CO Line Access Number   Telephone Number (32 digits)')) {
        $_.'CO Line Access Number   Telephone Number (32 digits)' = 'Dial='
    }
    $_
} | Export-Csv newfixedcsv.csv -NoTypeInformation

To briefly explain the error, .Replace($oldValue, $newValue) complains because the argument for $oldValue cannot be an empty string ('' or [string]::Empty or [NullString]::Value).