Special Characters substitution in column names

[#1]

Hello All, I am trying to substitute a simple underscore ( _ ) into each column name that has any conceivable "special" character without having to do individual if statements for each character.

Say column 1 is labeled Test1%good#parts, column 2 is labeled Test2*Fail#Opens, etc

I want the renamed column names consistent- Test1_good_parts, Test2_Fails_Opens, etc.. The data in the columns are counts (numeric and continuous or ordinal).

I've tried versions of:

spec_char = or(" ","~","`","!","@","#","$","%","^","&","*","(",")","-","+","\","|","[","[","]","<",">","?","/","."  // no underscore here

);

dt = current data table();

col_name_list = dt<< get column names( string );

nc = N Cols( dt );

For( i = 1, i <= nc, i++,

  one_col_name = col_name_list;

  If(

  Contains( one_col_name, eval(spec_char) ),

  Column( i ) << set name( Substitute( one_col_name, eval(spec_char) , "_" ) )

  );

  );

But rep is a pattern where eval(spec_char) is looking for a character.

Is there a clean way to perform this type of group substitution?

Thanks.

[#2 · Accepted solution]

Here is a script that will do what you want:

Names Default To Here( 1 );

dt = Current Data Table();

colnames = dt << get column names( string );

For( i = 1, i <= N Items( colnames ), i++,

       wordcount = 1;

       thenewcolname = "";

       While( Word( wordcount, colnames, " !@#$%^&*()-+[]<>?/\|" ) != "",

              If( wordcount > 1,

                     thenewcolname = thenewcolname || "_"

              );

              thenewcolname = thenewcolname || Word( wordcount, colnames, " !@#$%^&*()-+[]<>?/\|" );

              wordcount = wordcount + 1;

       );

       Column( dt, colnames) << set name( thenewcolname ) ;

);

[#3 · Accepted solution]

The Regex function should work well for this:

dt = Current Data Table();

For(i = 1, i <= N Col(dt), i++,

    Column(dt, i) << set name(Regex(Column(dt, i) << get name, "\W", "_", GLOBALREPLACE))

);

[#4]

Thank you both MS and Jim, both work very smooth!

Rob M.