Could someone let me know to how get rid of unknown characters from the text file.

[vgeetanp]

For Example :

I have a text in my text file (below)  wherein while importing it into the workflow I need to view it by ignoring the unknown character. Refer Below.

 

          To 

Is it easy to do ? Thanks.

[vgeetanp]

Thank you Krishna. The Data Cleansing helps to get rid of the unwanted character BUT it removes all the extra spaces too, which makes the column separation task tough. 

ms dhoni Y Y N

Virat Kohli Y N

Raina Y N

Would you be able to assist me on how to get this done.? The txt file and the expected output is attached in the above reply.

Thanks in advance.

[vgeetanp]

Thank you Krishna.. The Data Cleansing helps me to get rid of the unwanted character BUT it removes the extra spaces (refer below image) too, which makes the column separation task tough. 

Would you be able to get this done ? The txt file and the expected output is attached in the above reply.

Thanks in advance.

[joshbennett]

Interesting find, @KrishnaKuchibhotla - it seemed somewhat strange to me that the Data Cleansing tool is removing this character with the 'Tabs, Line Breaks, and Duplicate Whitespace' setting. After some playing with this in regexr.com, I discovered this pesky little character is a "form feed" character, which apparently is also covered by "\s+" in RegEx (which is what the Data Cleansing tool uses for the 'Tabs, Line Breaks, and Duplicate Whitespace' setting, which is why this solution appeared promising).

@vgeetanp  - given this, an alternative to using the Data Cleansing tool which is removing additional characters you wish to keep would be to use the following formula:

regex_replace([Field_1], "\f", "")

The "\f" (i.e., representing "form feed" characters) is what targets your specific undesired character, while leaving the rest of your data as is (though I did notice that it also appears to convert null records to empty / blank records, but this didn't seem like it would be a deal breaker). 

Workflow attached for your reference - hope this helps.

[vgeetanp]

Woow @joshbennett .. That's Perfect!! This is what I looked for.. Thank You..

I would like to ask one more favor.. Could you please let me know the best practice to convert the given text file into the expected output. Both files are attached previously.

Output Required:

 Thanks in advance.

[joshbennett]

Assuming your input file(s) have the same report header (rows 1-7), this should do the trick:

See attached for workflow - boom!

[vgeetanp]

Thanks for the swift response. I almost got it done.. BUT have one small issue. The last fields seems to be taking incorrect values. 

The output should be : 

Whereas your solution gives the below :

Could you please check that and share me the updated solution ?

[joshbennett · Accepted solution]

Third time's the charm.

[vgeetanp]

Thanks alot buddy @joshbennett .. You are a STAR!

[joshbennett]

Any time - and gee, thanks.