ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a time due to scheduled maintenance on Thursday, April 22nd. Please plan accordingly.

Alteryx Designer Discussions

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

Parse text 1 row into multiple rows

jimmytpnguyen
5 - Atom

Hi there,

 

I'd like to read text from input data then based on the values convert into multiple rows, see below.

 

Thanks,

Jimmy

 

Input Data
[5510,5511,5551,6701,6700,8544,5512,5513]
 
Output Data
5510
5511
5551
6700
8544
5512
5513
ctthornb123
7 - Meteor

I'm not sure if this needs to be dynamic but I think you just need to clear the brackets using a replace formula, then use text to columns tool to split to columns by using the comma as a delimiter.  Then transpose all the data and clear out the nulls.  I set the text to columns tool to 25 columns but this can be adjusted depending on the number of records you may need to account for in the data.  However, the # of columns cannot exceed 1000 so we will need to look at a different solution if that's the case.

 

See attached workflow

danilang
17 - Castor
17 - Castor

Hello @jimmytpnguyen

 

The solution the @ctthornb123 provided will work, but there is a more dynamic way to split to rows.  Use the Text To Columns tool and configure it to split to rows.  This will place each item on a new row with out requiring the transpose tool. This is also an option with the RegEx tool.

 

Split to rows.png

 

To remove the brackets you can use Trim(field1,"[]") in a formula tool.

 

Split to rows wf.png

 

See the attached for the complete workflow.

 

Dan

jimmytpnguyen
5 - Atom

Great! thank you so much.

Labels