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

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

Benson — Tue, 12 Jun 2018 07:06:20 GMT

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

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

txnelson — Tue, 12 Jun 2018 10:12:26 GMT

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

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

Benson — Tue, 12 Jun 2018 10:28:10 GMT

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

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

pmroz — Tue, 12 Jun 2018 13:52:11 GMT

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, "', '") || "')";

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

Benson — Wed, 13 Jun 2018 03:31:15 GMT

This is useful :D