CODEWITHSUNDEEP
powershell
BHNONE
BHNONE

Reputation: 21

Powershell - remove spaces from a column of a CSV file

I have a CSV file that has 1 column that has spaces in the middle of the column that I need to remove. Here is an example couple lines of data and what I would like the result to be. I would like to do this with powershell if possible.

Current:

"setvalue","36499","GORBEL INC","882","7/11/17","-   4989.00","R31836"
"setvalue","7047","5TH 3RD CC","19775","7/13/17","51.61",""
"setvalue","68329","J D NEUHAUSE, L.P.","56866","7/10/17","-       .12","R31838"
"setvalue","27085","MAGNETEK INC","727031","6/30/17","-   1002.57","R69706"

Intended output:

"setvalue","36499","GORBEL INC","882","7/11/17","-4989.00","R31836"
"setvalue","7047","5TH 3RD CC","19775","7/13/17","51.61",""
"setvalue","68329","J D NEUHAUSE, L.P.","56866","7/10/17","-.12","R31838"
"setvalue","27085","MAGNETEK INC","727031","6/30/17","-1002.57","R69706"

Can someone please show me the way to do this?

Upvotes: 2

Views: 6263

Answers (2)

user6811411
user6811411

Reputation:

To reference a column it needs a header, so:

$InputCSVFile=".\input.csv"
$OutputCSVfile=".\output.csv"

$headers = 1..7|%{"H{0}" -f $_}
$Csv = Import-Csv $InputCSVFile -Header $Headers
$Csv|ft -auto

ForEach ($Row in $Csv) {
     $Row.H6 = $Row.H6 -Replace ' '
}
$Csv | Export-Csv $OutputCSVfile -NoType
# to skip the header change the previous line with the following one
# $CSV | ConvertTo-CSV -NoType | Select -Skip 1 | Set-Content $OutputCSVFile

Sample output

> .\SO_45642679.ps1

H1       H2    H3                 H4     H5      H6          H7
--       --    --                 --     --      --          --
setvalue 36499 GORBEL INC         882    7/11/17 -   4989.00 R31836
setvalue 7047  5TH 3RD CC         19775  7/13/17 51.61
setvalue 68329 J D NEUHAUSE, L.P. 56866  7/10/17 -       .12 R31838
setvalue 27085 MAGNETEK INC       727031 6/30/17 -   1002.57 R69706


"setvalue","36499","GORBEL INC","882","7/11/17","-4989.00","R31836"
"setvalue","7047","5TH 3RD CC","19775","7/13/17","51.61",""
"setvalue","68329","J D NEUHAUSE, L.P.","56866","7/10/17","-.12","R31838"
"setvalue","27085","MAGNETEK INC","727031","6/30/17","-1002.57","R69706"

Upvotes: 0

Esperento57
Esperento57

Reputation: 17462

try this

$PathCSVFile="c:\temp\test.csv"
(import-csv $PathCSVFile | %{
     $_.ColumnNameWithproblem=$_.ColumnNameWithproblem.ToString().Replace(' ', '')
}) | export-csv $PathCSVFile -notype

Upvotes: 2

Related Questions