cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
A Script Box to Convert Pasted Lists into Comma Separated Format
jay_holavarri
Level III

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 );

);

11156_scriptbox_paste_SQL.jpg

Comments

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.

11887_Screen Shot 2016-06-24 at 5.44.44 PM.png

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