Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.
SOLVED

How can I parse data that has the Escape square character in it?

Highlighted
Asteroid

Hi all,

 

I'm working with a dataset I am trying to parse out, but I'm not sure how to parse out this symbol. I've attached a screenshot of it below. There's the square Escape character and the number 3 I am trying to parse my columns by. Any ideas on how to do this?

 

Annotation 2019-04-12 133336.png

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @hydrogurl01 

 

Could you please post a sample of your data? At least one line containing the square character.

 

We could extract the ASCII or Unicode Character of this square to remove it with regex or even regular replace function.

 

Cheers,

Highlighted
Asteroid

Hi @Thableaus ,

 

I've attached some sample data here. I've never used ASCII or Unicode before so any detail on that would be helpful!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@hydrogurl01 

 

Try this:

 

ReplaceChar([Field], CharFromInt(3), "")

 

This will replace the \u0003 unicode character - which stands for the End of Text. I suppose this is your square character.

 

Cheers,

Highlighted
Asteroid

Thanks @Thableaus ! This got rid of the squares, however I still do have the 3 in here. I can't delimit by the number 3 because I have 3's elsewhere in my data that need to stay there. Any advice on how to navigate around this?

 

Also would you be able to explain the CharfromInt(3)?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@hydrogurl01 

 

Try parse method with RegEX tool using this:

(.*?)\x{0003}3(.*?)\x{0003}3(.*?)\x{0003}3(.*?)

 

\x{0003} stands for the Unicode square character.

 

CharfromInt(3) is a String function that returns a string based on its Unicode decimal value (in this case, Unicode value for the weird square is 3).


Cheers,

 

Highlighted
Asteroid

HI @Thableaus ,

 

This is almost there! For some reason the last comments column isn't showing up with this regex expression. I tried copying/pasting the same expression again at the end but then everything blanks out and I still don't see the last comments column. Any insight into why that last column isn't showing up?

Alteryx Certified Partner
Alteryx Certified Partner

@hydrogurl01 

 

Sorry:

 

(.*?)\x{0003}3(.*?)\x{0003}3(.*?)\x{0003}3(.*)

 

Change the expression, the last "?" character in RegEX is unnecessary.

 

Cheers,

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@hydrogurl01 

 

Did it work?


Cheers,

Highlighted
Asteroid

Worked perfectly!! Thank you so much for all the help!

Labels