CODEWITHSUNDEEP
powershell
Brtrnd
Brtrnd

Reputation: 196

csv reformatting with powershell

I have a file cointaining a lot of lines in this format: 

firstname     ; lastname     ; age     ;

(it's a bit more complex but that's basically the file)

so the fields are of a fixed length, padded with spaces and with a semicolon in between the fields.

I would like to have it so: 

firstname, lastname, age,

(commas and no fixed width)

I have replaced the commas with regexp but I would like to also trim the end of the strings. But I don't know how to do this.

The following is my start, but I can't manage to get a ".TrimEnd()" in there. I have also thought of trying a "-replace(" ", " ") but I can't integrate it in this expression:

Get-Content .\Bestand.txt | %{$data= [regex]::split($_, ';'); [string]:: join(',', $data)}

Can I get some information on how to achieve this?

Upvotes: 0

Views: 2352

Answers (3)

Aaron Jensen
Aaron Jensen

Reputation: 26719

The -replace operator takes a regular expression, which you can use to remove all leading and trailing spaces:

Get-Content .\Bestand.txt | 
    Foreach-Object { $_ -replace ' *; *',',' } |
    Out-File .\Bestand.csv -Encoding OEM

Upvotes: 0

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

Since you already create something CSV-ish, I'd go all the way and create proper CSV:

$cols = "firstname","lastname","age","rest"

Import-Csv "C:\input.txt" -Delimiter ";" -Header $cols | % {
  foreach ($property in $_.PsObject.Properties) {
    $property.Value = ([string]$property.Value).Trim()
  }
  $_
} | Export-Csv "C:\output.csv" -NoTypeInformation

Upvotes: 0

Shay Levy
Shay Levy

Reputation: 126712

I suggest you replace each occurrence of 'space;space' with a comma (assuming the replaced characters do not appear within a valid value), so the end result will look like:

firstname,lastname,age

Keeping it like the following is not a good idea cause now some of your headers (property names) start with a space:

"firstname, lastname, age,"

Give this a try (work on a copy of the file):

(Get-Content .\Bestand.txt) | 
foreach {$_ -replace ' ; ',','} |
out-file .\Bestand.txt

Now it's easy to import and process the file with Import-Csv cmdlet.

Upvotes: 1

Related Questions