Replacing empty values in a CSV with PowerShell

Question

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.

Can you update your question including the CSV as plain text instead of a screenshot? — Santiago Squarzon
– Santiago Squarzon, Commented Sep 22, 2022 at 15:02
If I understand correctly, you just want to insert Dial= in the CO Line Access Number + Telephone Number (32 digits) if the value is an empty / null string. Is this correct ? — Santiago Squarzon
– Santiago Squarzon, Commented Sep 22, 2022 at 15:17

Santiago Squarzon · Accepted Answer · 2022-09-22 16:21:06Z

2

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

edited Sep 22, 2022 at 16:21

answered Sep 22, 2022 at 15:23

Santiago Squarzon

65.8k5 gold badges26 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Replacing empty values in a CSV with PowerShell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related