6 Replies Latest reply on May 8, 2010 6:34 PM by LaRetta_1

    Need help parsing a text that has no delimiters

    TKnTexas

      Title

      Need help parsing a text that has no delimiters

      Post

      I am stuck parsing out a text file. I have figured out how to mark only the lines of the text that are truly data lines.  I have been able to eliminate the fluff lines, i.e. headers, blank lines, footers, etc.  

       

      The report is an AP Edit Listing.  I can parse out the parts of the data lines that are the:

      VOUCHER NUMBER,

      VENDOR NUMBER,

      VENDOR NAME,

      INVOICE NUMBER, and

      INVOICE DATE.

       

      Where I am having a problem is the invoice amount.  The specific problem is the credit memos.  When the report is written it has two oddities.  The first is that the numbers are left justified, not right justified.  Decimals are not aligned.  The second is that the minus sign is at the end of the number, not the beginning. 

       

      My calculation that best works is Middle (input_line, 68, 12).  i get the number and the minus sign at the end.  If i set the field to text, it shows the number as 279.02-.  If I change it to show as number, it ends up 279.02.  When I use a summary field to total it, it is off exactly by the amount of treating the credit as a debit. 

       

      Any suggestions?  I am really stumped. 

       

        • 1. Re: Need help parsing a text that has no delimiters
          LaRetta_1

          Is the number always a negative?  Are there always two decimal places? Is there possibility that decimal (period) or dash (minus) appears elsewhere in the string?

          • 2. Re: Need help parsing a text that has no delimiters
            comment_1

            I didn't understand the issue of decimals alignment.

             

             

            The sign problem could be solved by =

             

            Let (
            t = Middle ( input_line ; 68 ; 12 )
            ;
            Case ( Right ( t ; 1 ) = "-" ; - GetAsNumber ( t ) ; GetAsNumber ( t ) )
            )

            • 3. Re: Need help parsing a text that has no delimiters
              LaRetta_1

              The reason I am asking is because I was unsure how you would always know the length of the number.  Your example is 7 but you indicate a firm 12 for the length of the number and because you indicate it is 'left aligned' it seems that it might be padded with spaces at the end?  I was then looking for another way to verify the length of the actual number plus 1 for the possible dash.

               

              If the number is indeed padded with trailing spaces (or even zeros) then testing for right 1 will fail unless the number takes up 11 characters so the 12th can be tested for the minus.  If this might fail (and knowing Comment it probably won't), you might protect by removing the spaces first, as:

               

              Let ( [
              t = Middle ( input_line ; 6 ; 12 ) ;
              n = Substitute ( t ; " " ; "" )
              ] ;
              Case ( Right ( n ; 1 ) = "-" ; - GetAsNumber ( n ) ; GetAsNumber ( n ) )
              )


              • 4. Re: Need help parsing a text that has no delimiters
                comment_1

                 


                LaRetta wrote:

                you indicate a firm 12 for the length of the number and because you indicate it is 'left aligned' it seems that it might be padded with spaces at the end? 


                 

                That's a good point. So how about =

                 

                Let (
                t = Trim ( Middle ( input_line ; 68 ; 12 ) )
                ;
                Case ( Right ( t ; 1 ) = "-" ; - GetAsNumber ( t ) ; GetAsNumber ( t ) )
                )


                • 5. Re: Need help parsing a text that has no delimiters
                  TKnTexas

                  I think there is a fixed length to the field.  If I can find out from the application specs.  Or maybe it is a maximum length for the number field.  There are other number columns on the report to the right of the invoice amount.  The discount calculation.  However, at this point of the cycle the amounts are all zeros. 

                   

                  Most of the numbers will be debits, indicating invoices to be paid.  Credit memo are the small number of transactions.

                   

                  I will give these suggestions a try.  I appreciate the quick response, especially on a weekend.

                  • 6. Re: Need help parsing a text that has no delimiters
                    LaRetta_1

                    Of course!  Much better than mine, Michael!  I began to use Substitute() so it would be easier to replace with a zero (or if it was padded with another character).  But if padded with zero (and the number contained a zero) then Substitute() would have changed the number.  :smileyvery-happy: