A new guy on our SQL team pulled a massive export of 60 different files. Unfortunately the output files have spaces as delimiters, and in varying amounts. For example, one file only has 10 actual fields, but each field has multiple spaces separating them, and each value in that field has multiple trailing spaces, as if someone tried to create aligned columns in Notepad with the space bar, like this.
Field1 Field2 Field3 Field4
value1 value2 value3 value4
I got errors trying to use \s as a delimiter on the input tool due to the inconsistent number of delimiters. I used "\n" instead, so Alteryx will at least read it without error. Then I added a formula to replace all spaces with pipes to attempt creating a single delimiter. However,each record has a different number of pipes separating fields. I now want to remove all duplicate strings of pipes leaving only one left as the delimiter.
So, a few questions...
First, am I doing this the hard way?
Second, what's the best method for turning multiple (and varying) repeating values into to a single one?
Last, should I have the SQL guy spend 2 days redoing everything? 🙂
THANKS FOR YOUR HELP.
