?
Solved

Powershell Question

Posted on 2010-09-21
19
Medium Priority
?
1,060 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 42

Expert Comment

by:Adam Brown
ID: 33730226
`n is the carriage return character in Powershell.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 42

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 42

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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 …
Suggested Courses

764 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