Forum Discussion

aaronb's avatar
aaronb
New Contributor
4 years ago

Extract transaction sets from text data

HI

I have the below 810 EDI formatted text data that I am needing some assistance with. Each transaction set begins with “ST” and ends with “SE”. I am need to extract each of the two transaction sets individually so I keep each “set” together for further manipulation downstream. Any thoughts on how this can be done in the most efficient manner?

Thanks in advance

ST*810*40001~
BIG*20211013*27383638*****DI~
REF*CR*1174143~
REF*VN*EDDON20~
REF*OI*27383638~
N1*SF*EDWARD DON & COMPANY -  FLORIDA*91*EDDON20~
N3*11500 MIRAMAR PARKWAY~
N4*MIRAMAR*FL*33025~
N1*ST*TAP AVENTURA LLC*92*1174143~
N3*19501 BISCAYNE BLVD #1960~
N4*MIAMI*FL*33180~
IT1*000010*1.000*PK*89.95**VN*000000000001102902~
PID*F****PLATE RND 6" KILN PEPPER~
IT1*000030*1.000*CS*116.61**VN*000000000001144794~
PID*F****COASTER PULPBOARED OFF WHT TAP 42~
IT1*000040*3.000*CS*31.68**VN*000000000001041937~
PID*F****LINER  37X50 1.5MIL LD 44GAL SUREFIT BLK~
IT1*000060*3.000*CS*66.05**VN*000000000001160905~
PID*F****CONT HNG 9X9" LITES PP BLK~
IT1*000080*1.000*CS*32.94**VN*2P1211~
PID*F****TISSUE BATH 2 PLY MINI JUMBO TORK ADVANC~
IT1*000090*1.000*EA*71.50**VN*000000000001127594~
PID*F****DISPENSER TOWEL TORK INTUITION BLACK~
IT1*000110*1.000*CS*28.88**VN*000000000001174889~
PID*F****SHEET PAPER 8X12" RECYCLED KRAFT~
TDS*67738~
TXI*SU*44.31~
CTT*7**165.311*LB~
SE*29*40001~
ST*810*40002~
BIG*20210901*27181502**TARA***DI~
REF*CR*1106203~
REF*VN*EDDON20~
REF*OI*27181502~
N1*SF*EDWARD DON & COMPANY -  FLORIDA*91*EDDON20~
N3*11500 MIRAMAR PARKWAY~
N4*MIRAMAR*FL*33025~
N1*ST*TAP 42*92*1106203~
N3*1411 SOUTH ANDREWS AVE~
N4*FORT LAUDERDALE*FL*33316~
IT1*000010*1.000*CS*76.34**VN*000000000001119000~
PID*F****CUP COFFEE 12 OZ CERAMIC BLK~
IT1*000020*1.000*EA*43.70**VN*SH01~
PID*F****SHIPPING~
TDS*12844~
TXI*SU*8.4~
CTT*2**31.000*LB~
SE*19*40002~
expression_language
transform

3 Replies

  • Charles's avatar
    Charles
    New Contributor II
    4 years ago

    Hi @aaronb ,

    I’m not sure how this is coming in or what downstream processing you’re doing, but could you utilize the $string.split() function if the input will always be this standard?

    For example, if your text is the $content of a JSON response, you could use the mapper to go from
    $content.split(‘ST810’) to $data

    And then you would have an array in $data that you could iterate through/process together downstream.

    Charles

    • aaronb's avatar
      aaronb
      New Contributor
      4 years ago

      Charles,

      If it helps the content is coming in as just a text file which I am then parsing using a CSV delimiter snap with “*” as the delimiter in this case. The issue I run into is that in the file each ST to SE block of data needs to be kept together as it contains invoice header and invoice detail data. The only context of what invoice line detail goes with which header is that they both reside in the same ST to SE block. Hopefully that makes sense or helps clarify.

      • aleung's avatar
        aleung
        Contributor III
        4 years ago

        EDI data usually required a purpose build parser. You either likely need to write a custom python script to group it by ST/SE or some 3rd party solution to get it to a XML or JSON form before feeding it to snapLogic