Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-10-07
14
Medium Priority
?
128 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 11

Expert Comment

by:Joe Klimis
ID: 40367990
Hi , could you post examples of you expected output based on you testdata ? this would help alot
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 11

Expert Comment

by:Joe Klimis
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 11

Expert Comment

by:Joe Klimis
ID: 40368545
do you want the order number  , city , state to appear at the begining of each line of data ?
0
 
LVL 11

Expert Comment

by:Joe Klimis
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 11

Expert Comment

by:Joe Klimis
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 11

Accepted Solution

by:
Joe Klimis earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
Loops Section Overview
Screencast - Getting to Know the Pipeline

886 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