how to add extra ';'(semi colon) or any special character in all columns except first column and empty cell value from csv using powershell

user_16887688 0
Viewed 134

I am using below code to add extra special character on all column except first column and empty cell value but code isn't working yet.

$path = 'F:\temp.csv'

Import-Csv $path | ForEach-Object {
    if ($_.value -eq $path.value ) {
        $_.value = ';'+$path.value
    }
    $_
} | Export-Csv $path -NoTypeInformation

Please see Image for more details: Image

Please find csv file uploaded on Google Drive: CSV FILE

csv powershell
1 Answers

I'm not sure what you were trying to do with $a, but if we remove that seems like you were close :)

Instead of checking if a value equals something we will just take every object and add a new property (i.e., a new column) to the object. We will call it 'B' and make the value the same as what's in 'A' but prefix a ';' to it using -replace. This replace operator uses regex so if we specify that we would like to replace '^', we are saying we would like to replace the start of the line/value. We replace this with ';' and save as our value. Then we output the object as you were already doing and then convert back to csv.

$path = 'F:\temp.csv'
Import-Csv $path | ForEach-Object {
    $_ | Add-Member -NotePropertyName 'B' -NotePropertyValue ($_.A -replace '^', ';')
    # or instead of -replace we can do similar to how you were trying
    # $_ | Add-Member -NotePropertyName 'B' -NotePropertyValue (';' + $_.A)
    $_
} | Export-Csv $path -NoTypeInformation

Update Per your comment, this code should update values in all columns except the first where value is not null/empty

$path = 'F:\temp.csv'
$data = Import-Csv $path 

# Track all the properties (columns) minus the first
$props = $data[0].psobject.Properties.Name | Select-Object -Skip 1

$data | ForEach-Object {
    foreach ($prop in $props) {
        if ( -not ([string]::IsNullOrWhiteSpace($_.$prop)) ){
            $_.$prop = ';' + $_.$prop
        }
    }
    $_
} | Export-Csv $path -NoTypeInformation
user_119540250
Related