Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Powershell Question

Posted on 2010-09-21
19
Medium Priority
?
1,061 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 43

Expert Comment

by:Adam Brown
ID: 33730226
`n is the carriage return character in Powershell.
0
Ready for your healthcare security check-up?

In the past few years, healthcare organizations have become a prime target for advanced attacks. Does your organization have what it needs to defend itself? Schedule your healthcare security check-up today and download our free Healthcare Security Resource Kit today!

 

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 43

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 43

Expert Comment

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

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 71

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
 

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 71

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 71

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 71

Accepted Solution

by:
Chris Dent earned 2000 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 71

Assisted Solution

by:Chris Dent
Chris Dent earned 2000 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Suggested Courses

610 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