"Foreign" text in a field

[datanibbler]

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

[morganaaron]

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?

[marcus_sommer · Accepted solution]

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

[MarcoWedel]

maybe chr(10) also

[datanibbler]

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

[datanibbler]

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).