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

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.
100questionsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Darrell PorterEnterprise Business Process ArchitectCommented:
I will be looking at this issue later tonight.
0
100questionsAuthor Commented:
Thank you.
0
Joe KlimisCommented:
Hi , could you post examples of you expected output based on you testdata ? this would help alot
0
ON-DEMAND: 10 Easy Ways to Lose a Password

Learn about the methods that hackers use to lift real, working credentials from even the most security-savvy employees in this on-demand webinar. We cover the importance of multi-factor authentication and how these solutions can better protect your business!

100questionsAuthor Commented:
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
100questionsAuthor Commented:
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
Joe KlimisCommented:
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
100questionsAuthor Commented:
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
Joe KlimisCommented:
do you want the order number  , city , state to appear at the begining of each line of data ?
0
Joe KlimisCommented:
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
100questionsAuthor Commented:
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
Joe KlimisCommented:
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
100questionsAuthor Commented:
Thanks Joe, however this still produces output which is all on one line.
It does not separate the data line by line..
0
Joe KlimisCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
100questionsAuthor Commented:
Works well, thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.