Extracting from a column that has comma delimited data

[#1]

I have a column fro a query that has data within that is comma delimited.

Reads

1,2,3,2,4,3,4,5,2,1,3,4,5,2,1,3,4,2,1,2,3,4,2,1,2,4,2,3,4,5

I would like to have a script that parses out each value into a new column with the column title of D01, D02, D03 etc....

I have used an addin for Text to Cols to work with this column but it would great to have a script as part of post-Query script that makes those columns

[#2 · Accepted solution]

There actually is a Text to Columns() function.  Here's a way to do it with a table where I start with 2 columns, Column 1 and Column 2.  Because Text to Columns() will insert the new columns directly after the column being split, you need to determine the position of the column so you can easily find where the new columns are in the table to rename them.

dt = Current Data Table();
col = "Column 1"; //name of column that needs to be split out

//Need to determine where new columns will go:
colnames = dt << Get Column Names(string);
which_col = Loc(colnames, col)[1]; //get position of column being split

//Get number of columns before split
n_col_pre = N Col(dt);

//Split the column by delimiter
dt << Text to Columns( delimiter(","), 
	columns(Column(dt, col))
);

//Determine number of new columns
new_cols = N Col(dt) - n_col_pre;

//Rename New Columns
for(i = 1, i <= new_cols, i++,
	col_pos = which_col + i; //get position of current column
	Column(dt, col_pos) << Set Name("D"||if(i < 10, "0","")||char(i));
	Column(dt, col_pos) << Set Data Type(Numeric); //makes each column numeric, not sure if you needed that too
);

Before:

After:

[#3]

It worked great!  Thank you.  Also thank you for the comments in the script they will help me understand it and learn from it.

Thanks again!

[#4]

glad it worked well for you!