ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

hydrogurl01
8 - 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

Thableaus
17 - Castor
17 - Castor

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,

hydrogurl01
8 - 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!

Thableaus
17 - Castor
17 - Castor

@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,

hydrogurl01
8 - 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)?

Thableaus
17 - Castor
17 - Castor

@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,

 

hydrogurl01
8 - 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?

Thableaus
17 - Castor
17 - Castor

@hydrogurl01 

 

Sorry:

 

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

 

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

 

Cheers,

Thableaus
17 - Castor
17 - Castor

@hydrogurl01 

 

Did it work?


Cheers,

hydrogurl01
8 - Asteroid

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

Labels