Alteryx designer Discussions

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

Searching and reporting on special characters in a spreadsheet

Meteor

We have spreadsheets for which we have to check for special characters amongst the data. We have to check for the following

 

&,  &#,  /~*,  --,  <,  >,  ‘,  “,  ’,  Carriage Return,  Line Feed,  #. \. '

(Commas excluded)

 

I would like to report in the form of a error messages like:

 

AG18 - Special Character "&'" found 

M14 - Special Character "Line Feed" found 

T16 - Special Character "Line Feed" found

 

(We can't modify the spreadsheet without permission, we're tasked with finding things like this and reporting back)

 

The only way I can think of doing this is to have a filter for each character and for each column. Since our spreadsheets go to Column AH I can't imagine creating 422 filter tools for each tab. There's got to be a better tool for something like this.

 

For all my other checks I do a filter tool, send it to a "Report Text" tool which provides the error message and on it goes to a Union, and a render.....

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@cjames728,

 

Think TRANSPOSE!  If your data is named with column headers, here's an example:

 

KEY|DATE|NAME

101|2017-12-19|Mark

202|2017-12-19|Mark & Gina

 

You can use a RECORDID tool and get a Row number and then TRANSPOSE the incoming data on ROW (as the key) and all other fields as data.  Now your output will be:

 

ROW|NAME|VALUE

1|KEY|101

1|DATE|2017-12-19

1|NAME|Mark

2|KEY|201

2|DATE|2017-12-19

2|NAME|Mark & Gina

 

Now you can use a FORMULA to create a new field via regular expressions to see what weird stuff you found.  FILTER on IsNull([STUFF]) and take the False output to see what was found where and in which rows.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Meteor

Absolutely Brilliant! That solved another (simpler) problem of how to report columns of missing required fields as well.

 

Many thanks!

Labels