Fixing spaces used as a delimiter and removing repeating delimiters

[tpeikin]

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.

[PhilipMannering · Accepted solution]

Tricky one. But you could try replacing more than 1 space (say, 2 or more) with a pipe and then split on a pipe.

See attached.

[Qiu]

@tpeikin 
It appears that Alteryx never ceases to surprise us.

A simple Cleansing Tool might do the trick already.

---

So, a few questions...

First, am I doing this the hard way?

There is only good way, I think

Second, what's the best method for turning multiple (and varying) repeating values into to a single one?

Cleansing Tool for this case?

Last, should I have the SQL guy spend 2 days redoing everything? 🙂

Dont be too bossy. but if I do help, A cup of coffee should be in place.😁

---

[PhilipMannering]

@Qiu That's a good idea. It won't help if you have field names with a space though right?

[Qiu]

@PhilipMannering 
You are correct.
Hopefully the Database "Specialist" would not put space in Field Names.

[RobertOdera]

Hi there, @Qiu @tpeikin @PhilipMannering 

To remove spaces from Field Names, if that's also getting in the way, use the Dyanmic Rename Tool --> Formula --> TRIM, per snippet below. Cheers!

[tpeikin]

Well, similar difficulties have happened with our Sr. DBA and other analysts. That's why I'm getting better at Alteryx!😉

[RobertOdera]

Awesome attitude @tpeikin!

Fantastic disposition.

Also, consider marking @Qiu  response as an acceptable solution.

You can mark more than one response as a solution :).

Cheers!

[Qiu]

@RobertOdera 
Appreciate your kindness.

@tpeikin 
Please if you find it my flow useful.