Powershell - remove spaces from a column of a CSV file

Question

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?

Esperento57 · Accepted Answer · 2017-08-11 19:45:51Z

2

try this

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

answered Aug 11, 2017 at 19:45

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

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

Comments

score 0 · Accepted Answer · 2017-08-11 21:51:54Z

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"

Shorter way to export the csv with no header: $CSV | ConvertTo-CSV -NoType | Select -Skip 1 | Set-Content $OutputCSVFile
@TheMadTechnician Thanks for the hint, I intentionally left the last line as an option in case the headers are useful for further processing. I'll change this.

Collectives™ on Stack Overflow

Powershell - remove spaces from a column of a CSV file

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related