1 2 Previous Next 22 Replies Latest reply on Jan 16, 2013 9:00 AM by jjfcpa

    Parsing text?

    jjfcpa

      I am trying to parse text that I have imported from a text file. The text looks like this.

       

      000105 D1 0000019215+0000000000+

      000105 D5 0001735000+0000000000+

      000105 D9 0000013685+0000000000+

      000105 E210000127000+0000000000+

      000105 E3 0000329850+0000000000+

      0001051 0003441480+0000000000+

      0001052 0002474572+0000000000+

       

      Each line is terminated by a carriage return and I need to read each line into a variable and then substring it from there.

       

      I tried using the GetValues() function and incrementing the 2nd paramter, but it always returns the first line of text. Any help appreciated.

        • 1. Re: Parsing text?
          BruceRobertson

          You have essentially said there is something wrong inside this black box; can you tell me what it is?

           

          It is rather difficult to troubleshoot a calculation if you don't reveal it.

           

          Please post a copy of your script or calculation; or a copy of your file.

          • 2. Re: Parsing text?
            kacike

            Here is what you need.

             

            • $Rep is your counter that increments by 1.  $Rep also tells you which value you are looking at in the block.  If $Rep is 4, then you are looking at the 4th value in your block.
            • $TotalRep is the total number of values in your block and the number of times you will be looking at that block.
            • The GetValue function will get you the appropriate value based on the $Rep variable as it loops.
            • You want to do the extra pasing you were talking about right after $Value is set.

             

            This shoud do it for you.

             

            Screen Shot 2013-01-16 at 6.33.55 AM.png

            • 3. Re: Parsing text?
              jjfcpa

              Sorry for being unclear.  Kacike - my code is essentially exactly like yours.  What is happening is the $Value is getting set correctly the first time it's called, but then seems to return a blank line, then the next line of text in the file, then a blank line, then the next line of text in the file.  So, I'm getting random blank lines which do NOT exist in the file and do not happen if I use Visual Foxpro to read the text in the same way.  VFP has a gets() function that will return data until the next carriage return.  Call it again, and it returns the next block of text until it hits a carriage return. 

               

              I thought the GetValue() would work the same way, but there must be something else going on. 

               

              I think I might have better luck reading the text using Middle() and using variables to keep track of the "start" and "length" parameters. 

               

              This looks like it would be really simple to do but has turned out to be more complicated than I ever dreamed.  I have looked at the text file in TextEdit and it is properly formed with lines of text terminated with a carriage return. 

              • 4. Re: Parsing text?
                jjfcpa

                I just copied and pasted 5 lines from the file I am attempting to read from (one line at a time) and added my own carriage returns and I am getting the same returns. 

                 

                If I use the custom dialog with the following code:

                 

                GetValue($YTDHIS , $CurrentLine) & " - " & GetValue($YTDHIS , $CurrentLine + 1 ) & " - " & GetValue($YTDHIS , $CurrentLine + 2 )

                 

                I'm not using dashes, I'm using carriage returns so I get one line of text on each row.  Beause my lines are so short, they fit nicely in the custom dialog.

                 

                $YTDHIS is the entire text file.

                $CurrentLine is a line counter

                 

                I get one line of data, a blank line, then the next line of data.  Is it me or Filemaker?

                • 5. Re: Parsing text?
                  comment

                  jjfcpa wrote:

                   

                  I have looked at the text file in TextEdit and it is properly formed with lines of text terminated with a carriage return. 

                   

                  I think there can be very little doubt that the issue is with your data. TextEdit is not a good enough tool to examine a text file - you should use a proper text editor, such as TextWrangler, where you can choose to see non-printing characters and such.

                   

                  You can also analyze the imported text directly in Filemaker, albeit with a bit more work. Looking at =

                   

                  Code ( Middle ( YourTextfield ; n ; 1 ) )

                   

                  you should get 43 (the code of the plus sign) when n = 32. After that, with n = 33, the result should be 13 (the code of a carriage return), and with n = 34, the result should be 48 (the code of zero, the first character on the second line). I believe your findings will be different.

                  • 6. Re: Parsing text?
                    jjfcpa

                    Michael

                     

                    I know what you mean about Textedit, but I've also used Notepad in windows which is better.  Also used the "modify command" in VFP which is their built in text editor for writing code and I'm pretty confident the data is OK.  I'd be happy to email it to you if you like.

                     

                    I just finished re-writing the code to use the Position() function to find each subsequent carriage return and a MIddle( Text, Start, End - Start) function to read the data and it works fine.  I get one line of code with no blanks lines.

                     

                    Is there a way to copy my code from Filemaker so I can paste it to this message?  I can't even copy and paste to Word (which also creates plain text files). 

                     

                    I'd like to resolve this because I have 3 more files that I need to read into Filemaker and all will be formatted like the one I'm working with now.  FYI, these files are being exported from an accounting program called Passport.  The calculations that I'm making are for box 12DD on the W2's - "cost of employer sponsored health care" - otherwise known as Obamacare bureaucracy.

                    • 7. Re: Parsing text?
                      kacike

                      Politics aside...

                       

                      Your data must be the issue.  Maybe you have multiple carriage returns in a row.  I would suggest using the "Substitute" function to trim out double or triple carriage returns in a row.  The fact that you dont see multiple carriage returns does not mean they are not there.

                       

                      For example:  Substitute ( "block of text"; "¶¶" ; "¶" )

                      • 8. Re: Parsing text?
                        comment

                        jjfcpa wrote:

                         

                        I just finished re-writing the code to use the Position() function to find each subsequent carriage return and a MIddle( Text, Start, End - Start) function to read the data and it works fine.  I get one line of code with no blanks lines.

                         

                        I don't think that's a significant test - after all, there are no blank lines in your original data either. Why don't you try testing what I suggested in my previous post?

                         

                        It would also be interesting (though inconlcusive) to know if ValueCount ( YourTextfield ) returns 7 or a much higher number.

                         

                         

                        jjfcpa wrote:

                         

                        I'm pretty confident the data is OK.  I'd be happy to email it to you if you like.

                         

                        Why not attach a sample file showing the issue here?

                        • 9. Re: Parsing text?
                          jjfcpa

                          I would almost agree with you, but I painstakingly copied 5 lines to a new text file (properly formatted in which I entered my own carriage returns) and it still returns a blank line in between each line of data.  I have a custom dialog where I am using the GetValue() and Middle() and you can plainly see that GetValue() is returning a blank line between each line of text. 

                           

                          Nevertheless, I'll try the substitute() and see if it fixes anything.  Thank you for the idea.

                          • 10. Re: Parsing text?
                            comment

                            kacike wrote:

                             

                            The fact that you dont see multiple carriage returns does not mean they are not there.

                             

                            For example?

                            • 11. Re: Parsing text?
                              kacike

                              3 Carriage Returns

                               

                               

                               

                               

                              1-16-2013 9-54-25 AM.png

                              • 12. Re: Parsing text?
                                jjfcpa

                                Michael

                                 

                                Yes, that's a good idea - how many carriage returns are showing up... I'll test right now.

                                 

                                OK, I created a little test 5 from the main file with only 5 lines of data in it.  When this line is processed (just like my main one with over 6,000 lines in it) returns a good line of text and a blank line in between when using GetValue().  When I use Pattercount() to see how many carriage returns are in the file, it returns 5 and that's how many lines of data are in the file.  So I'm thinking it's not my data. 

                                 

                                I'm not sure it matters, but I'm reading this from a variable, not a text field?

                                • 13. Re: Parsing text?
                                  jjfcpa

                                  Michael & kacike

                                   

                                  Found something interesting that may be a different issue.  If I use Pattercount(), I get 5 carriage returns, if I use ValueCount() I get 10.  Shouldn't they be consistent?

                                   

                                  Perhaps I do need to run the Substitute() command and replace the double CR's with single CR's.  I'm gonig to try that next but have to get to the office.  I'll report back just FYI.

                                  • 14. Re: Parsing text?
                                    comment

                                    jjfcpa wrote:

                                     

                                    When I use Pattercount() to see how many carriage returns are in the file, it returns 5 and that's how many lines of data are in the file.  So I'm thinking it's not my data. 

                                     

                                    I am afraid this is not going anywhere. What's the point of asking for advice if you're not willing to take it?

                                     

                                     

                                    ---

                                    EDIT:

                                     

                                    jjfcpa wrote:

                                     

                                    If I use Pattercount(), I get 5 carriage returns, if I use ValueCount() I get 10.  Shouldn't they be consistent?

                                     

                                    Ah, now you're getting somewhere.

                                     

                                    jjfcpa wrote:

                                     

                                    Perhaps I do need to run the Substitute() command and replace the double CR's with single CR's.

                                     

                                    That won't do any good, since there are only 5 of them. You have another character in there causing havoc.

                                     

                                     

                                     

                                     

                                    Message was edited by: Michael Horak

                                    1 2 Previous Next