1 2 3 Previous Next 40 Replies Latest reply on Nov 20, 2015 11:13 AM by yomango

    Parsing CSV documents

    yomango

      Hello, community fellows. I get EDI - CSV documents reports with no carriage returns on them. I want to parse each line. I can have the calculation go to the line I want but I can measure the length of the line because there's not a carriage return (¶), not a tilde (~) or an ending asterisk (*) I do not have a way to position that last character to get the length of the string line (the sample below shows 12  asterisk (*) for the B3 string line but some reports have 13). Neither I can reference the next line down since the reports vary from one to another (see the B3 string line):

      ISA*00*          *00*          *02*FXFE           *ZZ*VSIA5032960454 *150924*0253*U*00401*000000815*0*P*:

      GS*IM*FXFE*VSIA5032960454*20150924*0253*815*X*004010

      ST*210*8150001

      B3**2241308812**PP**20150921*23784**20150923*035*FXFE

      G62*86*20150921

      N1*SH*VETERINARY SERVICE INC

       

      ISA*00*          *00*          *02*FXFE           *ZZ*VSIA5032960454 *151002*0313*U*00401*000000821*0*P*:

      GS*IM*FXFE*VSIA5032960454*20151002*0313*821*X*004010

      ST*210*8210001

      B3**2241308860*SO5580918*PP**20150924*10859**20150928*035*FXFE

      N9*BM*SO5580918

      G62*86*20150924


      I will be greatly thankful for your help

        • 1. Re: Parsing CSV documents
          MacEasy

          It looks like each line begins with ISA*00.

           

          I like using the function 'ParseData'  available on custom functions at Brian Dunnings website.

          http://www.briandunning.com/cf/559

           

          Then using the occurrence parameter and pattern count to determine the number of ISA*00, then a simple loop should let you loop thru your lines and parse your data.

           

          You may also look for another delimiter, that may not show, sometimes you have a linefeed as opposed to a carriage return.

          I'd look at the character right before the second occurrence of ISA*00 using the CODE function to get at the unicode value.  Often in CVS the return is a Char(32) vs. a Char(13)=code("¶"),   Sometimes it's a char(3)  .  One more comment on that I remember in one of the versions of filemaker,  10, 11, 12.  You'd pass a parameter to a script and it would change your return character from char(13) to char(32), so you had to know to substitute and convert your carriage returns back to the normal carriage returns filemaker uses.

           

          Hope this helps you to parse your data

          • 2. Re: Parsing CSV documents
            yomango

            Hello, Peter. Thank you for taking the time to help me out. I do not quite follow you. I do not have a problem parsing the ISA line, all reports start with it. My problem is getting the length of any other line so I can parse it all. This is my formula for line B3*: where the line reads..B3**2241308812**PP**20150921*23784**20150923*035*FXFE

            *Case(PatternCount ( EDI 210 Invoice::EDI_txt ; "ST*" );

                      Let([ B3=Position(EDI 210 Invoice::EDI_txt;"B3*";1;1);

                            E=Position(EDI 210 Invoice::EDI_txt;"Position to next to last character";1;1);

                           B3T=Middle(EDI 210 Invoice::EDI_txt;ST;E-B3);

                               L=E-ST;

                            A2=Position(B3T;"*";1;2);

                            A3=Position(SB3T;"*";1;3)

                          ];

                      Case(A3=0 and A2 ≥ 1;Right(B3T;L-A2); Case(A3=0 and A2 = 0;"";Case(A3≥ 1 and A2 ≥ 1;Middle(B3T;A2+1;A3-A2-1))))

                           )

                    )

             

            The "E" calculation is my problem

            • 3. Re: Parsing CSV documents
              yomango

              Correction: The formula should be:

              Case(PatternCount ( EDI 210 Invoice::EDI_txt ; "ST*" );

                        Let([ B3=Position(EDI 210 Invoice::EDI_txt;"B3*";1;1);

                              E=Position(EDI 210 Invoice::EDI_txt;"Position to next to last character";1;1);

                             B3T=Middle(EDI 210 Invoice::EDI_txt;ST;E-B3);

                                 L=E-ST;

                              A12=Position(B3T;"*";1;12);

                              A13=Position(SB3T;"*";1;13)

                            ];

                        Case(A13=0 and A12 ≥ 1;Right(B3T;L-A12); Case(A13=0 and A12 = 0;"";Case(A13≥ 1 and A12 ≥ 1;Middle(B3T;A12+1;A13-A12-1))))

                             )

                      )

              • 4. Re: Parsing CSV documents
                beverly

                This is EDI and not comma-separated-values (as there are no commas). Your "line/row/record" delimiter is return, but your "field/column" delimiter is the asterisk (*). And as you have found the text is not consistent columns. Each position in each line means something. You should be able to get a document explaining the placement of these values, so you get the correct data into the correct field.

                 

                Note, that you may have more than one (*) next to each other. That means a "blank" field, so don't skip those!

                 

                As others have stated, PatterCount(), Position() and the other functions are handy in parsing this kind of TEXT. Some EDI is also mapped to particular lengths within the "fields". It doesn't appear that you can use that information.

                 

                Typically, I push each line into a variable, then parse from there. You may wish to substitute each (*) -in a single line in the variable- and then you can use the 'value' functions. You still need to know which value is which field.

                 

                HTH,

                Beverly

                • 5. Re: Parsing CSV documents
                  MacEasy

                  If understand your problem correctly, it looks like there are 10 fields of data per record.

                   

                  I'm also assuming your problem line always begins with "B3*" and that "FXFE" only appears once in the B3 field data (it uses that to parse the data).

                   

                  So here's how I would set it up.

                   

                  $csvtext=EDI 210 Invoice::EDI_txt

                  $csvtext="¶" & Substitute($csvtext;" ISA*00*";" ¶ISA*00*")

                  // I substituted in a return character to separate records

                  // I also noticed there are a couple bugs in that parse data function to if you try to use the same start & end tag or a tag that is simply a space.

                  $NumberRecords = patterncount($csvtext,""),

                  $counter = 1

                   

                  Loop

                  Exit loop if $counter>$numberrecords

                  $currentrecord = "ISA*00*" & ParseData($csvtext; "ISA*00*"; "¶";$counter)

                  $fieldofinterest ="B3*" &  parsedata($currentrecord, " B3*", "FXFE", 1) & "FXFE"

                  //because it gets the text between "B3* and FXFE, you have to append those back on.

                  $counter=$counter+1

                  Endloop

                  • 6. Re: Parsing CSV documents
                    yomango

                    Again thank you for your time. But as I said, there is not a character  or a delimiter to let me reference that to measure “B3”, for instance. So, in your calculation example, “FXFE” is data from a report but it won’t show  on all reports, so I can’t use it as a reference delimiter. And if the .txt document were to come with carriage returns (¶) I wouldn’t have any problem. When I open the .txt with Word, paste or import the it into FM I get a carriage return; but I can’t convert to Word the hundreds of .txt documents I receive every day, no time for that.

                     

                     

                     

                    Regards,

                     

                     

                     

                    De: Peter Beehler

                    Enviado el: Tuesday, November 17, 2015 1:27 PM

                    Para: Mauricio Navarro <basesdedatosfmp@gmail.com>

                    Asunto: Re:  - Parsing CSV documents

                     

                     

                     

                     

                     

                    <https://community.filemaker.com/?et=watches.email.thread>

                     

                     

                     

                     

                     

                    Parsing CSV documents

                     

                     

                    reply from  <https://community.filemaker.com/people/MacEasy?et=watches.email.thread> Peter Beehler in Discussions -  <https://community.filemaker.com/message/522915?et=watches.email.thread#522915> View the full discussion

                    • 7. Re: Parsing CSV documents
                      siplus

                      once you isolate a line, a good idea is to replace the field delimiter with a ¶ using substitute, therefore getting a list, which will easily deliver its contents with GetValue() thus avoiding Patterncount and Middle and Position.

                      • 8. Re: Parsing CSV documents
                        yomango

                        Hello, Beverly. Thank you for taking the time to respond. I have no problem parsing any line of text from the EDI file (sorry I called CSV)., except for the last field on the column since the EDI shows all records on a single line. If I paste the text into a FM text field and do a carriage return within the field, the parsing calculation works (because I created CR’s manually), if I open the EDI on Word, copy paste into FM, I get CR’s as well. But there are hundreds of  EDI’s to parse, not enough time to open every one with Word, save them to be imported or copy paste them.

                         

                         

                         

                        As you also remarked, each line has a certain number of characters but fields could  have a min/max of characters, so I can’t go for the length of the line unless I can parse out that line before. And to parse it I need to have a delimiter at the end (which I don’t), except “empty space. No last “*”, no “~”, no CR, no”|”. That is my problem.

                         

                         

                         

                        Regards,

                         

                         

                         

                        De: Beverly Voth

                        Enviado el: Tuesday, November 17, 2015 1:14 PM

                        Para: Mauricio Navarro <basesdedatosfmp@gmail.com>

                        Asunto: Re:  - Parsing CSV documents

                         

                         

                         

                         

                         

                        <https://community.filemaker.com/?et=watches.email.thread>

                         

                         

                         

                         

                         

                        Parsing CSV documents

                         

                         

                        reply from Beverly Voth <https://community.filemaker.com/people/beverly?et=watches.email.thread>  in Discussions - View the full discussion <https://community.filemaker.com/message/522872?et=watches.email.thread#522872>

                        • 9. Re: Parsing CSV documents
                          yomango

                          Hello, thank you for your time. How do you isolate a line when there isn’t a carriage return nor any last character delimiter. I do not know if you had seen the line sample I posted. I am not sure if when you copy that sample line to try it creates a carriage return, but the EDI’s reports I get do not, the entire text is one whole line.

                           

                           

                           

                          Regards,

                           

                           

                           

                          De: siplus

                          Enviado el: Tuesday, November 17, 2015 2:16 PM

                          Para: Mauricio Navarro <basesdedatosfmp@gmail.com>

                          Asunto: Re:  - Parsing CSV documents

                           

                           

                           

                           

                           

                          <https://community.filemaker.com/?et=watches.email.thread>

                           

                           

                           

                           

                           

                          Parsing CSV documents

                           

                           

                          reply from siplus <https://community.filemaker.com/people/siplus?et=watches.email.thread>  in Discussions - View the full discussion <https://community.filemaker.com/message/522937?et=watches.email.thread#522937>

                          • 10. Re: Parsing CSV documents
                            siplus

                            Replace("ISA*00*"; "¶ISA*00*") ?

                            • 11. Re: Parsing CSV documents
                              beverly

                              Are you sure there's no "end-of-line" character? perhaps it's CRLF or just LF. If you can paste into Word and "see" the return, the file has to have something. Word is just "converting" whatever is end-of-line to CR (or perhaps CRLF).

                               

                              Do you have documentation on the format you are trying to import? That would surely give you the information on end-of-line.

                               

                              beverly

                              • 12. Re: Parsing CSV documents
                                beverly

                                I opened your sample file with a text editor that does not convert the end-of-line. What you have is the LF (line-feed) character. Also known as HEX# 0A or ASCII Char(11). The CR (carriage-return) is HEX# 0D or ASCII Char(13).

                                 

                                If you can get the entire text INTO an FM field, you may try to change Char(11) to Char(13) and then go from there.

                                 

                                Beverly

                                • 13. Re: Parsing CSV documents
                                  MacEasy

                                  I agree with Beverly.   Use left & Right text functions to isolate the last character of what you know is the end of a line and then use filemaker code function to see what the hidden end of line character is...

                                   

                                  Then use substitute(text, char('codenumberyoufind'), "¶")

                                   

                                  If word can see it, it's there.  It's probably code = 32,   common LF used.

                                  For that matter just try   substitute(text, char(32), "¶") and see if that does it

                                  • 14. Re: Parsing CSV documents
                                    yomango

                                    Thanks. Good idea if I knew what are you talking about, better yet know how to do it. Yes, I have the text in FileMaker, how  do I do your suggestion.

                                     

                                     

                                     

                                    Regards,

                                     

                                     

                                     

                                    De: Beverly Voth

                                    Enviado el: Tuesday, November 17, 2015 3:28 PM

                                    Para: Mauricio Navarro <basesdedatosfmp@gmail.com>

                                    Asunto: Re:  - Parsing CSV documents

                                     

                                     

                                     

                                     

                                     

                                    <https://community.filemaker.com/?et=watches.email.thread>

                                     

                                     

                                     

                                     

                                     

                                    Parsing CSV documents

                                     

                                     

                                    reply from Beverly Voth <https://community.filemaker.com/people/beverly?et=watches.email.thread>  in Discussions - View the full discussion <https://community.filemaker.com/message/522972?et=watches.email.thread#522972>

                                    1 2 3 Previous Next