A Script Box to Convert Pasted Lists into Comma Separated Format

[#1]

I use a custom JMP interface to pull data from a database with the Open Database command. It is a common problem with query tools that you have a list of something in a data table or spreadsheet (part id's, for example) but are required to convert it into a comma separated list (along with the addition of single quotes) to work with an SQL statement.

I could not find a standard JMP box that would do what I wanted, and I wound up using the Script Box. The intent of that box is, of course, to run scripts, but it automatically creates a comma separated list with the << Get Lines message. The function is shown below, and the attached script shows it in action. There are probably other uses for converting a pasted list like this.

fn_scriptBox = Function( {_vv}, //formats the script box output for SQL

  _yy = _vv << Get Lines;

  _zz = "'";

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

  _zz ||= _yy || "','"

  );

  _zz = Left( _zz, Length( _zz ) - 2 );

);

[Nate_Riordan]

The concat items() function is a nifty way to take lists and turn them into strings as well.  When I was writing code for SQL injection I found it to be faster than looping.

[jay_holavarri]

It took me 'til now to realize you were giving me a useful suggestion I could drop right into this script. I can indeed simplify the script with Concat Items.

From this:

fn_scriptBox = Function( {_vv}, //formats the script box output for SQL

  _yy = _vv << Get Lines;

  _zz = "'";

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

  _zz ||= _yy || "','"

  );

  _zz = Left( _zz, Length( _zz ) - 2 );

);

  //end fn_scriptbox

To this:

fn_scriptBox = Function( {_vv}, //formats the script box output for SQL

  _yy = _vv << Get Lines;

  _zz = "'" || Concat Items (_yy, "','") || "'";

);

Thanks Nate. I didn't test whether it was faster, but I believe you. I'll see if I can figure out how to rev this little example.

jh