Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1064
  • Last Modified:

Powershell Question

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
craigthackray
Asked:
craigthackray
  • 8
  • 6
  • 3
  • +2
2 Solutions
 
geieeaCommented:
$content = $content -creplace $match,$replacement
0
 
Procastin8orCommented:
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
 
Adam BrownSr Solutions ArchitectCommented:
`n is the carriage return character in Powershell.
0
Improved Protection from Phishing Attacks

WatchGuard DNSWatch reduces malware infections by detecting and blocking malicious DNS requests, improving your ability to protect employees from phishing attacks. Learn more about our newest service included in Total Security Suite today!

 
craigthackrayAuthor Commented:
Sorry my code  sample is wrong I did include the dollar sign. Thanks but the Stack overlfow example still isn't helping.
0
 
Adam BrownSr Solutions ArchitectCommented:
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
 
Adam BrownSr Solutions ArchitectCommented:
Oh, you'd need an $i++ in the else section, too.
0
 
Chris DentPowerShell DeveloperCommented:

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
 
craigthackrayAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:
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
 
craigthackrayAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:

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
 
craigthackrayAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:

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
 
craigthackrayAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:

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
 
craigthackrayAuthor Commented:
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
 
Chris DentPowerShell DeveloperCommented:

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
 
craigthackrayAuthor Commented:
Once again thank you sir.
0
 
craigthackrayAuthor Commented:
This code solves my problem and even handles an additional problem I had.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 8
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now