Link to home
Start Free TrialLog in
Avatar of Navarre_EDI
Navarre_EDI

asked on

EDI Inbound 850 pull information into a text file.

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
Avatar of RobSampson
RobSampson
Flag of Australia image

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

Avatar of Navarre_EDI
Navarre_EDI

ASKER

Hello

The script works good  but only writes to msgbox instead of a file. Can I get help writing this to a file
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

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

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
Rob

If you dont undertstand I can explain better and thanks for all the help
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.
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
Hello Rob

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

Thanks
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

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
Thanks Rob for looking at this hopefully you understand if not I will explain again
So you want to grab multiple PO1 and PER lines?   I though you only wanted the one PO1 line above the ESD line....
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
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.
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
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
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
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
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
In the latest output, I made it so there were no extra lines at the beginning or end...is that what you wanted?

Rob.
Yes but I need char return line feed at the end of each line
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.
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
Thanks again for all your help
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.
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?
This was a great script and he explain everything
Hi, thanks for the grade.  What did you change? Do you still need help with it?

Rob.