Solved

How do I get a Powershell script to produce the desired output?

Posted on 2014-10-07
14
111 Views
Last Modified: 2014-10-15
Sample Data to work with:

GS*SW*AAAAAAA*5555555555*20140929*0999*999*X*009999~
W06*F*0077885*20140926*ORD-000999999**09999999~
N4*CITY*ST*12345~
N9*ZZ*1234567891~
LX*1~
W12*CC*10*10*0*CA**VN*12345~
N9*SE*12345678912345678921~
N9*WG*200~
N9*PK*10~
N9*DS*DESC 1~
LX*2~
W12*CC*20*20*0*CA**VN*12345~
N9*SE*78945612378945612312~
N9*WG*200~
N9*PK*10~
N9*DS*DESC 2~
LX*3~
W12*CC*10*10*0*CA**VN*12345~
N9*SE*45678912345678912345~
N9*WG*200~
N9*PK*10~
N9*DS*DESC 3~
LX*4~
W12*CC*10*10*0*CA**VN*12345~
N9*SE*15478965325658745875~
N9*WG*200~
N9*PK*10~
N9*DS*DESC 4~
LX*5~
W12*CC*7*7*0*CA**VN*12345~
N9*SE*96325874125874545875~
N9*WG*200~
N9*PK*10~
N9*DS*DESC 5~
LX*6~
W12*CC*70*70*0*CA**VN*12345~
N9*SE*85214785693265985874~
N9*WG*200~
N9*PK*10~
N9*DS*DESC 6~


Please note that each data set starts with GS.

The script needs to look through a text file and notice where each GS is found.  The next GS which is found would indicate the beginning of a new data set.

As it looks through the data it needs to produce the following output in a separate text file.

Order No:  (This is obtained from the W06 line.  Above the PO is 0077885)
City:  (This is obtained from the first N4 segment.   Above it is CITY.),
State:   (This is obtained from the first N4 segment.   Above it is ST.)

For each W12 and the N9 which follows, it needs to show the following

Qty: 10, Item #: 12345, SSC: 12345678912345678921

To summarize, for each data set, it needs to show the following

Order No.:
City:      ,   State:            
Qty:   , Item #:           , SSC:
Qty:   , Item #:           , SSC:
Qty:   , Item #:           , SSC:  and so forth

I would like a Powershell script which does this.
0
Comment
Question by:100questions
  • 7
  • 6
14 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
Comment Utility
I will be looking at this issue later tonight.
0
 

Author Comment

by:100questions
Comment Utility
Thank you.
0
 
LVL 10

Expert Comment

by:JoeKlimis
Comment Utility
Hi , could you post examples of you expected output based on you testdata ? this would help alot
0
 

Author Comment

by:100questions
Comment Utility
To all, please note that the QTY should be pulling from the amount shown in the second instance.....in the W12 line....
Ie.  W12*CC*10*10*0*CA**VN*12345~
In this line it's the 2nd 10 not the first 10.
0
 

Author Comment

by:100questions
Comment Utility
Sample type data:


 Order No.: 0077885
 City:  City shown here    ,   State:   State shown here          
 Qty: 10, Item #:  12345, SSC: 1234567489456465464
 Qty: 25, Item #:  12345, SSC: 1234567891234567897
 Qty: 30, Item #:  12345, SSC: 1234567891234567897

  Order No.: 0077886
 City:  City shown here    ,   State:   State shown here          
 Qty: 10, Item #:  12245, SSC: 1334567489456465464
 Qty: 25, Item #:  12245, SSC: 1334567891234567897
 Qty: 30, Item #:  12245, SSC: 1334567891234567897
0
 
LVL 10

Expert Comment

by:JoeKlimis
Comment Utility
Hi

give this a go  you may need to change the $dataFile variable to point to you data file , and the results will be saved into $resultsFile variable location.

Let me know how you  get on .

Regards
Joe



$dataFile		= "data.txt"
$resultsFile	= "Results.txt"
$data 		 	= get-content $dataFile
$result			= ""
write-output "Processing $($data.count) lines of data"

foreach ($line in $data)
{
	$SplitLine =$line.split("~")[0].split("*")
	switch ( $splitLine[0] )
	{
	"GS" 	{
			$orderNo = ""
			$city=""
			$state=""
			$City=""
			$item = ""
			$Quantity=""
			$scc=""
			}
	"W06" 	{ 
				$OrderNo = $SplitLine[2]}
	"N4" 	{ 
				$City = $SplitLine[1]
				$state = $SplitLine[2]
				$result = $result + "`n`nOrder No.: $($Orderno)`nCity:  $City   ,   State:   $state "
	
			}
	"W12" 	{ 	
				$item = $SplitLine[8]
				$Quantity = $SplitLine[3] 
			}
	"N9" 	{ 
				if ($SplitLine[1] -eq "SE") 
				{ 	$SCC = $SplitLine[2].Substring(0,20)
					$result = $result +"`nQty: $Quantity, Item #:  $Item, SSC: $SCC" 
				}
			}
	}
}
$result | out-file $resultsFile

Open in new window

0
 

Author Comment

by:100questions
Comment Utility
Thanks Joe.  This is a great start.  I added the path to where my data is and where the results should be.
It ran however the output is all on one line, it does not separate the data as per my sample output above.

It looks something like this:

Order No.: 0099999City:  City   ,   State:   ST Qty: 90, Item #:  01234, SSC: 12345678945612345456 .....etc...

Can the output be changed to be similar to the output I have above?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Expert Comment

by:JoeKlimis
Comment Utility
do you want the order number  , city , state to appear at the begining of each line of data ?
0
 
LVL 10

Expert Comment

by:JoeKlimis
Comment Utility
so the output looks like  like the following ? if not please put you example in a  codebox,  so I can see exactly what you require.

Regards
Joe
 Order No.: 0077885 City:  CITY   ,   State:   ST Qty: 10, Item #:  12345, SSC: 12345678912345678921
 Order No.: 0077885 City:  CITY   ,   State:   ST Qty: 20, Item #:  12345, SSC: 78945612378945612312
 Order No.: 0077885 City:  CITY   ,   State:   ST Qty: 10, Item #:  12345, SSC: 45678912345678912345
 Order No.: 0077885 City:  CITY   ,   State:   ST Qty: 10, Item #:  12345, SSC: 15478965325658745875

Open in new window

0
 

Author Comment

by:100questions
Comment Utility
Joe, the data should look like this, for instance if there was more than one PO to be listed:

Order No.: 0077885
City:  CITY   ,   State:   ST
Qty: 10, Item #:  12345, SSC: 12345678912345678921
Qty: 20, Item #:  12345, SSC: 78945612378945612312
Qty: 10, Item #:  12345, SSC: 45678912345678912345
Qty: 10, Item #:  12345, SSC: 15478965325658745875

Order No.: 0091234
City:  CITY   ,   State:   ST
Qty: 10, Item #:  12345, SSC: 12345678912345678921
Qty: 20, Item #:  12345, SSC: 78945612378945612312
Qty: 10, Item #:  12345, SSC: 45678912345678912345
Qty: 10, Item #:  12345, SSC: 15478965325658745875


Order No.: 0077885
City:  CITY   ,   State:   ST
Qty: 10, Item #:  12345, SSC: 12345678912345678921
Qty: 20, Item #:  12345, SSC: 78945612378945612312
Qty: 10, Item #:  12345, SSC: 45678912345678912345
Qty: 10, Item #:  12345, SSC: 15478965325658745875

Order No.: 0091234
City:  CITY   ,   State:   ST 
Qty: 10, Item #:  12345, SSC: 12345678912345678921
Qty: 20, Item #:  12345, SSC: 78945612378945612312
Qty: 10, Item #:  12345, SSC: 45678912345678912345
Qty: 10, Item #:  12345, SSC: 15478965325658745875

Open in new window

0
 
LVL 10

Expert Comment

by:JoeKlimis
Comment Utility
I think the following will produce the required formatted output.

Please try and let me know.
$dataFile		= "data.txt"
$resultsFile	= "Results.txt"
$data 		 	= get-content $dataFile
$result			= ""
write-output "Processing $($data.count) lines of data"
$count  = 0
foreach ($line in $data)
{
	$SplitLine =$line.split("~")[0].split("*")
	switch ( $splitLine[0] )
	{
	"GS" 	{

			$orderNo = ""
			$city=""
			$state=""
			$City=""
			$item = ""
			$Quantity=""
			$scc=""

			}
	"W06" 	{ 	$count++
				$OrderNo = $SplitLine[2]}
	"N4" 	{ 
				$City = $SplitLine[1]
				$state = $SplitLine[2]
				if ($count -gt 1) {$result = $result + "`n`n"}
				$result = $result + "Order No.: $($Orderno)`nCity:  $City   ,   State:   $state "
			
			}
	"W12" 	{ 	
				$item = $SplitLine[8]
				$Quantity = $SplitLine[3] 
			}
	"N9" 	{ 
				if ($SplitLine[1] -eq "SE") 
				{ 	$SCC = $SplitLine[2].Substring(0,20)
					$result = $result +"`nQty: $Quantity, Item #:  $Item, SSC: $SCC" 
				}
			}
	}
}
$result | out-file $resultsFile -encoding ascii

Open in new window

0
 

Author Comment

by:100questions
Comment Utility
Thanks Joe, however this still produces output which is all on one line.
It does not separate the data line by line..
0
 
LVL 10

Accepted Solution

by:
JoeKlimis earned 500 total points
Comment Utility
Hi Sorry Please try the following

$dataFile		= "data.txt"
$resultsFile	= "Results.txt"
out-file $resultsFile  -encoding ascii
$data 		 	= get-content $dataFile
$result			= ""
write-output "Processing $($data.count) lines of data"
$count  = 0
foreach ($line in $data)
{
	$SplitLine =$line.split("~")[0].split("*")
	switch ( $splitLine[0] )
	{
	"GS" 	{

			$orderNo = ""
			$city=""
			$state=""
			$City=""
			$item = ""
			$Quantity=""
			$scc=""

			}
	"W06" 	{ 	$count++
				$OrderNo = $SplitLine[2]}
	"N4" 	{ 
				$City = $SplitLine[1]
				$state = $SplitLine[2]
				if ($count -gt 1) {" " | out-file $resultsFile -append -encoding ASCII  }
				"Order No.: $($Orderno)" | out-file $resultsFile -append -encoding ASCII  
				"City:  $City   ,   State:   $state " | out-file -filepath $resultsFile -append -encoding ASCII 
			
			}
	"W12" 	{ 	
				$item = $SplitLine[8]
				$Quantity = $SplitLine[3] 
			}
	"N9" 	{ 
				if ($SplitLine[1] -eq "SE") 
				{ 	$SCC = $SplitLine[2].Substring(0,20)
					"Qty: $Quantity, Item #:  $Item, SSC: $SCC" | out-file -filepath $resultsFile -append -encoding ASCII 
				}
			}
	}
}

Open in new window

0
 

Author Closing Comment

by:100questions
Comment Utility
Works well, thank you.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article shows how a content item can be identified directly or through translation of a navigation type. It then shows how this information can be used to create a menu for further navigation.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now