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
Benson
Level II

How do I make text box to a List for sql query

I am trying to make a text box to allow users to input information for sql query, and users copy this information from excel to text box. my question:

1. This length of Lot is fixed =14, and there are 11 lots in this example. Why the length of text box is 176?

2. How to  make this text into a list as {"18148018-01-00","18148018-01-01","18148018-02-00"......}

Names Default To Here( 1 );
//make a text box for user input
New Window( "Example",
 Outline Box( "Lot List Input",
  H List Box( Text Box( "=>" ), teb = Text Edit Box( "Please input your lots", <<Set Width( 200 ), <<set nLines( 20 ) ) )
 )
);

length(teb<<get text);

 

 

 

1.png

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: How do I make text box to a List for sql query

The issue is, that the paste buffer that you copied into from your Excel table, contains Carriage Returns(hex 0D) and Linefeed(hex 0A) at the end of each cell that was copied.  Below is the Hex representation of the list of lots, taken from the pasted Excel file.  It is actually all one long line of hex characters, but for illustration, I have broken the line of hex characters at the end of each lot

31383134383031382D30312D30300D0A
31383134383031382D30312D30310D0A
31383134383031382D30322D30300D0A
31383135383031312D30312D30300D0A
31383135383031312D30322D30300D0A
31383138383030322D30312D30300D0A
31383138383030322D30312D30320D0A
31383138383030322D30312D30330D0A
31383138383030342D30312D30300D0A
31383439383030372D30312D30300D0A
31383441383030352D30312D30300D0A

 Thus, 

176 = 11 X (14 + 2)

 

Here is a piece of code that will break the string into a list of strings

fullText = teb << get text;
fullTextList = {};
start = 1;
While( Substr( fullText, start, 14 ) != "",
	Insert Into( fullTextList, Substr( fullText, start, 14 ) );
	start = start + 16;
);
show( fullTextList );
Jim

View solution in original post

pmroz
Super User

Re: How do I make text box to a List for sql query

Here's another way to parse the information using WORDS.  Followed by using CONCAT ITEMS to create an IN list.

lot_text = 
"31383134383031382
31383134383031382
31383134383031382
31383135383031312
31383135383031312
31383138383030322
31383138383030322
31383138383030322
31383138383030342
31383439383030372
31383441383030352";
crlf = "
";
lot_list = words(lot_text, crlf);
lot_in_list = "('" || concat items(lot_list, "', '") || "')";

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: How do I make text box to a List for sql query

The issue is, that the paste buffer that you copied into from your Excel table, contains Carriage Returns(hex 0D) and Linefeed(hex 0A) at the end of each cell that was copied.  Below is the Hex representation of the list of lots, taken from the pasted Excel file.  It is actually all one long line of hex characters, but for illustration, I have broken the line of hex characters at the end of each lot

31383134383031382D30312D30300D0A
31383134383031382D30312D30310D0A
31383134383031382D30322D30300D0A
31383135383031312D30312D30300D0A
31383135383031312D30322D30300D0A
31383138383030322D30312D30300D0A
31383138383030322D30312D30320D0A
31383138383030322D30312D30330D0A
31383138383030342D30312D30300D0A
31383439383030372D30312D30300D0A
31383441383030352D30312D30300D0A

 Thus, 

176 = 11 X (14 + 2)

 

Here is a piece of code that will break the string into a list of strings

fullText = teb << get text;
fullTextList = {};
start = 1;
While( Substr( fullText, start, 14 ) != "",
	Insert Into( fullTextList, Substr( fullText, start, 14 ) );
	start = start + 16;
);
show( fullTextList );
Jim
Benson
Level II

Re: How do I make text box to a List for sql query

Jim, Thanks for explantion. I  consider there will be 2 extra characters form excecl with copy/paste, and Now I could make a list :)

pmroz
Super User

Re: How do I make text box to a List for sql query

Here's another way to parse the information using WORDS.  Followed by using CONCAT ITEMS to create an IN list.

lot_text = 
"31383134383031382
31383134383031382
31383134383031382
31383135383031312
31383135383031312
31383138383030322
31383138383030322
31383138383030322
31383138383030342
31383439383030372
31383441383030352";
crlf = "
";
lot_list = words(lot_text, crlf);
lot_in_list = "('" || concat items(lot_list, "', '") || "')";
Benson
Level II

Re: How do I make text box to a List for sql query

This is useful :D