Solved

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

Posted on 2014-10-07
14
116 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
ID: 40367453
I will be looking at this issue later tonight.
0
 

Author Comment

by:100questions
ID: 40367506
Thank you.
0
 
LVL 10

Expert Comment

by:JoeKlimis
ID: 40367990
Hi , could you post examples of you expected output based on you testdata ? this would help alot
0
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 

Author Comment

by:100questions
ID: 40368051
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
ID: 40368218
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
ID: 40368406
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
ID: 40368438
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
 
LVL 10

Expert Comment

by:JoeKlimis
ID: 40368545
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
ID: 40368557
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
ID: 40370672
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
ID: 40371016
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
ID: 40371163
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
ID: 40371838
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
ID: 40382265
Works well, thank you.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Synchronize a new Active Directory domain with an existing Office 365 tenant
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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