Dividing field into columns with non ordered values

[ievpal]

Hi,

I have a field in my data, which I want to divide into columns. However, I cant use Text to Columns tool as data in my columns is not ordered.

I already have list of columns that should be made.

Sample of my data:

Tags

RUNS:1,VERSION:7.2,OUTPUT:TXT,

VERSION:7.1,RUNS:1,OUTPUT:CSV,

RUNS:1,OUTPUT:CSV,VERSION:7.2,

OUTPUT:CSV,OWNGROUP:Y,VERSION:7.1,REASON: OTHER

Result I want to get:

RUNS	VERSION	OUTPUT	OWNGROUP	REASON
1	7.2	TXT
1	7.1	CSV
1	7.2	CSV
	7.1	CSV	Y	OTHER

[patrick_digan · Accepted solution]

@ievpal I've certainly overcomplicated the matter, but the key for my approach was using the unique tool. A simpler approach isn't immediately coming to mind.

[MarqueeCrew · Accepted solution]

@ievpal,

I got a little more sleep than @patrick_digan.  Here's my solve:

I used multiple passes for parsing the data (rows and then columns) and then brought the data back together in a crosstab (naturally orders fields alphabetically).  I then used a select to reorder the columns and remove a RecordID field.

Cheers,

Mark

[ievpal]

Thank you both on a quick reply! :)

[lminors · Accepted solution]

Another slightly complicated solution. Dividing workflow up into RUNS, VERSIONS etc and then combining the fields and parsing on the colon.