Split Comma-separated column into multiple columns

[SriHari52]

Hi there,

I need your help and suggestions with the following scenario. I have the following file 

ID_ left	ID_ Right	Names_ left	Names_ right
1	6	AA,BB,CC	DD,FF
2	7	DD,EE,FF	HH,LL
3	8	GG,HH	JJ,KK,BB

My requirement is to split these values different columns like below.

ID_ left	ID_ Right	Names_ left	Names_ right	#1_Left	#2_Left	#3_Left	#1_Right	#2_Right	#3_Right
1	6	AA,BB,CC	DD,FF	AA	BB	CC	DD	FF
2	7	DD,EE,FF	HH,LL	DD	EE	FF	HH	LL
3	8	GG,HH	JJ,KK,BB	GG	HH		JJ	KK	BB

I understand we can use text-to-columns to get this output, but let's say I have 500 columns and I have to de-limit those values? Can't think of a solution. Can anyone help?

[IraWatt]

Hey @SriHari52,

All you need is the text to column tool:

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[DataNath]

Perhaps a little long-winded but here's a dynamic approach whereby you can just provide a data set with N delimiters where N can be any number, and this ought to automatically handle that without needing to change the configuration each time:

[Maskell_Rascal]

Hi @SriHari52 

If its just the two columns with an unknown amount delimited values to split, either of the previous solutions should work for you. I'd opt to use two Text to Column tools with number of columns set to 1000 on each of them, then follow those up with a Data Cleanse tool that is configured to remove Null columns. 

If however, your need is to split an unknown number of columns, then this solution will suite you better. 

Create a Record ID, Transpose your data, Text to Columns to Spit to Rows, Tile tool for prepping the updated column names, Formula to create new column names, and then flip your data back using the Crosstab Tool. 

Attached is a sample workflow with both solutions for you to try. 

Cheers!

Phil

[binuacs]

@SriHari52 One way of doing this with the Transpose and Multi-Row tool