Solved

EDI Inbound 850 pull information into a text file.

Posted on 2011-02-10
29
580 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Navarre_EDI
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Rob

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

Expert Comment

by:RobSampson
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Rob for looking at this hopefully you understand if not I will explain again
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
So you want to grab multiple PO1 and PER lines?   I though you only wanted the one PO1 line above the ESD line....
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Navarre_EDI
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yes but I need char return line feed at the end of each line
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks again for all your help
0
 
LVL 65

Expert Comment

by:RobSampson
Comment Utility
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
Comment Utility
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
Comment Utility
This was a great script and he explain everything
0
 
LVL 65

Expert Comment

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

Rob.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
Utilizing an array to gracefully append to a list of EmailAddresses
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

763 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

12 Experts available now in Live!

Get 1:1 Help Now