Trim of CSV column data, stating null-value expression

ryanstanford2 · 2017-10-31T13:01:27.000Z

Hello,

I have a csv file that through manipulation has an extra space in a column that I’m looking to trim.

I have tried:

$Trim = Import-Csv $File | ForEach-Object{$_.PSObject.Properties | ForEach-Object{$_.EEpayment = $_.EEPayment.Trim()}}

but get, “You cannot call a method on a null-valued expression.”

psophos · 2017-10-31T13:12:52.000Z

Do you even need that first foreach-object:

Start smaller…

Import-Csv $File | 
    Select-Object *, @{n=EEpayTrim; e={$_.EEPayment.Trim()}}

see if this gives anything

Evan7191 · 2017-10-31T13:20:31.000Z

Is the space at the beginning, middle, or end of the column?

ryanstanford2 · 2017-10-31T14:05:35.000Z

Evan7191:

Is the space at the beginning, middle, or end of the column?

End of the column, and it appears to only show if the csv is opened in notepad, or converted to a different format that is opened in notepad(like my script eventually does do).

Evan7191 · 2017-10-31T14:46:39.000Z

Try something like this:

$Trim = Import-Csv $File
$Trim = $Trim -ireplace '\s,',','
$Trim | Out-File drive:\path\file.csv

That will find and remove any space that appears immediately before a comma. It will not affect spaces that are not followed by a comma, and it will not affect commas that do not have a space immediately before them.

ryanstanford2 · 2017-10-31T17:31:26.000Z

Evan7191:

Try something like this:
$Trim = Import-Csv $File
$Trim = $Trim -ireplace '\s,',','
$Trim | Out-File drive:\path\file.csv
That will find and remove any space that appears immediately before a comma. It will not affect spaces that are not followed by a comma, and it will not affect commas that do not have a space immediately before them.

This exported the length of every row, but it did head me in the right direction. I added the ‘\s’ replace as part of the export back to the file. I’m sorry if that doesn’t make sense, but it works in my code so… Hoozah!

$Trim | ConvertTo-Csv -NoTypeInformation | ForEach-Object{$_ -replace '\s',','} | ForEach-Object{$_ -replace '["()$-]', ''} | Out-File $File -fo -en ascii

Evan7191 · 2017-10-31T17:59:25.000Z

Ryan5204:

Evan7191:
Try something like this:
$Trim = Import-Csv $File
$Trim = $Trim -ireplace '\s,',','
$Trim | Out-File drive:\path\file.csv
That will find and remove any space that appears immediately before a comma. It will not affect spaces that are not followed by a comma, and it will not affect commas that do not have a space immediately before them.
This exported the length of every row, but it did head me in the right direction. I added the ‘\s’ replace as part of the export back to the file. I’m sorry if that doesn’t make sense, but it works in my code so… Hoozah!
$Trim | ConvertTo-Csv -NoTypeInformation | ForEach-Object{$_ -replace '\s',','} | ForEach-Object{$_ -replace '["()$-]', ''} | Out-File $File -fo -en ascii

You should use “\s,” because \s by itself will replace every space, which you may not want. \s, will remove only spaces followed by a comma, which indicates that the space is at the end of a column.

ryanstanford2 · 2017-10-31T19:01:00.000Z

Evan7191:

You should use “\s,” because \s by itself will replace every space, which you may not want. \s, will remove only spaces followed by a comma, which indicates that the space is at the end of a column.

Good catch, that was a typo on my part. The specific file I was using didn’t have any other spaces, but that’s more by chance than anything.