244529_en-US

liliconcom

String to data table (code optimization)

An .exe file within a loop returns a string a need to convert (efficiently) to a data table.

 

The string has the following characteristics:

 

• Rows are limited by "\n" characters, although this can be changed if needed
• Columns are limited by ","
• The begging and the end of the string has rows one needs to eliminate
• The datetime rows can vary. In the example one can see 7 but these can go up to 100k rows.
• This operations will be repeated within the loop in the order of hundreds (different strings but same number of rows)

 

 

str_query = 
"*> mycolumnname
1,mycolumnname,50.,mycolumdescription,mycolumnunits
* End exe...
*> mycolumnname, 31-Jan-2020 14:30:00, 31-Jan-2020 15:30:00, blabla, 7
31-Jan-20 14:30:00,mycolumnname,10.60466
31-Jan-20 14:40:00,mycolumnname,10.60466
31-Jan-20 14:50:00,mycolumnname,10.60466
31-Jan-20 15:00:00,mycolumnname,10.60466
31-Jan-20 15:10:00,mycolumnname,10.60466
31-Jan-20 15:20:00,mycolumnname,10.60466
31-Jan-20 15:30:00,mycolumnname,10.60466
* End exe...
...
...
...

...
";

 

 

How can I transform this string into a data table with the following (stacked) format:

 

31-Jan-20 14:30:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 14:40:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 14:50:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:00:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:10:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:20:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits
31-Jan-20 15:30:00,mycolumnname,10.60466,mycolumdescription,mycolumnunits

Which JSL functions can manage these string to data table operations efficiently (memory and/or CPU wise?).

 

Multi-threading or multiprocessing cannot be used this is querying a database.

 

 

 

 

Re: String to data table (code optimization)

FN,

Curious to know if you tried Craig's approach?  Did it work for your example?

-B

Re: String to data table (code optimization)

...and you can still pass the CSV parameters to that open statement. If the output has extra lines at the beginning that you can't fix, you can tell the open statement about the first data line, and the presence of column names, and what data types to use. If the data is more than a few lines, it may be noticeably faster to tell JMP everything that is in the source script so JMP doesn't make an extra pass to guess the format. The source script can be found in a table JMP already imported.

Re: String to data table (code optimization)

Cool! RunProgram can return a blob. The open() function can accept a blob in place of a file name. If you can get the external program to create the CSV as its printed output (on stdout) it should work.

example here: https://community.jmp.com/t5/Discussions/Load-CSV-from-variable/m-p/208453

 

Re: String to data table (code optimization)

Yes, it is almost a string with a CSV formatting, and the output can be tuned to certain extent.

 

The current code is using RunProgram() to get the output string, then Words() to obtain a list and then TextToColumns().

 

Can we Open() a string directly? It seems open is for files only, which ideally should be avoided.

 

Re: String to data table (code optimization)

your format looks a lot like a CSV file. the open() function understands CSV data, and if you give it enough information about the columns and data types, it can be quite efficient (both your development time and your waiting time.)

How are you getting the data from the .exe program to JMP? (flat file? runProgram()? open()? something else?)

 

Re: String to data table (code optimization)

Maybe putting the two parts into lists is a way to get started?

clear globals();

query = 
"*> mycolumnname 1,mycolumnname,50.,mycolumdescription,mycolumnunits
* End exe...
*> mycolumnname, 31-Jan-2020 14:30:00, 31-Jan-2020 15:30:00, blabla, 7
31-Jan-20 14:30:00,mycolumnname,10.60466
31-Jan-20 14:40:00,mycolumnname,10.60466
31-Jan-20 14:50:00,mycolumnname,10.60466
31-Jan-20 15:00:00,mycolumnname,10.60466
31-Jan-20 15:10:00,mycolumnname,10.60466
31-Jan-20 15:20:00,mycolumnname,10.60466
31-Jan-20 15:30:00,mycolumnname,10.60466
* End exe...
...
...
...

...
";

query=Substitute( query, "*> mycolumnname ", "colnames=list(" );
query=Substitute( query, "* End exe...", ");" );
query=Substitute( query, "*> mycolumnname, ", "coldata=list(" );
query=Substitute( query, "* End exe...", ");" );
for(i=1, i<=5, i++, query=Substitute( query, "...", "" ));

show(query);<BR /><BR />

What have you tried?