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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131

2 Answers2

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Matt
  • 45,022
  • 8
  • 78
  • 119
  • 2
    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` – evilSnobu Oct 17 '14 at 14:30
  • If you want ASCII encoding, you can trade Out-File for Set-Content, which uses a default encoding of ASCII. – mjolinor Oct 17 '14 at 15:20
  • @kamikatze Good to know about the encoding. In this case I was just following what the OP had as far as output. – Matt Oct 17 '14 at 15:27
  • 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 ?:) – evilSnobu Oct 17 '14 at 15:52
  • @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. – Matt Oct 17 '14 at 15:53
  • This is what I was able to my question, Many thanks to all who contributed you were very helpful, this script gets me to 90% solution: Get-Content –path C:\Users\USERNAME\Desktop\Results.txt| ForEach-Object {$_.Trim( ) -replace "\s+","," } | Out-File -filepath C:\Users\USERNAME\Desktop\Results.csv with the result is a csv file with all the answers commas included in the first cell of the spreadsheet, like this 5,48,469 any idea on how to get the numbers to break up into individual cells in excel?? –  Oct 29 '14 at 18:13
  • @user177073 perhaps you need to set the encoding on outfile. See updated example. `Out-File -filepath C:\Users\USERNAME\Desktop\Results.csv -Encoding ascii` – Matt Oct 29 '14 at 18:24
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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Avinash Babu
  • 6,171
  • 3
  • 21
  • 26