[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Powershell program to read file and do substitutions

Posted on 2013-05-20
13
Medium Priority
?
371 Views
Last Modified: 2013-05-21
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
0
Comment
Question by:carlmd
[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
  • 5
  • 4
  • 4
13 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39181507
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
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39181677
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.
0
 
LVL 20

Author Comment

by:carlmd
ID: 39181679
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.
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 42

Expert Comment

by:Meir Rivkin
ID: 39181697
I do output the whole line check line 20.
Also ive attached the output txt file.
0
 
LVL 20

Author Comment

by:carlmd
ID: 39181719
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....
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39181743
U right my bad. Fixing...
0
 
LVL 71

Assisted Solution

by:Qlemo
Qlemo earned 400 total points
ID: 39181744
It should be vice versa: All substitutions are wrong, only outputting the replaced field instead of the complete line. This should work:
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) {
      $tokens[166} += $map[$tokens[190]]
      $tokens -join '|'
    } else {
      $_
    }
  } | Out-File $outputfile
}

createMap
parseFile

Open in new window

0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39182036
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

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39182159
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.
0
 
LVL 20

Author Comment

by:carlmd
ID: 39183949
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
0
 
LVL 42

Accepted Solution

by:
Meir Rivkin earned 1600 total points
ID: 39183972
cls
$inputfile = Read-Host 'Enter Input file name:'
$outputfile = $inputfile.Replace(".", ".out.");
if([IO.File]::Exists($inputfile) -ne $true) {
	write-output "$inputfile could not be found"
	return
}

$map = @{}
ri $outputfile -ErrorAction SilentlyContinue
function createMap{
	gc c:\temp\map.txt |%{
		$tokens = $_ -split ' '
		$map[$tokens[0]] = $tokens[1]
	}
}

function parseFile{
	gc $inputfile |%{
		$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

0
 
LVL 20

Author Closing Comment

by:carlmd
ID: 39184857
Works like a champ!
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39185839
Please note http:#a39182159. The replacement is not safe, a different column might get changed.
0

Featured Post

Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
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…
Suggested Courses

650 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