Solved

Powershell Question

Posted on 2010-09-21
19
1,050 Views
Last Modified: 2012-05-10
Hi I want to get a csv file and remove all carriage returns and replace it with a single carriage return.

Normally this code works like magic but for some reason it is not recognizing the carriage return character can anyone thing of an alternative way for me to do this or tell me what I'm doing wrong. Thanks.
$filename = "xxx.csv"
$match = "`r`r"
$replacement = "`r"
$content = get-content $filename
$content = $content -creplace $match,replacement

Open in new window

0
Comment
Question by:craigthackray
  • 8
  • 6
  • 3
  • +2
19 Comments
 
LVL 4

Expert Comment

by:geieea
ID: 33729907
$content = $content -creplace $match,$replacement
0
 
LVL 2

Expert Comment

by:Procastin8or
ID: 33730079
I'm not too familiar with powershell, but I did find this:
http://stackoverflow.com/questions/325953/how-can-i-replace-newlines-using-powershell
0
 
LVL 38

Expert Comment

by:Adam Brown
ID: 33730226
`n is the carriage return character in Powershell.
0
 

Author Comment

by:craigthackray
ID: 33731181
Sorry my code  sample is wrong I did include the dollar sign. Thanks but the Stack overlfow example still isn't helping.
0
 
LVL 38

Expert Comment

by:Adam Brown
ID: 33731379
I think the problem is that you can't actually replace text that is only carriage returns or spaces. Of course, parsing makes me want to eat my own feet, so I try to avoid it whenever possible. The only thing I can think of, though, that doesn't require parsing is to pass the content into two variables and do a kind of hopscotch thing going through the two arrays in a do {}while loop. So, something like,
if ((array1[$i] -eq $null) -and (array1[$i+1] -eq $null))
{
$i++
}
else
{
array2[$i] = array1[$i]
}

Or something like that. Works in my head, but I haven't tested it and it could take some tweaking to work right.
0
 
LVL 38

Expert Comment

by:Adam Brown
ID: 33731383
Oh, you'd need an $i++ in the else section, too.
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33741895

Can you post a sample of the data you want to clean up? You should obscure anything sensitive, it's only really the line breaks you want to clean that I'd like to see.

Chris
0
 

Author Comment

by:craigthackray
ID: 33743140
Attached is a sample. Basically I have a huge amount of these I need to loop through and remove the carriage returns for each file. All your suggestions have been appreciated. Thanks.
Statement.csv
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33743197
Ahh I see.

So it's not so much that you have carriage returns, rather that you have "blank" lines?

In which case doesn't this kill them off?

Get-Content Statement.csv.txt | Where-Object { $_ -NotMatch '^,*$' -And $_ -ne "" } > NewFile.txt

Basically wipes out any line that only consists of commas or is entirely blank.

HTH

Chris
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:craigthackray
ID: 33743237
Thanks Chris this is great and it does remove spaces but if I attempt to put it back into a csv file it still removes the formatting so I don't have columns any longer just one flat row so it still doesn't fix my problem. Any other thoughts would be appreciated. Once again Thanks.
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33743268

This doesn't kill the existing line-breaks. I guess you're just doing this to dump it to CSV?

Get-Content Statement.csv.txt | Where-Object { $_ -NotMatch '^,*$' -And $_ -ne "" } > NewFile.csv

Chris
0
 

Author Comment

by:craigthackray
ID: 33743278
Yes and a question why do you append .txt extenstion on the csv file do I need to do this to the file before I try to run this code?
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 33743297

No, you don't need that. I only have it because your attachment has it :) Feel free to drop it off, it's not necessary, CSV is a plain text format and the operation of this script won't be affected by the source file's extension.

Chris
0
 

Author Comment

by:craigthackray
ID: 33743326
Chris when I convert it is the same as before I'm getting one column with all my data. Different rows but one column. Am I doing something wrong or are you getting these same results. I'm using Excel 2007 to open the CSV file btw.
0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
ID: 33743394

Hmm now that's interesting. It doesn't like the file encoding. Forced to ASCII here which seems to cheer it up. Give it a try?

Chris
Get-Content Statement.csv | Where-Object { $_ -NotMatch '^,*$' -And $_ -ne "" } | Set-Content NewFile.csv -Encoding ASCII

Open in new window

0
 

Author Comment

by:craigthackray
ID: 33743423
Genius really genius this an end to my question but I have one more maybe you can help me. If you cant still thanks for all the help but if you can even better. If I have a folder with just these files is there any way I can loop through all and perform this bit of code on each file not creating a new file but just saving over the original file?
0
 
LVL 70

Assisted Solution

by:Chris Dent
Chris Dent earned 500 total points
ID: 33743468

Yes, indeed :)

This should do it. Needed to break it up a bit so it could write back to the source file.

Chris
Get-ChildItem "TheFolder" | ForEach-Object {
  $FileName = $_.FullName
  $Content = Get-Content $FileName | Where-Object { $_ -NotMatch '^,*$' -And $_ -ne "" }
  $Content | Set-Content $FileName -Encoding ASCII
}

Open in new window

0
 

Author Comment

by:craigthackray
ID: 33743749
Once again thank you sir.
0
 

Author Closing Comment

by:craigthackray
ID: 33743764
This code solves my problem and even handles an additional problem I had.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This is about my first experience with programming Arduino.
A procedure for exporting installed hotfix details of remote computers using powershell
The viewer will learn how to count occurrences of each item in an array.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now