Solved

How do I get Powershell to look through a text file and produce output to a file?

Posted on 2014-09-11
12
232 Views
Last Modified: 2014-09-12
I have data with several companies that looks similar to this:
KLM*00*          *00*          *AA*DDDSSS         *45*4564564465     *5464564*1552*U*5465*000021*0*P*>~
AAA*00*          *00*          *00*123456789     *01*123456     *123456*1234*^*00123*000000123*0*P*>~
GS*PO*9988771231*0123456789*20140909*0123*123*Y*123456~
ST*555*00123456~
CDE*00*OP*12345**20140908~
PO1**15*CA*1.00**AD*123456456456*OP*123123~
PO4*10~
PO1**22*CA*10.05**DD*12313123213444*AB*012132~
DZA*D****TEST3~
PO1**10*CA*11.22**AA*99991132123231*AB*989889~
KLM*B****TEST2~
PO1**20*CA*12.00**KL*1231321332123*AB*756465~
EFG*A****TEST~
FGH*0*000000123~
KLM*00*          *00*          *AA*DDDSSS         *45*4564564465     *5464564*1552*U*5465*000021*0*P*>~
BBB*00*          *00*          *00*123456789     *01*123456     *123456*1234*^*00123*000000123*0*P*>~
GS*PO*11111776655*0123456789*20140909*0123*123*Y*123456~
ST*555*00123456~
CDE*00*OP*12345**20140908~
PO1**12*CA*1.00**AD*123456456456*OP*123123~
PO4*10~
PO1**15*CA*10.05**DD*12313123213444*AB*012132~
DZA*D****TEST3~
PO1**10*CA*11.22**AA*99991132123231*AB*989889~
KLM*B****TEST2~
PO1**20*CA*12.00**KL*1231321332123*AB*756465~
EFG*A****TEST~
FGH*0*000000123~
KLM*00*          *00*          *AA*DDDSSS         *45*4564564465     *5464564*1552*U*5465*000021*0*P*>~
CCC*00*          *00*          *00*123456789     *01*123456     *123456*1234*^*00123*000000123*0*P*>~
GS*PO*3333776655*0123456789*20140909*0123*123*Y*123456~
ST*555*00123456~
CDE*00*OP*12345**20140908~
PO1**11*CA*1.00**AD*123456456456*OP*123123~
PO4*10~
PO1**2*CA*10.05**DD*12313123213444*AB*012132~
DZA*D****TEST3~
PO1**10*CA*11.22**AA*99991132123231*AB*989889~
KLM*B****TEST2~
PO1**20*CA*12.00**KL*1231321332123*AB*756465~
EFG*A****TEST~
FGH*0*000000123~
KLM*00*          *00*          *AA*DDDSSS         *45*4564564465     *5464564*1552*U*5465*000021*0*P*>~
DDD*00*          *00*          *00*123456789     *01*123456     *123456*1234*^*00123*000000123*0*P*>~
GS*PO*87778778*0123456789*20140909*0123*123*Y*123456~
ST*555*00123456~
CDE*00*OP*12345**20140908~
PO1**12*CA*1.00**AD*123456456456*OP*123123~
PO4*10~
PO1**99*CA*10.05**DD*12313123213444*AB*012132~
DZA*D****TEST3~
PO1**10*CA*11.22**AA*99991132123231*AB*989889~
KLM*B****TEST2~
PO1**12*CA*12.00**KL*1231321332123*AB*756465~
EFG*A****TEST~
FGH*0*000000123~

Open in new window

I want Powershell to look through the different companies and output in 1 file the data for each of the companies as follows:

PONumber: 9988771231
Total: $the calculated total goes here

PONumber: 11111776655
Total: $the calculated total goes here

PONumber: 3333776655
Total: $the calculated total goes here

PONumber: 87778778
Total: $the calculated total goes here


All the above in one file.

The script I will begin with is this.

$InputFile = "C:\Data\original.txt"
$OutputFile = "NewData.txt"

[double]$Total = 0
($Input = Get-Content -Path $InputFile) | ? {$_ -match "PO1"} | % {$col=$_.split("*");$total += [double]$col[2] * [double]$col[4]}

$Totline = "Total: `$$Total"
write-host "This is Total `$$total"

GC $InputFile |?{$_ -match "(PO\*)(\d{0,10})"} | Out-Null

$poline = "PONumber: $($Matches[2])"

@"
$poline
$totline
"@ | out-file $OutputFile -Encoding UTF8

Open in new window

What modifications do I have to make to the script to display the appropriate information for all the companies it finds?
0
Comment
Question by:100questions
[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
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 29

Expert Comment

by:becraig
ID: 40317962
Is the expectation to calculate the total between:
GS*PO*xxx - [line output 1]
.......    [This would be the total for the po on the line above]
GS*PO*xxx
.......    [This would be the total for the po on the line above]
GS*PO*xxx - [line output 2]
etc

Is that what you are looking for ?
0
 
LVL 40

Accepted Solution

by:
Subsun earned 500 total points
ID: 40318045
Try this and see if it works as expected..
$InputFile = "C:\Data\original.txt"
$OutputFile = "NewData.txt"

Function ParseText ($OutputFile,$InputFile){
Begin{
$poline,$totline,$total = $NULL,$NULL,$NULL
Set-Content $OutputFile $null
}
Process{
	Get-Content -Path $InputFile | % {

If ($_ -match "FGH\*0"){
	If ($poline -ne $null -and $total -ne $null){
	$Totline = "Total: `$$Total"
	write-host "This is Total `$$total"
		"`n$poline `r`n$totline" | out-file $OutputFile -Encoding UTF8 -Append
		$poline,$totline,$total = $NULL,$NULL,$NULL
	}
}
   If($_ -match "(PO\*)(\d{0,10})") {
	$poline = "PONumber: $($Matches[2])"
    }Elseif ($_ -match "PO1"){
        $col=$_.split("*") ; $total += [double]$col[2] * [double]$col[4]
  }
 }
}
}

ParseText $OutputFile $InputFile

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40318223
If you applied this regular expression pattern to the text:
(?:GS\*PO\*(\d+))|(?:PO1.*?CA\*(\d+\.\d\d))

Open in new window


Then you could iterate the matches, gathering the data you want.  If the first submatch is not empty, then it is the PO Number.  If the second submatch is not empty, it is a dollar amount.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:100questions
ID: 40319193
@aikimark - do you mean to add the text to the first script I have in my entry?
0
 

Author Comment

by:100questions
ID: 40319210
@Subbun.  Thanks for the script, I made some small modifications however it only seems to output data for 2 out of the 4 companies in another file I have.  Why is it not picking up all the companies?
Also, I changed the script to look at the line that starts with KLM as the marker since that's where the data for each company starts.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40319225
I was thinking about parsing the entire file with the regex pattern.  It is a different approach than the one already suggested.

What does your PS script look like now?
0
 
LVL 40

Expert Comment

by:Subsun
ID: 40319324
What if you try the code which I suggested? I mean with out any modifications.
0
 

Author Comment

by:100questions
ID: 40319412
@Subsun.  FGH in the data I provided is fictitious.

If I try your file, and I insert the actual 3 letters of in the actual file, nothing happens, not even a blank file is created.
Then when I add the correct full path to the ouput, a blank file is created.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 40319435
You need to replace the  FGH pattern with the pattern where the data ends for each company. The code was working with the input data which you have posted in your question.  Im travelling, so I will not be able to test now.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40319469
@100questions

When you post data, it should be an accurate representation of your production data.  If your representative sample deviates too much from your actual data, the experts will post solutions that you will have to tweak in order to solve your actual problem.  These question threads are our only means of solving problems at EE and none of the experts is psychic, so the accuracy of your data examples is very important.
0
 

Author Closing Comment

by:100questions
ID: 40319618
Seems to work, however I had to change the output file to an actual path of where the start path was, which I also changed.
0

Featured Post

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

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…
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

695 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