csv reformatting with powershell

Question

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?

Shay Levy · Accepted Answer · 2013-07-02 15:01:27Z

1

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.

answered Jul 2, 2013 at 15:01

Shay Levy

128k34 gold badges193 silver badges210 bronze badges

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

2 Comments

latkin Over a year ago

I would suggest accounting for arbitrary amount of whitespace on either side of the ;, by using $_ -replace '\s*;\s*', ','

Shay Levy Over a year ago

That's valid as well, I'd use + instead of *

Aaron Jensen · Accepted Answer · 2013-07-02 18:42:39Z

0

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

answered Jul 2, 2013 at 18:42

Aaron Jensen

27.2k18 gold badges85 silver badges93 bronze badges

2 Comments

Χpẘ Over a year ago

-replace also takes an array (or output of a pipe) as input: (cat .\bestand.txt) -replace ' *; *',',' | Out-File .\Bestand.csv -Encoding OEM

Brtrnd Over a year ago

There was an arbitrary amount of spaces so this worked as a charm.

Ansgar Wiechers · Accepted Answer · 2013-07-02 17:47:10Z

0

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

answered Jul 2, 2013 at 17:47

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Collectives™ on Stack Overflow

csv reformatting with powershell

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related