Solved

EDI Inbound 850 pull information into a text file.

Posted on 2011-02-10
29
590 Views
Last Modified: 2012-08-13
I have edi inbound document that I want to pull information out of the file only when i see ESD in the PID file.
Example:

PID*F****ESD TREND MICRO A/V 2011 3-PC

The information I need to pull out to put in a flat file


BEG*00*NE*00847004402726**20110201
DTM*010*20110201
PER*DC**EM*john.welsch@boeing.com
PO1*1*1*EA*50**IN*556639*VN*8064101



ISA*00*          *00*          *ZZ*COSTCOECOMM2   *14*102276177NAV   *110201*0831*U*00401*000049501*0*P*>
GS*PO*COSTCOECOMM2*102276177NAV*20110201*0831*49501*X*004010
ST*850*495010001
BEG*00*NE*00847004402726**20110201
REF*VR*0678500
REF*OP*217564016
DTM*010*20110201
TD5**2*UPSM*M********CG
N9*H5*LEGAL REQUIREMENTS
MSG*This order incorporates the costco.com E-standard Terms, as they may
MSG*be amended from time to time, and any applicable signed agreements
MSG*between us.
N1*ST*John Welsch*93*00011
N3*567 TEA HOUSE LN
N4*MOUNT PLEASANT*SC*294647802
PER*DC**TE*5622347790
PER*DC**EM*john.welsch@boeing.com
N1*OB*John Welsch
N3*567 TEA HOUSE LN
N4*MOUNT PLEASANT*SC*294647802*US
PO1*1*1*EA*50**IN*556639*VN*8064101
PID*F****TREND MICRO MAX SECURITY
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC
IT8*SC
CTT*1*1
SE*25*495010001
IEA*1*000049501
0
Comment
Question by:Navarre_EDI
  • 17
  • 12
29 Comments
 
LVL 65

Expert Comment

by:RobSampson
ID: 34867043
Hi there, try something like this.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True   

strEDI = "myfile.txt"
strTrigger = "PID.*ESD"
strMessage = ""
blnTriggerFound = False
arrDataToRetrieve = Array("BEG.*", "DTM.*", "PER.*", "PO1.*")
Const intForReading = 1
Set objEDI = objFSO.OpenTextFile(strEDI, intForReading, False)
While Not objEDI.AtEndOfStream
	strLine = objEDI.ReadLine
	objRegEx.Pattern = strTrigger
	Set colMatches = objRegEx.Execute(strLine)
	If colMatches.Count > 0 Then
		blnTriggerFound = True
		For Each strMatch In colMatches   
			If strMessage = "" Then
				strMessage = strLine
			Else
				strMessage = strMessage & VbCrLf & strLine
			End If
		Next
	End If
	For Each strData In arrDataToRetrieve
		objRegEx.Pattern = strData
		Set colMatches = objRegEx.Execute(strLine)
		If colMatches.Count > 0 Then
			For Each strMatch In colMatches   
				If strMessage = "" Then
					strMessage = strLine
				Else
					strMessage = strMessage & VbCrLf & strLine
				End If
			Next
		End If		
	Next
Wend
objEDI.Close

If blnTriggerFound = True Then
	MsgBox strMessage
Else
	MsgBox "Not found"
End If

Open in new window

0
 

Author Comment

by:Navarre_EDI
ID: 34875727
Hello

The script works good  but only writes to msgbox instead of a file. Can I get help writing this to a file
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34876335
Sure, sorry I forgot that bit.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True   

strEDI = "myfile.txt"
strOutputFile = "output.txt"
strTrigger = "PID.*ESD"
arrDataToRetrieve = Array("BEG.*", "DTM.*", "PER.*", "PO1.*")

strMessage = ""
blnTriggerFound = False
Const intForReading = 1
Set objEDI = objFSO.OpenTextFile(strEDI, intForReading, False)
While Not objEDI.AtEndOfStream
	strLine = objEDI.ReadLine
	objRegEx.Pattern = strTrigger
	Set colMatches = objRegEx.Execute(strLine)
	If colMatches.Count > 0 Then
		blnTriggerFound = True
		For Each strMatch In colMatches   
			If strMessage = "" Then
				strMessage = strLine
			Else
				strMessage = strMessage & VbCrLf & strLine
			End If
		Next
	End If
	For Each strData In arrDataToRetrieve
		objRegEx.Pattern = strData
		Set colMatches = objRegEx.Execute(strLine)
		If colMatches.Count > 0 Then
			For Each strMatch In colMatches   
				If strMessage = "" Then
					strMessage = strLine
				Else
					strMessage = strMessage & VbCrLf & strLine
				End If
			Next
		End If		
	Next
Wend
objEDI.Close

If blnTriggerFound = True Then
	Set objOutput = objFSO.CreateTextFile(strOutput, True)
	objOutput.Write strMessage
	objOutput.Close
	MsgBox "Data has been output to " & strOutput
Else
	MsgBox "Not found"
End If

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Navarre_EDI
ID: 34891112
Hello

When I am try to write it is give an invalid arument. Can I get help writing to a text file if found if not nothing needs to happen
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34892756
Sorry again, I had a typo there.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True   

strEDI = "myfile.txt"
strOutputFile = "output.txt"
strTrigger = "PID.*ESD"
arrDataToRetrieve = Array("BEG.*", "DTM.*", "PER.*", "PO1.*")

strMessage = ""
blnTriggerFound = False
Const intForReading = 1
Set objEDI = objFSO.OpenTextFile(strEDI, intForReading, False)
While Not objEDI.AtEndOfStream
	strLine = objEDI.ReadLine
	objRegEx.Pattern = strTrigger
	Set colMatches = objRegEx.Execute(strLine)
	If colMatches.Count > 0 Then
		blnTriggerFound = True
		For Each strMatch In colMatches   
			If strMessage = "" Then
				strMessage = strLine
			Else
				strMessage = strMessage & VbCrLf & strLine
			End If
		Next
	End If
	For Each strData In arrDataToRetrieve
		objRegEx.Pattern = strData
		Set colMatches = objRegEx.Execute(strLine)
		If colMatches.Count > 0 Then
			For Each strMatch In colMatches   
				If strMessage = "" Then
					strMessage = strLine
				Else
					strMessage = strMessage & VbCrLf & strLine
				End If
			Next
		End If		
	Next
Wend
objEDI.Close

If blnTriggerFound = True Then
	Set objOutput = objFSO.CreateTextFile(strOutputFile, True)
	objOutput.Write strMessage
	objOutput.Close
	MsgBox "Data has been output to " & strOutputFile
Else
	'MsgBox "Not found"
End If

Open in new window

0
 

Author Comment

by:Navarre_EDI
ID: 34908734
Hello

That work but one question. Sample file below

In the file we build it should only have
the PO1 that is before PID when ESD is found.

BEG*00*NE*00847004402726**20110201
DTM*010*20110201
PER*DC**TE*5622347790
PER*DC**EM*john.welsch@boeing.com
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC

Not have

BEG*00*NE*00847004402726**20110201
DTM*010*20110201
PER*DC**TE*5622347790
PER*DC**EM*john.welsch@boeing.com
PO1*1*1*EA*50**IN*556639*VN*8064101  This should not be in the file
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC


Sample File

ISA*00*          *00*          *ZZ*COSTCOECOMM2   *14*102276177NAV   *110201*0831*U*00401*000049501*0*P*>
GS*PO*COSTCOECOMM2*102276177NAV*20110201*0831*49501*X*004010
ST*850*495010001
BEG*00*NE*00847004402726**20110201
REF*VR*0678500
REF*OP*217564016
DTM*010*20110201
TD5**2*UPSM*M********CG
N9*H5*LEGAL REQUIREMENTS
MSG*This order incorporates the costco.com E-standard Terms, as they may
MSG*be amended from time to time, and any applicable signed agreements
MSG*between us.
N1*ST*John Welsch*93*00011
N3*567 TEA HOUSE LN
N4*MOUNT PLEASANT*SC*294647802
PER*DC**TE*5622347790
PER*DC**EM*john.welsch@boeing.com
N1*OB*John Welsch
N3*567 TEA HOUSE LN
N4*MOUNT PLEASANT*SC*294647802*US
PO1*1*1*EA*50**IN*556639*VN*8064101
PID*F****TREND MICRO MAX SECURITY
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC
IT8*SC
CTT*1*1
SE*25*495010001
IEA*1*000049501
0
 

Author Comment

by:Navarre_EDI
ID: 34908744
Rob

If you dont undertstand I can explain better and thanks for all the help
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34910947
Hi, there's not much difference between those lines that should and shouldn't be there.....is there some logical way to decide which one to keep?

Rob.
0
 

Author Comment

by:Navarre_EDI
ID: 34916596
Helo Rob

When ever there is ESD in PID we need to grab the line right before PO1. Those are the rules. Can you help
0
 

Author Comment

by:Navarre_EDI
ID: 34920308
Hello Rob

Could we do soemthing like that if ESD is found then grab the line above it?

Thanks
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34922139
OK, I've had to do it a slightly different way, but I think this works.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objRegEx = CreateObject("VBScript.RegExp")
objRegEx.Global = True   

strEDI = "myfile.txt"
strOutputFile = "output.txt"
strTrigger = "[\r\n]PID.*ESD.*[\r\n]"
arrDataToRetrieve = Array("[\r\n]BEG.*[\r\n]", "[\r\n]DTM.*[\r\n]", "[\r\n]PER.*[\r\n]", "[\r\n].*PO1.*[\r\n]PID.*ESD.*[\r\n]")

strMessage = ""
blnTriggerFound = False
Const intForReading = 1
Set objEDI = objFSO.OpenTextFile(strEDI, intForReading, False)
strAllText = objEDI.ReadAll
objEDI.Close

objRegEx.Pattern = strTrigger
Set colMatches = objRegEx.Execute(strAllText)
If colMatches.Count > 0 Then
	blnTriggerFound = True
	'For Each strMatch In colMatches
	'	If Left(strMatch, 2) = VbCrLf Then strMatch = Mid(strMatch, 2)
	'	If Right(strMatch, 2) = VbCrLf Then strMatch = Left(strMatch, Len(strMatch) - 2)
	'	If strMessage = "" Then
	'		strMessage = strMatch
	'	Else
	'		strMessage = strMessage & strMatch
	'	End If
	'Next
End If
For Each strData In arrDataToRetrieve
	objRegEx.Pattern = strData
	Set colMatches = objRegEx.Execute(strAllText)
	If colMatches.Count > 0 Then
		For Each strMatch In colMatches
			If Left(strMatch, 2) = VbCrLf Then strMatch = Mid(strMatch, 2)
			If Right(strMatch, 2) = VbCrLf Then strMatch = Left(strMatch, Len(strMatch) - 2)
			If strMessage = "" Then
				strMessage = strMatch
			Else
				strMessage = strMessage & strMatch
			End If
		Next
	End If		
Next

If blnTriggerFound = True Then
	Set objOutput = objFSO.CreateTextFile(strOutputFile, True)
	objOutput.Write strMessage
	objOutput.Close
	MsgBox "Data has been output to " & strOutputFile
Else
	'MsgBox "Not found"
End If

Open in new window

0
 

Author Comment

by:Navarre_EDI
ID: 34962050
Hello

I try it and found two problems

Old Output:
BEG*00*NE*00847004402726**20110201
DTM*010*20110201
PER*DC**TE*5622347790
PER*DC**EM*john.welsch@boeing.com
PO1*1*1*EA*50**IN*556639*VN*8064101
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC
PO1*1*1*EA*30**IN*558194*VN*8064937
PID*F****ESD TREND MICRO A/V 2011 4-PC

New Output with the script above.  missing one of PER fields
BEG*00*NE*00847004402726**20110201
DTM*010*20110201
PER*DC**TE*5622347790
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC


But it grab the data PO1 field correct but the only problem is if we have multiple lines it will not grab.

I sent a file with multiple ESD in it.
Hope this help and thanks
Costco.txt
0
 

Author Comment

by:Navarre_EDI
ID: 34962055
Thanks Rob for looking at this hopefully you understand if not I will explain again
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34964500
So you want to grab multiple PO1 and PER lines?   I though you only wanted the one PO1 line above the ESD line....
0
 

Author Comment

by:Navarre_EDI
ID: 34964516
Correct

The first script you gave me we was grab both PER lines

Also I want to grab multplie PO1 lines that have ESD and PID
example

PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC
PO1*1*1*EA*30**IN*558194*VN*8064937
PID*F****ESD TREND MICRO A/V 2011 4-PC
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 34965673
OK, I may have had too many line feeds in the regex matches.  Change these two lines:
strTrigger = "[\r\n]PID.*ESD.*[\r\n]"
arrDataToRetrieve = Array("[\r\n]BEG.*[\r\n]", "[\r\n]DTM.*[\r\n]", "[\r\n]PER.*[\r\n]", "[\r\n].*PO1.*[\r\n]PID.*ESD.*[\r\n]")

to this
strTrigger = "[\r\n]PID.*ESD.*"
arrDataToRetrieve = Array("[\r\n]BEG.*", "[\r\n]DTM.*", "[\r\n]PER.*", "[\r\n].*PO1.*[\r\n]PID.*ESD.*")

and see how it goes.

Regards,

Rob.
0
 

Author Comment

by:Navarre_EDI
ID: 34982994
Rob

With that change I think I got it to work but I also want to add the name to be built to the file. Also can you explain alittle about the script so I know how to make changes.

This is the line I would like to add

N1*ST*John Welsch*93*00011


Thanks alot have a great weekend Rob
0
 

Author Comment

by:Navarre_EDI
ID: 34983073
Rob

I firgure out how to add N1 but would like alittle help explain the Vbs.
Also the output is building the file with a blank line at begin of the file. I include the file.
output.txt
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 34993805
Hi, sorry for my delay.  I have removed the line feed at the top of the file with:
If Left(strMessage, 1) = VbLf Then strMessage = Mid(strMessage, 2)

and removed the carriage return at the end of the file with:
If Right(strMessage, 1) = VbCr Then strMessage = Left(strMessage, Len(strMessage) - 1)

I have added comments in the file to help you understand it.  The bulk of it uses Regular Expressions to find patterns in the file, and I'm not *that* great with them, so I've explained them as best as I can.

Regards,

Rob.
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Create the regular expression object
Set objRegEx = CreateObject("VBScript.RegExp")
' Sets or returns a Boolean value that indicates if a pattern should match all occurrences in an entire search string or just the first one
' We set it to True to return a collection containing all matches
objRegEx.Global = True

' Input file
strEDI = "myfile.txt"
' Output file
strOutputFile = "output.txt"
' This is the regular expression pattern that we must find in the file. If it is not found, blnTriggerFound is left at false, and no data is output.
strTrigger = "[\r\n]PID.*ESD.*"
' This array holds each of the regular expression patterns to find in the file.
' [\r\n] matches a line feed, so, to match a string at the beginning of a line, we use this first
' .* returns multiple matches of any character, which is basically just anything after the specified text
arrDataToRetrieve = Array("[\r\n]N1.*", "[\r\n]BEG.*", "[\r\n]DTM.*", "[\r\n]PER.*", "[\r\n].*PO1.*[\r\n]PID.*ESD.*")

' Set output message to an empty string
strMessage = ""
' Set the flag for the trigger to false
blnTriggerFound = False
' Open the input file
Const intForReading = 1
Set objEDI = objFSO.OpenTextFile(strEDI, intForReading, False)
' Read all of the file into a string variable to perform the regular expression searches on
strAllText = objEDI.ReadAll
' Close the input file
objEDI.Close

' Set the regular expression pattern to look for
objRegEx.Pattern = strTrigger
' Perform the search on strAllText, returning all matches to the colMatches collection
Set colMatches = objRegEx.Execute(strAllText)
' Check in the trigger was found, based on whether the count of mathces is greater than zero
If colMatches.Count > 0 Then blnTriggerFound = True

' Enumerate through each element in the arrDataToRetrieve array, so search for each pattern
For Each strData In arrDataToRetrieve
	' Set the pattern
	objRegEx.Pattern = strData
	' Search strAllText for each pattern
	Set colMatches = objRegEx.Execute(strAllText)
	If colMatches.Count > 0 Then
		' Enumerate through the returned matches
		For Each strMatch In colMatches
			' Trim off the carriage returns from the matches (because we used /r/n)
			If Left(strMatch, 2) = VbCrLf Then strMatch = Mid(strMatch, 2)
			If Right(strMatch, 2) = VbCrLf Then strMatch = Left(strMatch, Len(strMatch) - 2)
			' Append the match found to our output string
			If strMessage = "" Then
				strMessage = strMatch
			Else
				strMessage = strMessage & strMatch
			End If
		Next
	End If		
Next
' Trim the carriage returns and line feeds from the final output string
If Left(strMessage, 1) = VbLf Then strMessage = Mid(strMessage, 2)
If Right(strMessage, 1) = VbCr Then strMessage = Left(strMessage, Len(strMessage) - 1)

' Check whether the trigger was found, and only if it was found, output the data to the output file
If blnTriggerFound = True Then
	' Create the new, empty output file
	Set objOutput = objFSO.CreateTextFile(strOutputFile, True)
	' Write the message
	objOutput.Write strMessage
	' Close the output file
	objOutput.Close
	MsgBox "Data has been output to " & strOutputFile
Else
	'MsgBox "Not found"
End If

Open in new window

0
 

Author Comment

by:Navarre_EDI
ID: 35052158
Hello

Is there a way to put char return line feed after every line? No extra line at the end or begin.

Thanks the program works great testing so far
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35052636
In the latest output, I made it so there were no extra lines at the beginning or end...is that what you wanted?

Rob.
0
 

Author Comment

by:Navarre_EDI
ID: 35057603
Yes but I need char return line feed at the end of each line
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35065815
I get output like this:

N1*ST*John Welsch*93*00011
N1*OB*John Welsch
BEG*00*NE*00847004402726**20110201
DTM*010*20110201
PER*DC**TE*5622347790
PER*DC**EM*john.welsch@boeing.com
PO1*1*1*EA*32**IN*558193*VN*8064936
PID*F****ESD TREND MICRO A/V 2011 3-PC

Open in new window


where each line is separated by CR and LF.  I know that because if I replace
      objOutput.Write strMessage
with
      objOutput.Write Replace(strMessage, VbCrLf, "")

it comes out all on one line.

What is the output that you need?

Regards,

Rob.
0
 

Author Comment

by:Navarre_EDI
ID: 35128156
Hello

Your script works perfect but how about if it is a nested of files in one document. So we have 4 documents in between ISA IEA

Can this script still work for that?

BCEG1JAB.txt
0
 

Author Comment

by:Navarre_EDI
ID: 35128160
Thanks again for all your help
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35135321
We'd have to be able to identify what separates the specific individual documents.  How many documents does the above sample consist of?  Do they all have a common start or end line, which is hopefully unique to the rest of the details?

Regards,

Rob.
0
 

Author Comment

by:Navarre_EDI
ID: 35137108
Rob

Just forgot the solution I ask for it will not work. I will just go with your first soluction breaking up indivual files.

Do you think you can help me with a script because I change the delimeter and the script not working anymore?
0
 

Author Closing Comment

by:Navarre_EDI
ID: 35137115
This was a great script and he explain everything
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35142236
Hi, thanks for the grade.  What did you change? Do you still need help with it?

Rob.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

726 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