Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do I remove characters from a text file ifrom each row?

Posted on 2011-10-06
8
Medium Priority
?
3,923 Views
Last Modified: 2012-05-12
Hello Experts,

I am getting better at scripting in powershell, but I am still a novice and I am having problems finding a solution to this one..

I have a group of .txt files exported from SQL Server and delimited with a pipe - I
I am trying to remove all characters and spaces that are before the first | in each row.

Anyone able to help with this one? I could use Textpad, but that wouldn't help me learn this.

Also,  any resource you have on this would be good too. I didn't find anything in google or I just didn't understand what I saw.


Idea of how the data look:


             12345| File Path would sit here | more information about the data | etc |

I need to remove that ID section leaving the pipe. That first column is fixed in the length but not the data.
0
Comment
Question by:jb428j
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 3

Expert Comment

by:LindyS
ID: 36928756
If you're using PowerShell to export the field, you could use the split option.

If you have the same number of pipes you want ignored, it would be something like;

{$_."ObjectName".Split("|")[-4]}

That would remove everything after the fourth pipe symbol from the right.
0
 
LVL 12

Expert Comment

by:GusGallows
ID: 36930668
In powershell you can use a combination of the indexof() function and the substring() function. The indexof() function will return the position of the first occurance of the "|" in your string. The substring will return the last so many characters of the string. So you figure out what position the object is in, subtract it from the length of the string, and then return those many characters from the right of the string. The code looks as follows:

#Write the string to a variable
$line = "12345| File Path would sit here | more information about the data | etc |"
#grab the length of the string
$len = $line.length
#find the character position of the first "|"
$PipePos = $line.indexof("|")
#subtract the position of the first character from the length of the string
$retChars = ($len - $PipePos)
#return everything except what is before the first "|"
$return = $line.Substring($len - $retchars, $retChars)
$return

Open in new window

0
 
LVL 12

Accepted Solution

by:
GusGallows earned 2000 total points
ID: 36931964
To take it a step further, you can use the following to read in the entire file, strip the parts out you want stripped out and then rewrite it into a new text file.

#create an output file to write the converted text to
$out = "c:\outputfile.txt"

#Get the contents of the file into an array
$lines = get-content "c:\filename.txt"

#go through each line and strip out the character prior to the first "|"
foreach ($line in $lines)
{
	#grab the length of the string
	$len = $line.length

	#find the character position of the first "|"
	$PipePos = $line.indexof("|")

	#subtract the position of the first character from the length of the string
	$retChars = ($len - $PipePos)

	#return everything except what is before the first "|"
	$return = $line.Substring($len - $retchars, $retChars)
	
	#write the new line to the output file
	out-file $out -inputobject $return -append
}

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Brent Challis
ID: 36940484
Here are two more approaches to the problem.

 The first is based on the assumption that you have a fixed number of characters that you want to prune from the beginning of each line, the second goes looking for the | symbol.

Each of these could be put in to a ps1 file and the file called with the appropriate filenames, or alternatively, you could put the code in to a function in a profile script.
Param ($SourceFile,$OutputFile)
{
Get-Content $SourceFile | ForEach-Object {$_.SubString(5)} | Out-File $OutputFile
}

Param ($SourceFile,$OutputFile)
{
Get-Content $SourceFile | ForEach-Object {$_.SubString($_.IndexOf('|'))} | Out-File $OutputFile
}

Open in new window

0
 
LVL 8

Expert Comment

by:Brent Challis
ID: 36940507
A more complex but more flexible approach would be to define a filter in a profile script that takes the content of a file from the pipeline, prunes each line in turn and outputs the pruned lines to the pipeline to provide flexibility.  In the attached code I have written the filter and provided an example of how it could be used.  To provide greater flexibility there are parameters to define whether you want the left or the right part of the string, what character to use as the delimiter (I have set a default to the comma but in your environment you could change it to the pipe symbol), and a switch to indicate whether or not you want to include the delimiter character.

I have included the switch parameter in the example with a value of true even though I have set the default value of true to demonstrate how a switch can be used.
filter Split-StringAtCharacter
{ 
Param
(
	[char]$CharToSplitAt = ',',
	[switch]$IncludeChar = $true,
	[ValidateSet("Right","Left")]
	[string]$Mode = "right"
)
    try
    {
    	$stringToProcess = $_
        $charPos = $stringToProcess.IndexOf($CharToSplitAt)
        $subString = ""
        switch ($mode)
        {
        	"right" 
        		{
        			if (-not $IncludeChar)
        			{
        				$charPos++
        			}
        			$subString = $stringToProcess.SubString($charPos)
        		}
        	"left" 
        		{
        			if ($IncludeChar)
        			{
        				$charPos++
        			}
        			$subString = $stringToProcess.SubString(0,$charPos)
        		}
        }
        Write-Output $subString
    }
    catch
    {
        Write-Warning "An error occurred processing: $stringToProcess `n$_"
    }
}	        

Get-Content "SampleStrings.txt" | Split-StringAtCharacter -CharToSplitAt '|' -Mode right -IncludeChar:$true | Out-File "SampleStringsOut.txt"
Get-Content "SampleStringsOut.txt"

Open in new window

0
 
LVL 8

Expert Comment

by:Brent Challis
ID: 36945865
An improved version of the code more in keeping with how code should be written for processing pipeline data is attached.  The idea of the BEGIN, PROCESS and END blocks is that the cmdlet is designed to be able to initialise data, process each item from the pipeline in turn and then clean up at the conclusion.
function Split-StringAtCharacter
{
Param
(
	[char]$CharToSplitAt = ',',
	[switch]$IncludeChar = $true,
	[ValidateSet("Right","Left")]
	[string]$Mode = "right"
)
	BEGIN 
	{
		#Initialisation code
	}
	
	PROCESS 
	{
		try
	    {
	    	$stringToProcess = $_
	        $charPos = $stringToProcess.IndexOf($CharToSplitAt)
	        $subString = ""
	        switch ($mode)
	        {
	        	"right" 
	        		{
	        			if (-not $IncludeChar)
	        			{
	        				$charPos++
	        			}
	        			$subString = $stringToProcess.SubString($charPos)
	        		}
	        	"left" 
	        		{
	        			if ($IncludeChar)
	        			{
	        				$charPos++
	        			}
	        			$subString = $stringToProcess.SubString(0,$charPos)
	        		}
	        }
	        Write-Output $subString
	    }
	    catch
	    {
	        Write-Warning "An error occurred processing: $stringToProcess `n$_"
	    }
	}
	
	END 
	{
		#Cleanup code
	}
}

Get-Content "SampleStrings.txt" | Split-StringAtCharacter -CharToSplitAt '|' -Mode left -IncludeChar:$true | Out-File "SampleStringsOut.txt"
Get-Content "SampleStringsOut.txt"

Open in new window

0
 
LVL 1

Author Comment

by:jb428j
ID: 36987507
Sorry for the late response all. Work has been in overload lately. I will do my best to see what works for my particular situation.  Again, thank you all.
0
 
LVL 1

Author Closing Comment

by:jb428j
ID: 36987714
This is exactly what I was looking for and the added comments in the script are amazing for helping me understand how it all worked. This script will go along way as I really start to understand these commands so that I may take this information incorporate it into old and new scripts.

Thank you ALL  for your help. I really do appreciate all of your time.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A brief introduction to what I consider to be the best editor for PowerShell.
A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Loops Section Overview

572 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