E=mc2
asked on
Would it possible to convert this PS script to VBScript and then modify the VBScript for the desired output?
Would it be possible to convert this PS Script to VBScript and then modify the VBScript for the desired output?
Input file:
ISA*00* *00* *AA*COMPANY *99*9999999999 *999999*9999*A*99999*99999 9999*9*Z*: ~
GS*PO*9999999999*999999999 9*20150101 *99999999* 9999*A*999 999ABCD~
ST*850*9999~
BEG*00*SA*9999999999**2015 0101~
CUR*AB*ABC~
REF*AB*9999-123~
PER*AB*ADDRESS*AB*999-999- 9999~
FOB*AB*AB~
DTM*002*20150102~
N1*AB*COMPANY*A*1234567891 234~
N3*ADDRESS~
N4*CITY*99*99999*AB~
N1*AB*COMPANY*0*1234561234 567~
N3*ADDRESS~
N4*CITY*AB*99999*AB~
N1*ST*NAME*0*1234561234562 1~
N3*ADD~
N4*ADD*AB*99999*AB~
PO1**10*AB***AB*1234567891 2345*CD*99 9999~
CTP**ABC*10.10~
PID*A*99***DESC1~
PO4*01~
SAC*A*E123***99999*1*0*1.7 0*AB*9**99 ~
PO1**100*AB***AB*123456123 45654*CD*9 99999~
CTP**DEF*10.15~
PID*A*99***DESC2~
PO4*01~
SAC*A*E123***99999*1*0*1.5 0*AB*9**99 ~
CTT*0**10*0*AB*1.234*AB~
SE*99*9999~
GE*9*9999~
IEA*9*999999999~
Desired output:
Desired Output file: (NB- The change is to add the discount in the SAC segment, and to calculate the new prices based on the discount)
Company PONumber: 9999999999
Ship to: ADD, ADD, AB, 99999, AB
Item 1: UPC: 12345678912345, Desc: DESC1, Qty: 10, Price $10.10, Disc: $1.70, Net: $8.40
Item 2: UPC: 12345612345654, Desc: DESC2, Qty: 100, Price $10.15, Disc: $1.50, Net: $8.65
Total: $949.00
Input file:
ISA*00* *00* *AA*COMPANY *99*9999999999 *999999*9999*A*99999*99999
GS*PO*9999999999*999999999
ST*850*9999~
BEG*00*SA*9999999999**2015
CUR*AB*ABC~
REF*AB*9999-123~
PER*AB*ADDRESS*AB*999-999-
FOB*AB*AB~
DTM*002*20150102~
N1*AB*COMPANY*A*1234567891
N3*ADDRESS~
N4*CITY*99*99999*AB~
N1*AB*COMPANY*0*1234561234
N3*ADDRESS~
N4*CITY*AB*99999*AB~
N1*ST*NAME*0*1234561234562
N3*ADD~
N4*ADD*AB*99999*AB~
PO1**10*AB***AB*1234567891
CTP**ABC*10.10~
PID*A*99***DESC1~
PO4*01~
SAC*A*E123***99999*1*0*1.7
PO1**100*AB***AB*123456123
CTP**DEF*10.15~
PID*A*99***DESC2~
PO4*01~
SAC*A*E123***99999*1*0*1.5
CTT*0**10*0*AB*1.234*AB~
SE*99*9999~
GE*9*9999~
IEA*9*999999999~
$InputFile = "C:\Input.txt"
$OutputFile = "C:\NewFile.txt"
Function ParseText ($OutputFile,$InputFile){
Begin{
$line,$poline,$shipto,$totline,$total,$i,$n3 = $null,$null,$null,$null,$null,0,0
Set-Content $OutputFile $null
$Data = Get-Content -Path $InputFile | ?{$_ -match "(BEG\*)|(PO1\*)|(CTP\*)|(PID\*)|(N\d\*)|(IEA\*)"}
}
Process{
$Data | % {
#Check the end of data set
If ($_ -match "IEA\*"){
If($poline -ne $null -and $total -ne $null){
$Totline = "Total: `$$Total"
Write-host "This is Total `$$total"
#Write the data collection to output file
"$poline$shipto$line`r`n$totline`r`n" | out-file $OutputFile -Encoding UTF8 -Append
#reset the variables
$line,$poline,$shipto,$totline,$total,$i,$n3 = $null,$null,$null,$null,$null,0,0
}
}
#Collect the PO Number
If($_ -match "(SA\*)(\d{3,10})"){
$poline = "Company: $($Matches[2])"
}
#Collect the Ship To information
If($_ -match "(N3\*)([^*~]+)"){
$n3++
If($n3 -eq 3){
$shipto = "`r`nShip to: $($Matches[2])"
}
}
If($n3 -eq 3 -and $shipto -ne $null -and $_ -match "(N4\*)([^~]+)"){
$n4 = ($Matches[2]).split("*") -join ", "
$shipto = "$shipto, $n4"
}
#Collect G80 details and following G81 Description
If($_ -match "(PO1\*{1,})(\d{1,})\*\w{2}\*{1,}\w{2}\*(\d{1,})"){
$count = $Matches[2]
$UPC = $Matches[3]
}
If($_ -match "(CTP\*{1,}).*\*(\d{1,}(\.\d{1,})?)"){
$Price = $Matches[2]
$total += [double]$count * [double]$Price
}
If($_ -match "PID\*"){
$i++
$Desc = $_ -Replace "PID\*\w\*{1,}(\d+\*{1,})?|<NL>|~"
[String[]]$line += "`r`nItem $i`: UPC: $UPC, Desc: $Desc, Qty: $count Price `$$Price"
}
}
}
}
If(Test-Path $InputFile){
ParseText $OutputFile $InputFile
}
Desired output:
Desired Output file: (NB- The change is to add the discount in the SAC segment, and to calculate the new prices based on the discount)
Company PONumber: 9999999999
Ship to: ADD, ADD, AB, 99999, AB
Item 1: UPC: 12345678912345, Desc: DESC1, Qty: 10, Price $10.10, Disc: $1.70, Net: $8.40
Item 2: UPC: 12345612345654, Desc: DESC2, Qty: 100, Price $10.15, Disc: $1.50, Net: $8.65
Total: $949.00
ASKER
Thanks Bill, much appreciated.
ASKER
In addition to the above, I would also like to hard code a Title into this. Thank you.
1. what line does the title come from ?
2. is there always 3 address fields, the last is the mailing address ?
2. is there always 3 address fields, the last is the mailing address ?
ASKER
Thank for your response.
The title line is something I could hard code into the script, so that I can name it according to the need.
So an area in the script where I could hard code the title would be great.
Also, the N4 is the last address field, namely N1, N3, N4. Hope this helps.
The title line is something I could hard code into the script, so that I can name it according to the need.
So an area in the script where I could hard code the title would be great.
Also, the N4 is the last address field, namely N1, N3, N4. Hope this helps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@100questions,
Just wanted to update you that I have decided to step away from these EDI related posts you need help with. It seems to be a somewhat moving target, or varying requirements, and typically I approach EE more as a place for me to help people solve problems and answer questions, rather than as a consulting service. That's not a complaint to you, but I just wanted to share my perspective so you didn't think I just abandoned you...
You have some good help on these questions, I hope you get the assistance you need to create the solutions.
~bp
Just wanted to update you that I have decided to step away from these EDI related posts you need help with. It seems to be a somewhat moving target, or varying requirements, and typically I approach EE more as a place for me to help people solve problems and answer questions, rather than as a consulting service. That's not a complaint to you, but I just wanted to share my perspective so you didn't think I just abandoned you...
You have some good help on these questions, I hope you get the assistance you need to create the solutions.
~bp
ASKER
Thanks.
ASKER
@robberbaron - I am now getting an error on line 93 of the script, Character 7, it says Invalid procedure call or argument.
Is there something I should be tweaking in the script?
Thanks in advance.
Is there something I should be tweaking in the script?
Thanks in advance.
that suggests your source does not match the sample. that line of code is trying to find the shipto address from the N3 line. And cant find it.
you will have to post the particular file that is causing the problem...
you will have to post the particular file that is causing the problem...
ASKER
Ok, perhaps I'll open another ticket then.
I used simlar input data but of course different specific data.
I'll take a look at the N3 to see what's going on.. and I'll report back.
Thank you.
I used simlar input data but of course different specific data.
I'll take a look at the N3 to see what's going on.. and I'll report back.
Thank you.
ASKER
@robberbaron - I believe I found the discrepancy in my data.
I noticed that in my data there were 2 DTM segments as opposed to 1.
Sample similar to below.
DTM*001*20110522~
DTM*002*20110523~
If I delete one of the DTM segments it works fine.
If I keep the other one as well, it produces an error.
I now realize the script needs to be able to handle both scenarios.
Should I open another ticket?
Thanks.
I noticed that in my data there were 2 DTM segments as opposed to 1.
Sample similar to below.
DTM*001*20110522~
DTM*002*20110523~
If I delete one of the DTM segments it works fine.
If I keep the other one as well, it produces an error.
I now realize the script needs to be able to handle both scenarios.
Should I open another ticket?
Thanks.
yes, thats the proper way. link to this q.
include detail on the possible scenarios and info on data format.
It appears your data is to the Purchase Order Transaction Set (850) specification but different implementation have different criteria.. Some appear to allow only 1 DTM segment, others up to 10.
include detail on the possible scenarios and info on data format.
It appears your data is to the Purchase Order Transaction Set (850) specification but different implementation have different criteria.. Some appear to allow only 1 DTM segment, others up to 10.
~bp