Link to home
Start Free TrialLog in
Avatar of E=mc2
E=mc2Flag for Canada

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*999999999*9*Z*:~
 GS*PO*9999999999*9999999999*20150101*99999999*9999*A*999999ABCD~
 ST*850*9999~
 BEG*00*SA*9999999999**20150101~
 CUR*AB*ABC~
 REF*AB*9999-123~
 PER*AB*ADDRESS*AB*999-999-9999~
 FOB*AB*AB~
 DTM*002*20150102~
 N1*AB*COMPANY*A*1234567891234~
 N3*ADDRESS~
 N4*CITY*99*99999*AB~
 N1*AB*COMPANY*0*1234561234567~
 N3*ADDRESS~
 N4*CITY*AB*99999*AB~
 N1*ST*NAME*0*12345612345621~
 N3*ADD~
 N4*ADD*AB*99999*AB~
 PO1**10*AB***AB*12345678912345*CD*999999~
 CTP**ABC*10.10~
 PID*A*99***DESC1~
 PO4*01~
 SAC*A*E123***99999*1*0*1.70*AB*9**99~
 PO1**100*AB***AB*12345612345654*CD*999999~
 CTP**DEF*10.15~
 PID*A*99***DESC2~
 PO4*01~
 SAC*A*E123***99999*1*0*1.50*AB*9**99~
 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
}

Open in new window



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
Avatar of Bill Prew
Bill Prew

I'm offline for the next 3 days, but if this is still open will try to post a solution early next week...

~bp
Avatar of E=mc2

ASKER

Thanks Bill, much appreciated.
Avatar of E=mc2

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 ?
Avatar of E=mc2

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.
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@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
Avatar of E=mc2

ASKER

Thanks.
Avatar of E=mc2

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.
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...
Avatar of E=mc2

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.
Avatar of E=mc2

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.
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.