Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Fixing spaces used as a delimiter and removing repeating delimiters

tpeikin
5 - Atom

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.

8 REPLIES 8
PhilipMannering
16 - Nebula
16 - Nebula

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
20 - Arcturus
20 - Arcturus

@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.😁


 

1117-tpeikin.PNG

PhilipMannering
16 - Nebula
16 - Nebula

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

Qiu
20 - Arcturus
20 - Arcturus

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

RobertOdera
13 - Pulsar

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!

DynamicRename_Eg.PNG

tpeikin
5 - Atom

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

RobertOdera
13 - Pulsar

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
20 - Arcturus
20 - Arcturus

@RobertOdera 
Appreciate your kindness.

@tpeikin 
Please if you find it my flow useful.

Labels