Link to home
Create AccountLog in
Avatar of Carl Dula
Carl DulaFlag for United States of America

asked on

Powershell program to read file and do substitutions

I am looking for a powershell program that will prompt for the name and open a .txt file. The .txt file has approximately 250 vertical bar character (|) delimited fields on each line. The number of lines varies. A sample of this file is attached as sample.txt.

The program needs to examine each line and look for the string 11300 in field 167 (as counted by the vertical bar characters). If field 167 does not equal 11300 write the unchanged line to an output file (inputfilename.out.txt) and go to the next line. If field 167 does contain 11300,then look in field 191 for a two character string. Using that two character string, look up in a table (can be a separate file or an array in the program, your choice), to obtain two digits to append to the string in field 167 (a sample of the table/array is attached as substitution.pdf) and write the line with that change to the output file. Continue with each line until the end of file is reached.

For example, if 11300 is found in field 167, and field 191 has AT in it, then field 167 should be written out as 1130010.

thanks....
sample.txt
substitution.pdf
Avatar of Meir Rivkin
Meir Rivkin
Flag of Israel image

i used map.txt which has the mapping from your pdf file.
the output of the script is also attached.
cls
$map = @{}
$outputfile = "c:\temp\inputfilename.out.txt"
ri $outputfile 
function createMap{
	gc c:\temp\map.txt |%{
		$tokens = $_ -split ' '
		$map[$tokens[0]] = $tokens[1]
	}
}

function parseFile{
	gc c:\temp\sample.txt |%{
		$tokens = $_.Split('|')
		if($tokens[166] -eq 11300){
			$code = $tokens[190]
			$digits = $map[$code]
			"11300{0}" -f $digits | Out-File $outputfile -Append
		}else{
			$tokens -join "|" | Out-File $outputfile -Append
		}
	}
}

createMap
parseFile

Open in new window

map.txt
inputfilename.out.txt
One improvement: Line 20 shouldn't rejoin the line, instead we still have the original line in $_:
			$_ | Out-File $outputfile -Append

Open in new window

I would also remove both
   | Out-File $outputfile -Append
and instead write the result in one go in line 22:
	}  | Out-File $outputfile

Open in new window

which saves a lot of file operations if the text file contains a lot of lines.
Avatar of Carl Dula

ASKER

Looks close...

"If field 167 does not equal 11300 write the unchanged line to an output file (inputfilename.out.txt) and go to the next line"

The intent was that if 11300 was not found, the input line is simply written to the output file unchanged. You appear to be writing out only that one field and not the entire input line.
I do output the whole line check line 20.
Also ive attached the output txt file.
The output.txt.file that is attached has 8 lines, the two with the substitution are correct, but five only have the one field on them. Maybe you attached the wrong file? Take a look at the attachment....
U right my bad. Fixing...
SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
cls
$map = @{}
$outputfile = "c:\temp\inputfilename.out.txt"
ri $outputfile -ErrorAction SilentlyContinue
function createMap{
	gc c:\temp\map.txt |%{
		$tokens = $_ -split ' '
		$map[$tokens[0]] = $tokens[1]
	}
}

function parseFile{
	gc c:\temp\sample.txt |%{
		$line = $_
		$tokens = $line.Split('|')
		if($tokens[166] -eq 11300){
			$line = $line.Replace("11300", ("11300{0}" -f $map[$tokens[190]]))
		}
		$line | Out-File $outputfile -Append
	}
}

createMap
parseFile

Open in new window

sedgwick,

To make it more safe I would use
			$line = $line.Replace("|11300|", ("|11300{0}|" -f $map[$tokens[190]]))

Open in new window

but that still might replace a different column.
Ok, I verified that the script works but I need it to prompt for filename of input file and use that to create output file name.

For example:

Input file name:   july.txt

then create the output file as july.out.txt
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Works like a champ!
Please note http:#a39182159. The replacement is not safe, a different column might get changed.