5 Replies Latest reply: Nov 4, 2014 7:41 AM by Friedrich Hofmann RSS

    "Foreign" text in a field

    Friedrich Hofmann

      Hi,

       

      I have an issue in a subroutine used to identify whether records are clean or have to be processed.

      One part of that routine is identifying "foreign" text - any text that should not be there.

       

      For that, I use the following code (in a LOAD):

      >>> LEN(PurgeChar(Mat_pre, ' /-0123456789STUECKstueckÜüPp.')) <<

      That covers all possible variations of 'Stueck' (or 'Stück' or 'Stk' or even 'pcs')

       

      The issue is: I have records in those Excel_lists (exported from a customer_tool) where there are three lines in one (like you can do in Excel by hitting "Alt+Enter").

      Usually then there is text in all of those lines. Sometimes, there is not - strangely, there are two line_breaks in a cell at times, with only one of those three "virtual" lines filles.

      However, that should not be an issue - my code should be able to capture that.

      What irritates me is that in two of those cases - where I have two linebreaks in one cell, but actually no text (except the word 'pcs' in one), that code reports a length of 2, meaning 2 "foreign" characters. I tried out without the LEN() to see exactly what those are and got - nothing.

      Can anybody give me a hint as to what character can be there? Is a linebreak maybe counted as a character? -> If so, how can I include that in my Purgechar()_statement?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: "Foreign" text in a field
          Aaron Morgan

          You could try adding: chr(13) into the PurgeChar statement to see if this is picking up any carriage returns? Outside of the single quotes, so try '/-0123' & chr(13) and see if they are the issue?

          • Re: "Foreign" text in a field
            Marcus Sommer

            Hi DataNibbler,

             

            len() should count every character und you could use ord() to return which character is be left. I'm not sure if it worked but you could create unvisible characters like a line-break in xls and use copy+paste to include these characters in your purgechar().

             

            Maybe you need to extend your logic for a replace() or trim() part.

             

            - Marcus

              • Re: "Foreign" text in a field
                Friedrich Hofmann

                Hi,

                 

                I tried it out and when I include a TRIM() around the contents of the field,

                - the "empty" line_breaks are gone and

                - the "foreign" text also

                => so I guess it was the chr(10).

                I will try out how it works out in the larger context of the entire code, but I guess that should do it.

                Thanks a lot!

                Best regards,

                 

                DataNibbler

                  • Re: "Foreign" text in a field
                    Friedrich Hofmann

                    Hi,

                     

                    I have just found out that because I tested on just one set of possible base_lists and then on another, not "switching backward" so to speak, I have overlooked something:

                    - In that subroutine, I have two checks: One for linebreaks within a field and one for "foreign" text

                    - In a subsequent check, records with >=1 "foreign" character will be disregarded and an alarm will be triggered so 
                       that someone will have a look and clean them manually.

                    <=> I just noticed that when there are linebreaks AND several lines actually filled in that one record, then there are also "foreign" characters, so it's probably the chr(10).

                    So I will now try out Aaron's suggestion with the chr(10).