Alteryx Designer

Share your Designer product ideas - we're listening!

Text to columns, create as many columns as there are delimiters

I love this tool,  but think it would be improved by including an option to create a column per delimiting character.  This could be added in the number of columns selector box.  In the case where 1 row has more delimiters than another, null columns can be created.  Without this option you have to Regex count the delimiters, select the max and then embed the Text to columns tools in a macro and then pass the max columns as a param.  Would be nice to resolve all this in the main tool.

 

Thanks, nick

5 Comments
9 - Comet

Great idea - create as many columns as the record with the most delimiters. This would be especially useful for times when the number of delimiters changes in the source data.

7 - Meteor

@spainn i've always wanted this, too!

 

there is another workaround for this that's less complex than creating a macro (RecordID>>Text to Columns::split to rows>>Run Total>>Cross Tab::Row=RecordID;Col=RunTotal>>Dynamic Rename::First Row as Field Names). that said, it adds tools that are contextually unrelated which can distort a workflow's "readability."

Alteryx Certified Partner

Totally agree with this idea. The use case I ran into this was batch downloading different tables through an API.

 

That being said, the most flexible and still easy to understand workaround, to me, is a simple macro:

Capture.PNG

 

I don't like to pivot/unpivot, doesn't work out well performance wise. All you need besides this macro is a single row to count your N for the Constant input.

Moderator
Moderator
Status changed to: Under Review

Thank you for your post! Our product team is currently working through evaluating the technical requirements behind implementing this idea. While this is occurring, which can take some time, I'm updating this idea to Under Review. Once our team can better speak on if or when we could implement this idea I'll update the idea accordingly!

I can relate to this issue and would love to see this included as a feature!

 

In the meantime, here is how I solve this without using a macro:

MuralidharAreti_0-1586908804038.png

Text to Columns put in an absurd number for the number of columns. If your data grows past this you will have to revisit this. 

 

Dynamic Rename brought the first row of data into the field names (because that is how this data set is structured)

 

Dynamic Select to eliminate fields that start with "Field_". This will keep only the fields that have data and remove the null columns.