0

I'd like to use PowerShell to remove superfluous spaces and replace them with a single comma between entries so I can convert the result to a working CSV file for further analysis.

Here is my code;

Get-Content –path C:\Users\USERNAME\Desktop\results.txt| ForEach-Object {$_ -replace "\s+"  " " } |  Out-File -filepath C:\Users\USERNAME\Desktop\results.csv

In the text file, the initial results are like this:

 entry     entry     entry    (all separated with 5 spaces)

What I want it to look like is this:

entry,entry,entry

So Excel will put them in separate cells.

Improve this question

2 Answers 2

Reset to default
2

You were close, but you needed to replace with a "," instead of " ". Let’s assume your text files lines are like the following. I don’t know if you meant the leading/trailing space to be there, but that's OK.

" entry entry entry "

Get-Content –path C:\Users\USERNAME\Desktop\results.txt| ForEach-Object {$_.Trim() -replace "\s+"  "," } |  Out-File -filepath C:\Users\USERNAME\Desktop\results.csv -Encoding ascii

$_.Trim() -replace "\s+" "," use trim to remove outer spaces to prevent extra values in the CSV just in case.

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

7 Comments

Just a word of warning on Out-File. Default encoding is Unicode. ((Get-Content 'entry.txt') -replace ('\s{2,5}', ',')).Trim() | Out-File -Encoding ascii out.csv
If you want ASCII encoding, you can trade Out-File for Set-Content, which uses a default encoding of ASCII.
@kamikatze Good to know about the encoding. In this case I was just following what the OP had as far as output.
Also my oneliner is wrong. First Trim(), then -replace, otherwise the trailing spaces get comma'd. I did not account for that. How do i downvote myself ?:)
@kamikatze update your comment correctly and use backticks. Once it is ready just delete the incorrect comment. I already have that in my answer essentially anyway.
|
0

You can use trim which removes characters (by default, spaces) from the beginning or end of a string. Have a look here.

This can be done in another way if you want to do this with the replace character with a regex. Have a look here too.

Improve this answer

Comments