0

Flat File content:

ST*850*12500001|
BEG*00*NE*71249364**20130103|
CUR*SE*SGD|
REF*BT*SGL169816-7191416|
P01*0000000001*4*EA*0*CP*UP*731304265511*VP*SURT1000XLIQ|
P01*0000000002*10*EA*0*CP*UP*731304265511*VP*SURT1000XLIQ|
P01*0000000003*100*EA*0*CP*UP*731304265511*VP*SURT1000XLIQ|

first lines of file

Question:

Anybody who can share an idea on how to parse dynamically each element. The length of each data differs from time to time like for example : 12500001, 731304265511 and SURT1000XLIQ. The problem is how can i get the data between the asterisk (*) and pipeline (|). Thanks to help....

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
RedHat
  • 205
  • 2
  • 12
  • 24
  • 1
    just to notify you: x12 files are mostly/always 'enveloped' with ISA header and a trailer. I do not think what you show here is what you will actually receive. – eppye Apr 15 '13 at 16:58
  • 2
    Not only that, but the ISA segment helps you identify the segment terminator, subelement separator and element delimiter. You *can* figure it out from your sample, but you're also missing sender / receiver information. – Andrew Apr 15 '13 at 20:08
  • Hi, i already have the complete documentation and sample file of 850 prior from posting this. I've just posted where should be the parsing starts. – RedHat Apr 22 '13 at 05:55

4 Answers4

1

Your flat file looks like an EDI file, though not an EDIFACT.

There are some converters (either open source or commercial) from EDI to XML, maybe that you can try with such a tool to be able to read the resulting xml file into a DataWindow or a datastore?

As I do not know the actual file format, I cannot be more precise.

Example of EDI -> XML converters :

EDIT: I just landed on an ancient post on the PowerBuilder Developer Journal that addresses the same subject: "Converting X12 EDI to XML", it gives additional ideas.

Terry
  • 6,160
  • 17
  • 16
Seki
  • 11,135
  • 7
  • 46
  • 70
  • 1
    Why convert it to XML? Why not dump it directly into a database? Or a CSV? Or a flat file? Everyone seems to want to translate to XML and I don't get it. Many times, it's just an extra step, unless your ERP or middleware system requires it (like converting EDI to an IDOC for SAP). – Andrew Apr 15 '13 at 20:10
  • @Andrew: I suggest to convert it because the OP just speaks about a file, without telling if he produces it or if it is given as is. Also while xml must not be a golden hammer, using it as pivot format can increase the ways to handle it after in PB (datawindow, datastore, pbdom). I doubt that something exists already for Powerbuilder to process EDI files. Of course the other option is else to make his own parser from specs. – Seki Apr 16 '13 at 07:52
  • Guys, i just want to share what i did. I have developed a PB function that will parsed the data based on the technical design then dumped it to the table after the parsing procedure and these is quite difficult because a lot of elements to be parse and the parsing should behave dynamically because the character length of each element is not constant. Thank you for the thoughts and ideas. – RedHat Apr 22 '13 at 06:01
0

That's an X12 file, an 850 Purchase Order. As eppye suggests in the comments, you will need to find out exactly what standard the file you're getting follows. To get you started there's a version of the 850 here. "Draft MEMA 4010 850" will help you understand the format. "GCommerce 4010 850V1.4.doc" is the Data Dictionary that tells you what's in each element. Please understand, though, that unless you're selling auto parts, that's probably not the exact specification of the file you're working with. The people sending you the file should provide this to you.

I would create a user object for each segment and loop, and one to represent the purchase order itself. The purchase order UO needs variables that are the data type of the segments and loops. Don't include the segments that are inside the loops, put those in the UO for the loop. The UO for a segment will have variables that are standard datatypes like string, integer, date, etc. for the Data Elements in the segment. Refer to the Data Dictionary for the datatypes.

Note that if a segment repeats, the variable that holds the segment is an array. The variables for the loops will be arrays.

Your objects that handle segments will have a method, e.g. of_importLine that splits up the line at the asterisk and stores the values.

Your objects that handle loops will have a method, e.g. of_readLoop that reads lines (segments) until it reads the last segment of the loop. For each segment, it will create the UO for that type of segment, assign it to the instance variable, or the next array slot if the segment can repeat, and call the new object's of_importLine.

Your object that handles the purchase order is like one for a loop, except that when it sees the first segment of a loop, it creates the UO for that kind of loop, assigns it to the next array slot for the loop, and calls the new object's of_readLoop. Note that while your purchase order object is reading inside a loop it will create a new object to read and store each repeat of the loop.

Your objects that read loops should log an error and stop if a required segment is missing or a segment that doesn't belong in the loop is found. The error should include the line number and contents. I can't give you a detailed description of how to validate the purchase order itself because many of the segments are optional in the specification, but they probably aren't optional in your application. For each segment and loop the object has a variable for, it needs the sequence where the item appears and minimum and maximum occurrences. Then the PO object can check the segments and loops as it goes along.

This will get the data from the document into objects that represent the parts of the document so you can work with the data.

Community
  • 1
  • 1
Hugh Brackett
  • 2,706
  • 14
  • 21
  • looks like a plain x12 850 order to me. Could you elaborate on why this is an MEMA 850 Purchase Order? – eppye Apr 15 '13 at 22:02
  • thank you. But like etc is still there. Chances are big it is an 850 that follows another guideline (from another buyer); might lead to misunderstandings? – eppye Apr 16 '13 at 16:59
  • of course. the people sending him the file need to tell him what they are sending. i edited my answer to include that, and make clear that the links I provided aren't necessarily the exact specification for his file. – Hugh Brackett Apr 17 '13 at 14:29
0

If you were using plain old PowerBuilder, the basics would be to do a bunch of Pos() and Mid()'s; nothing automagic. However, in your tags you mention PFC; you can always bring in the String Service's of_ParseToArray() function after you've read the file in, something like (the following is untested and is up to the reader to find the bugs):

long ll_Line, ll_LineCount, ll_Element, ll_ElementCount
string ls_Lines[], ls_Elements[]
n_cst_String lnv_String  

ll_LineCount = lnv_String.of_ParseToArray (ls_FileContents, "|", ls_Lines)
FOR ll_Line = 1 TO ll_LineCount
   ll_ElementCount = lnv_String.of_ParseToArray (ls_Lines[ll_Line], "*", ls_Elements)
   // process the line with the elements separated out...
NEXT

Good luck,

Terry

Terry
  • 6,160
  • 17
  • 16
0

Below is the method that I've derived.

ls_tranidcode = of_trimdata(is_Message, 4, '*')
ids_edihdr.SetItem(ll_hdrins,'TRANSETIDCODE',ls_tranidcode)

Function:
of_trimdata()

Return Type : String 
Argument Type : 
String arg_msg
Integer pos1
String  s_dlm

Long ll_pos2
String ls_ret

arg_msg = mid(arg_msg, pos1)            
ll_pos2 = POS(arg_msg,s_dlm) 
ls_ret = Mid(arg_msg, 1, ll_pos2 - 1)
arg_msg = Mid(arg_msg, ll_pos2 + 1) 

RETURN ls_ret
RedHat
  • 205
  • 2
  • 12
  • 24