CODEWITHSUNDEEP
powershell
script0207
script0207

Reputation: 385

Powershell Remove spaces in the header only of a csv

First line of csv looks like this spaces are at after Path as well 

author    ,Revision  ,Date      ,SVNFolder ,Rev,Status,Path

I am trying to remove spaces only and rest of the content will be the same . 

author,Revision,Date,SVNFolder,Rev,Status,Path

I tried below

Import-CSV .\script.csv | ForEach-Object {$_.Trimend()}

Upvotes: 2

Views: 3476

Answers (4)

mklement0
mklement0

Reputation: 437803

Note: The solutions below avoid loading the entire CSV file into memory.

First, get the header row and fix it by removing all whitespace from it:

$header = (Get-Content -TotalCount 1 .\script.csv) -replace '\s+'

If you want to rewrite the CSV file to fix its header problem:

# Write the corrected header and the remaining lines to the output file.
# Note: I'm outputting to a *new* file, to be safe.
#       If the file fits into memory as a whole, you can enclose
#       Get-Content ... | Select-Object ... in (...) and write back to the
#       input file, but note that there's a small risk of data loss, if 
#       writing back gets interrupted.
& { $header; Get-Content .\script.csv | Select-Object -Skip 1 } |
  Set-content -Encoding utf8 .\fixed.csv

Note: I've chosen -Encoding utf8 as the example output character encoding; adjust as needed; note that the default is ASCII(!), which can result in data loss.

If you just want to import the CSV using the fixed headers:

& { $header; Get-Content .\script.csv | Select-Object -Skip 1 } | ConvertFrom-Csv

As for what you tried:

Import-Csv uses the column names in the header as property names of the custom objects it constructs from the input rows.

This property names are locked in at the time of reading the file, and cannot be changed later - unless you explicitly construct new custom objects from the old ones with the property names trimmed.

Import-Csv ... | ForEach-Object {$_.Trimend()}

Since Import-Csv outputs [pscustomobject] instances, reflected one by one in $_ in the ForEach-Object block, your code tries call .TrimEnd() directly on them, which will fail (because it is only [string] instances that have such a method).

Aside from that, as stated, your goal is to trim the property names of these objects, and that cannot be done without constructing new objects.

Upvotes: 0

user3520245
user3520245

Reputation: 1075

$inFilePath = "C:\temp\headerwithspaces.csv"
$content = Get-Content $inFilePath
$csvColumnNames = ($content | Select-Object -First 1) -Replace '\s',''
$csvColumnNames = $csvColumnNames -Replace '\s',''
$remainingFile = ($content | Select-Object -Skip 1)

Upvotes: 0

No Refunds No Returns
No Refunds No Returns

Reputation: 8336

expanding on the comment with an example since it looks like you may be new:

$text = get-content .\script.csv
$text[0] = $text[0] -replace " ", ""
$csv = $text | ConvertFrom-CSV

Upvotes: 1

Janne Tuukkanen
Janne Tuukkanen

Reputation: 1660

Read the whole file into an array:

$a = Get-Content test.txt

Replace the spaces in the first array element ([0]) with empty strings:

$a[0] = $a[0] -replace " ", ""

Write over the original file: (Don't forget backups!)

$a | Set-Content test.txt

Upvotes: 0

Related Questions