Cleaning unstructured data in load script

[pduplessis]

I am building a QV app on a CRM database that contains some notes fields. However the data in the notes field comes into QV with some "fomatting" that makes the notes illegible in QV.

for example: "{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}\viewkind4\uc1\pard\f0\fs17 This message is a test message in the history detail.\par}"

What I only need is: This message is a test message in the history detail.

Does anyone have any ideas of what string functions I could use to clean this data. I know, there probably isn't a silver bullet to solve this.

best regards

[pduplessis]

I have discovered that the text I want is always after the 21st "\". So, I will use the "\" as a delimiter as such, and get the text between the 21st and 22nd "\".

Are there any suggestions for the code I could use?

thanks

[johnw · Accepted solution]

I think this:

subfield(MyInputField,'\',22) as MyOutputField

Edit: Looks like you also want to exclude fs17, even though it's between the 21st and 22nd '\'. Maybe this:

subfield(subfield(MyInputField,'\',22),' ',2) as MyOutputField

[pduplessis]

Thanks for your response.

So far I have made some progress with TextBetween( fieldname, '\', '\',21) followed by Purgechar( fieldname, 'fs22').

but I'll try your suggestion too.

thanks.

[johnw]

You don't want to use purgechar(). It will remove all of the characters in the list, not just 'Fs22' itself. So the comment "I Caught 2 Fish Today" would turn into "I Caught ih Today".

[pduplessis]

wow, thanks for catching that. I got busy on something else, and haven't checked back.