topic Re: Extracting from a column that has comma delimited data in Discussions

Extracting from a column that has comma delimited data

UberBock — Tue, 10 Mar 2020 22:43:32 GMT

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

Re: Extracting from a column that has comma delimited data

cwillden — Wed, 11 Mar 2020 11:56:43 GMT

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:

Re: Extracting from a column that has comma delimited data

UberBock — Wed, 11 Mar 2020 13:28:39 GMT

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!

Re: Extracting from a column that has comma delimited data

cwillden — Wed, 11 Mar 2020 16:44:34 GMT

glad it worked well for you!