community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Deleting or Collecting Records with Errors

Atom

Hello everyone,

I'm trying to clean some data that's been a bit poorly formatted. The data is tab separated, but formatted in such a way that sometimes there is an extra tab, which then results in a warning of either "Too many fields in record," or "Not enough fields in record," if a value is pushed to a new row.


Ideally, I'd like to figure out a systematic way to find these extra tabs, and delete them; returning the rows to their proper format. As this seems like it would be rather difficult, I'd settle for the simpler solution of simply deleting these problematic rows, or of collecting the warning/error messages and then using them to filter the rows out.


I've been looking through the forums here and haven't had much luck in finding a solution to something that would seem to be rather simple. If someone could relieve me of my current ineffectual disposition by either helping me with the simpler solution of deleting the problematic rows or collecting the errors/warnings, or the more ideal solution of removing these extra tabs systematically, I would be most appreciative.

Thanks, everyone!
-Nick

Highlighted
Alteryx Certified Partner

Hi Nick,

 

I may be oversimplifying your problem, but maybe this can lead you in the right direction...

  1. Input the file as a fixed width flat file
  2. Use a formula and the REGEX_Replace function to find any instance of one or more tabs and replace it with one tab.
  3. Write back out to a new (or overwrite old) tsv file.

See attached example.  Hope this helps.

Labels