Solved

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

Posted on 2014-10-07
14
114 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
 

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

9 Experts available now in Live!

Get 1:1 Help Now