topic Session 8: Working with external files & Databases in JMP Scripters Club Discussions

Session 8: Working with external files & Databases

maria_astals — Mon, 10 Jul 2023 07:02:51 GMT

Working with external files & Databases
Text files, excel files, Zip files, SQL embedded in your JSL

With: @jthi and @maurogerber.

Video 1:

(view in My Videos)

Video 2: Examples

(view in My Videos)

Re: Session 8: Working with external files & Databases

jthi — Fri, 07 Jul 2023 08:40:56 GMT

Regarding Parse XML. You can find working example from JMP's Sample Scripts. You can open the folder by running following command in the JMP Script Window

Open("$SAMPLE_SCRIPTS")

It might be possible to simplify the example, but at least it seemed to get the sheetname correctly

sheetname = XML Attr(
	"urn:schemas-microsoft-com:office:spreadsheet^Name",
	"Untitled"
);
dt = New Table( sheetname );

Re: Session 8: Working with external files & Databases

jthi — Mon, 10 Jul 2023 06:39:48 GMT

It seems like @maria_astals post contains same .zip twice. Could you Maria update the starting post with the attachment I have added here?

Also, would it be helpful if the scripts were to be posted also as comments in a spoiler? Of course the scripts won't work if there are external files being used, but depending for example how the search on community works, it could maybe be seen there?

Script from the session below:

//##################################################################################
//###
//### 8. Working with external files and databases
//###
//##################################################################################
// In this file we will cover:
// - Short recap of session 7 (scope/namespaces and report layer)
// - .xlsx
// - text files
// - xml files
// - json files
// - GET Request

// - DLL (See Session 5)
// - BATCH FILES (See Session 5)

// - Databases (using .sqlite database)
// - pictures
// - pdf


//##################################################################################
//### Recap of session 7 (Scope properties and report layer)
// https://www.jmp.com/support/help/en/17.0/#page/jmp/advanced-scoping-and-namespaces.shtml#ww339298
// https://www.jmp.com/support/help/en/17.0/#page/jmp/best-practices-for-advanced-scripting.shtml#
// https://www.jmp.com/support/help/en/17.0/#page/jmp/show-symbols-clear-symbols-and-delete-symbols.shtml
//##################################################################################

// Scope and namespaces
// Start your scripts with 
Names Default To Here(1);
// Enabling the Names Default To Here mode associates a scope called Here with an executing script

// Do not leave following to production code as they will break other scripts
Clear Symbols();
Clear Globals();
Delete Symbols();
Delete Globals();

// To see which messages object has you can use Show Properties(obj);

// Report layer
// To explore report layer use obj << Show Properties; and obj << Show Tree Structure; (obj << Get XML;)
// Get access to report layer using rep = obj << Report; or Report(obj);

// Report subscripting (returns first match)
param_tratio2 = biv_report[Outline Box("Parameter Estimates"), Number Col Box("t Ratio")];

// Use obj << Get XML; to see how XML looks before using XPath

// XPath (returns a list of matches AND you can send messages to a list)
biv_report << XPath("//OutlineBox[contains(text(), 'Bivariate Normal Ellipse')]");


//##################################################################################
//###
//### Working with external files
//###
//##################################################################################

//##################################################################################
//### JMP Data Table reminders (see session 6)
//##################################################################################
dt = Open("$SAMPLE_DATA/Big Class.jmp"); // Invisible, Private
m_num = dt << get as matrix;
names = Column(dt, "name") << Get Values;
all_vals = dt[0, 0]; // list of lists
dt[0, {"height", "weight"}]; // matrix because columns are numeric
Close(dt, no save);

//##################################################################################
//### Excel file
//##################################################################################
// Open with interactive Open first to get basic settings and modify as needed
// or see the syntax from Scripting Index and hope for best
dt = Open(
	"$SAMPLE_IMPORT_DATA/Team Results.xlsx",
	Worksheets("Ungrouped Team Results"),
	Worksheet Settings(Headers Start on Row(3), Data Starts on Row(4))
);

// Save jmp data table as excel workbook
dt = Open("$SAMPLE_DATA/Big Class.jmp", invisible);
dt << Save(Get Default Directory() || "BigClass.xlsx"); // save to same directory as this script is saved into

// Create as excel workbook
dt2 = Open("$SAMPLE_DATA/Abrasion.jmp", invisible);
Create Excel Workbook(Get Default Directory() || "MyWorkbook.xlsx", {dt, dt2}, {"Big", "Abrasive"});
Close(dt, no save); Close(dt2, no save);

Open(Get Default Directory());

Try(Delete File(Get Default Directory() || "BigClass.xlsx"));
Try(Delete File(Get Default Directory() || "MyWorkbook.xlsx"));

//##################################################################################
//### Text files
//##################################################################################
txt1 = Load Text File("$SAMPLE_IMPORT_DATA/Animals.txt");
txt2 = Load Text File("$SAMPLE_IMPORT_DATA/Animals_L.txt");
dt_txt = Open("$SAMPLE_IMPORT_DATA/Animals_L.txt"); // if data is clean formatted Open() might work

Save Text File(Get Default Directory() || "example.txt", "line1\!Nline2\!N", mode("replace"));
Save Text File(Get Default Directory() || "example.txt", "line3\!Nline4\!N", mode("append"));
txt4 = Load Text File(Get Default Directory() || "example.txt");

Try(Delete File(Get Default Directory() || "example.txt"));

// To read text line by line, use Words() with correct separator
// Some possible separators, \!N, \!r, \!n
all_lines = Words(txt2, "\!N");
For Each({line}, all_lines,
	show(line);
	//Show(Words(line, "\!t"));
);

// Some trickery opening as script and using ScriptBox
my_scriptwindow = Open("$SAMPLE_IMPORT_DATA/Animals_L.txt", "script");
my_scriptwindow << Show Window(0);
myfile_lines = my_scriptwindow[scriptBox(1)] << getLines();
my_scriptwindow << close window;
wait(0);
show(all_lines, myscript_lines);


txt3_line3 = Load Text File("$SAMPLE_IMPORT_DATA/Animals_line3.txt"); // data starts on line 3
// Depending on what you want to do, you could remove unnecessary lines (1 and 2)
lines = Words(txt3_line3, "\!N"); // be careful with Words() as it will combine separators
Remove From(lines, 1, 1);
// and then save it, reopen or build table by looping or just Open with Blob
// Advanced... you can use Blob with Open. There is explanation somewhere in community by @Craige_Hales
dt = Open(Char To Blob(Concat Items(lines, "\!N")), "text"); //"text" must be here


//##################################################################################
//### XML files
//##################################################################################
// I have very rarely used this, but this script can give an idea
// Parse XML, XML Attr and XML Text
file_contents = Load Text File("$SAMPLE_IMPORT_DATA/BigclassExcel.xml");
Parse XML(file_contents,
	OnElement(
		"Worksheet",
		StartTag(
			sheetname = XML Attr("Name");
			show(sheetname);
			dt = New Table(sheetname);
			row = 1;
			col = 1;
		)
	),
	OnElement(
		"Row",
		StartTag(
			If(row > 1, // assume first row is column names 
				dt << addRows(1)
			)
		),
		EndTag(
			row++;
			col = 1;
		)
	),
	OnElement("Cell", EndTag(col++)),
	OnElement(
		"Data",
		EndTag(
			data = XML Text(collapse);
			If(row == 1,
				New Column(data, character(10)), 	// assume first row has column names 
				Column(col)[row - 1] = data// and other rows have data 
			);
		)
	)
);

// Open with XML export made with interactive. Might be ways to get this working
dt = Open(
	"$SAMPLE_IMPORT_DATA/BigclassExcel.xml",
	XML Settings(
		Stack(0),
		Row("/Workbook/Worksheet/Table/Row/Cell/Data"),
		Col(
			"/Workbook/Worksheet/Table/Row/Cell/Data",
			Column Name("Data"),
			Fill("Use Once"),
			Type("Character"),
			Format({"Best"}),
			Modeling Type("Continuous")
		)
	),
	XML Wizard(1) // XML Wizard(0) to disable ui
);

//##################################################################################
//### DLL and Batch files (See Session 5 (Load DLL and Run Program())
//##################################################################################
// DLL
// See https://docs.microsoft.com/en-us/windows/win32/api/
F_getScreenSize = Function({}, {Default Local},
	securDll = Load DLL("user32.dll", autoDeclare(false));
	securDll << DeclareFunction("GetSystemMetrics", Arg(Int32), returns(Int32));
	size_x = 0;
	size_y = 0;
	mode_x = 0;
	mode_y = 1;
	size_x = securDll << GetSystemMetrics(mode_x);
	size_y = securDll << GetSystemMetrics(mode_y);  
	
	Return(Eval List({size_x, size_y}));
);
myScreenSize = F_getScreenSize();


// Batch file (.bat)
// A quick note on Batch Files
//   - No trailing spaces
//   - Must add a 'NewLine'
mybat_string = "
echo off \!n
cls \!n
echo Hello World \!n
echo The time is " || Char(MDYHMS(Today())) || " \!n
";
mybat_path = Get Default Directory() || "testbatch.bat";
Save Text File(mybat_path, mybat_string, Mode("replace"));

// If we need return result, you could use Run Program
bat_path = Convert File Path(mybat_path, windows);
rp1 = Run Program(
	Executable(bat_path),
	ReadFunction("text")
);


Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));
mybat_string2 = "\[mkdir "C:\SCRIPTERSCLUBTEST\" \!n]\";
mybat_path = Get Default Directory() || "testbatch.bat";
mybat_path = Convert File Path(mybat_path, windows);
Save Text File(mybat_path, mybat_string2, Mode("replace"));
Open(mybat_path); // this will run the bat
Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));
wait(3); // we will need to wait
Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));

// Or use DLL
Try(Delete Directory("C:\SCRIPTERSCLUBTEST"));
wait(0);
Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));

kernel32 = LoadDLL("Kernel32");
kernel32 << DeclareFunction(
	 "WinExec",
	 Convention(STDCALL),
	 Alias("WinExec"),
	 Arg(AnsiString, "lpCmdLine"),
	 Arg(Int32, "uCmdShow"),
	 Returns(Int32) 
);

kernel32 << WinExec(mybat_path, 5);
Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));
wait(3); // we will need to wait. The time depends on .bat ran
Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));

Try(Delete File(mybat_path));
Try(Delete Directory("C:\Users\SCRIPTERSCLUBTEST"));


//##################################################################################
//### JSON Files
//### there are many different functions, search scripting index for "JSON"
//##################################################################################
json1 = Load Text File(Get Default Directory() ||"Big Class_column.json", "JSON"); // associative array
json2 = Load Text File(Get Default Directory() ||"Big Class_row.json"); // string
// Parse JSON(json2);

// Using interactive Open with preview
dt = Open(
	"/H:/JSL Course/session 8/Big Class_row.json",
	JSON Settings(
		Stack(0),
		Row("/root"),
		Col("/root/name", Column Name("name"), Fill("Use Once"), Type("Character"), Format({"Best"}), Modeling Type("Continuous")),
		Col("/root/age", Column Name("age"), Fill("Use Once"), Type("Numeric"), Format({"Best"}), Modeling Type("Continuous")),
		Col("/root/sex", Column Name("sex"), Fill("Use Once"), Type("Character"), Format({"Best"}), Modeling Type("Continuous")),
		Col("/root/height", Column Name("height"), Fill("Use Once"), Type("Numeric"), Format({"Best"}), Modeling Type("Continuous")),
		Col("/root/weight", Column Name("weight"), Fill("Use Once"), Type("Numeric"), Format({"Best"}), Modeling Type("Continuous"))
	),
	JSON Wizard(0)
);

// If the JSON is easy to use format for JMP just Open might work
dt2 = Open(Get Default Directory() ||"Big Class_row.json");

// Sometimes it might be useful to use Open(Blob()) trick
dt3 = Open(Char To Blob(json2), "json"); // open text to table

Try(Close(dt, no save));
Try(Close(dt2, no save));
Try(Close(dt3, no save));

// JSON to AA and AA to JSON
my_json_str = "{ \!"myChar\!": \!"Character Value\!", \!"myNum\!": 12345}";
aa_json = Parse JSON(my_json_str);
json_str = As JSON Expr(aa_json); // back to JSON
// aa_json2 = Parse JSON(json_str);

//##################################################################################
//### HTTP Requests (GET)
//##################################################################################
// can be used for testing simple requests https://httpbin.org/

request = New HTTP Request(
	URL("https://httpbin.org//get"), 
	Method("GET"), 
	Timeout(5) // default is 60. I suggest modifying it as needed and depending on the server
);
data = request << Send; // See scripting index for more messages related to New HTTP Request
// request << Get Last URL;
// request << Get Status;
aa_json = Parse JSON(data);

//##################################################################################
//### Working with databases (sqlite select)
	// https://community.jmp.com/t5/Discussions/Which-way-to-go-query-database/m-p/655026
	// https://www.jmp.com/support/help/en/17.0/#page/jmp/sql-functions.shtml#ww5089351
	// https://www.jmp.com/support/help/en/17.0/#page/jmp/sql-messages.shtml#
//##################################################################################
// General format (to my knowledge) for connection string is
	// connection_str ="DSN=MYDSN_NAME;UID=USERNAME;PWD=PASSWORD";
	// dsn_string = "Driver={ODBC DRIVER};Dbq=DBDSNNAME;UID=DBUSERNAME;PWD=DBPASSWORD;"; (driver part isn't always needed)
// When New SQL Query is used you need to prefix with ODBC:
	//dsn_string = "ODBC:Driver={ODBC DRIVER};Dbq=DBDSNNAME;UID=DBUSERNAME;PWD=DBPASSWORD;";
// If you are able to build a query using JMP's Query Builder to your database, you can get connection string from there
	// it might contain much more information but you can most likely simplify it
// THESE ARE DEPENDENT ON YOUR DATABASE
	// IT department might be able to help with these
	// https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-an-odbc-data-source-sql-server-import-and-export-wizard?view=sql-server-ver16
	// https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_4406003916.html#Connecting-Using-a-Connection-String

	// Also be careful when sharing tables which have table scripts created by database query as those might contain passwords
		// Hide ODBC connection String preference might help with this (https://community.jmp.com/t5/JMP-Knowledge-Base/Database-table-scripts-Update-from-Database-Modify-etc-or/ta-p/621050)

sqlite_path = Get Default Directory() || "QCdata.sqlite";
connection_str = Eval Insert("SQLite:Database=^sqlite_path^");
sql_str = "select * from lot";


// Open database 
dt = Open Database(connection_Str, sql_str, "NewTable");


// Create Database Connection, Execute SQL, Close Database Connection
dbc = Create Database Connection(connection_str);
dt = Execute SQL(dbc, sql_str, "NewTable");
Close Database Connection(dbc);


// New SQL Query (to my knowledge this is the preferred method)

sql_obj = New SQL Query(Connection(connection_str), Custom SQL(sql_str));
dt = sql_obj << Run; // uses users jmp preferences
dt = sql_obj << Run Background; // Will let JMP work
dt = sql_obj << Run Foreground; // Will block JMP execution until query finishes

// You can set OnRunComplete scripts to New SQL Query (see scripting index)
sql_obj << Run Foreground(OnRunComplete(Write("Number of rows in query result: ", N Rows(queryResult)))); // will not print to embdedded log
// There is also OnRunCanceled and OnError


// Few SQL "tips"
// Use connection_str and sql_template pattern (you could even save these to simple .jsl files)
dsn_string = Eval Insert("SQLite:Database=^sqlite_path^");
sql_template = "\[select * 
from lot 
where lot in (¤lotnr¤)]\";

lotnr = "'API00017'";
sql_str = Eval Insert(sql_template, "¤"); // insert values to your template

sql_obj = New SQL Query(Connection(dsn_string), Custom SQL(sql_str));
dt = sql_obj << Run Foreground;

lotnr = "'API00017', 'API00058'";
sql_str = Eval Insert(sql_template, "¤");
sql_obj = New SQL Query(Connection(dsn_string), Custom SQL(sql_str));
dt = sql_obj << Run Foreground;


// If database queries won't throw errors and you can try to use Log Capture() to capture issues
sql_error = Log Capture(dt = Open Database(connection_Str, sql_str, "NewTable"));
show(sql_error);
If(Contains(sql_error, "[ODBC Driver Manager]"),
    Throw(sql_error);
);

// Might be a good idea to check if dt is empty or has no rows
Is Empty(dt10); 
N Rows(dt); // this will error if dt doesn't exist


// Use Query to perform SQL queries with OPEN JMP data tables
dt = Open("$SAMPLE_DATA/Big Class.jmp", Invisible);
dt_result = Query(dt, 
	"SELECT name, age, height 
	FROM 'Big Class'
	WHERE age > 14;"
);
 
 // Using aliases, performing a join
dtSAT = Open("$SAMPLE_DATA/SATByYear.jmp", Invisible);
dtUS = Open("$SAMPLE_DATA/US Demographics.jmp", Invisible);
dt_result2 = Query(
	Table(dtSAT, "t1"),
	Table(dtUS, "t2"), 
	"\[SELECT t1.State, t1."SAT Math", t2."College Degrees",
            t2."Eighth Grade Math"
       FROM t1
       LEFT OUTER JOIN t2
           ON t1.State = t2.State
       WHERE t1.'SAT Math' > 550;
      ]\"
);

// Query that returns a scalar value
retval1 = Query(Scalar, dt, "SELECT AVG(height) from 'Big Class';");
// Query with no tables
retval2 = Query(Scalar, "SELECT SQRT(152399025);");

// could be done without sql, but it is good to have options
avg_height = Col Mean(Column(dt, "height")); 
sqrt_val = Sqrt(152399025);



// available functions https://www.jmp.com/support/help/en/16.2/#page/jmp/sql-functions-available-for-jmp-queries.shtml#ww998370
// There are tasks which are sometimes easier to perform with SQL (if you can write the query) than with JMP
dt = Open("$SAMPLE_DATA/Big Class.jmp", Invisible);
// one example could be concatenation of values to single column by group
// This can be helpful before summary or split for example to avoid losing data
dt_result = Query(dt, 
	"\[SELECT age, group_concat(name, ', ') names
	FROM 'Big Class'
	GROUP BY age
	]\"
);



//##################################################################################
//### zip files (scripting index for more)
// https://www.jmp.com/support/help/en/16.2/#page/jmp/zip-archives.shtml?os=win&source=application#1931138
//##################################################################################
za = Open(Get Default Directory() || "folder1.zip", zip); // open as zip archive object
dirlist = za << dir; // returns list of members in the list 
za << Write("newfile.txt", "this is newfiledata"); // write new file
dirlist = za << dir; // 
text = za << Read(dirlist[2]); // read from zip archive

// Compress folder into a Zip file (@Justin_Chilton)
	// https://community.jmp.com/t5/JSL-Cookbook-Archived/Compress-a-Folder-into-a-Zip-File/ta-p/48878
	
//##################################################################################
//### Multiple File Import (MFI)
// https://www.jmp.com/support/help/en/16.2/#page/jmp/utility-functions.shtml?os=win&source=application#ww7168938
//### Can be helpful when you have multiple similar looking .csv files for example
//### Try it first out interactivelty and then copy the script
//### You could also get list of files using Files In Directory() and looping over them but 
//### MFI has additional features
//##################################################################################
mfi = Multiple File Import(
	<<Set Folder("$SAMPLE_IMPORT_DATA"),
	<<Set Name Filter("UN*.csv"), // import files with this name
	<<Set Name Enable(1), // display the file name in a column
	<<Set Add File Name Column(1),
) << Import Data(); // returns a list

// MFI + Zip (@Craige_Hales)
	// https://community.jmp.com/t5/Uncharted/MFI-from-Selected-CSVs-in-a-ZIP/ba-p/439274#U439274

// Directory Tree: Explore Space Used by Folders (@Craige_Hales)
	// https://community.jmp.com/t5/Uncharted/Directory-Tree-Explore-Space-Used-by-Folders/ba-p/456571


//##################################################################################
//### Pictures
// https://community.jmp.com/t5/Discussions/Add-images-from-URLs-in-a-column/td-p/556300
// https://community.jmp.com/t5/Discussions/Display-PNG-image-from-URL-into-a-Wafer-Map/m-p/578427
// See Set Graphlet from scripting index and External image example
//##################################################################################
/* Picture file imported as a picture object */
pic = Open("$SAMPLE_IMAGES/tile.jpg", jpg);
show(pic);
// Adding picture to new window
nw = New Window("Picture", 
	Outline Box("Picture", 
		Picture Box(pic)
	)
);

// adding picture to data table's expression column
dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Column 1", Expression, "None", Set Selected, Set Values({}))
);
dt[1, 1] = pic;



//##################################################################################
//### PDF
// Example from scripting index
//##################################################################################
/* PDF file imported as one or multiple data tables
open(pdfFilePath,
	PDF Tables(Table(<Name(name)>, Add Rows(Page(n), <Header Rows(n)>, Rect(top, left, right, bottom), <RowBorders(n, ...)>, <Column Borders(n, ....)>), ...)) |
	PDF All Tables(< Combine(All | Matching Headers | None)>, <Minimum Rows(n)>, <Minimum Columns(n)>) |
	PDF Text(<Pages(n, ...)>, <sort>) |
	PDF Wizard
);*/
dt = Open("$SAMPLE_DATA\Big Class.jmp");
w = New Window("test", Data Table Box(dt));
w << save picture(Get Default Directory() || "test.pdf", pdf);
w << close window;
close(dt, no save);
pdftable = Open(Get Default Directory() || "test.pdf", PDF All Tables(Combine(all))); // just some of the rows
pdftable2 = Open(Get Default Directory() || "test.pdf", PDF Tables(Table(Table Name("test"), Add Rows(Page(1), Rect(0, 0, 5, 3)))));

Close(pdftable, no save);
Close(pdftable2, no save);

Try(Delete File(Get Default Directory() || "test.pdf"));

Re: Session 8: Working with external files & Databases

maria_astals — Mon, 10 Jul 2023 07:03:31 GMT

@jthi , done :)

Re: Session 8: Working with external files & Databases

vohwinkelh0 — Mon, 10 Jul 2023 08:16:12 GMT

Sorry I was not able to make it but I thought I re share this post from the community, which I found extremely helpful:

Save to Database is too slow (10k records per minute)

Henning

Re: Session 8: Working with external files & Databases

Ressel — Tue, 01 Aug 2023 19:11:34 GMT

That was another highly useful lesson for the novice scripter. I specifically liked @jthi's comment at 29:32 ("If you can get [the JMP Query Builder] to work."). My experience was that the JMP Query Builder constantly froze or crashed in JMP14, but I have since given it another try with JMP17 where it is stable, although it can take a lot of time to build a query, depending on the size of the tables involved. Being a novice and not knowing SQL, I worked for a long time by loading a millions of rows (i.e., the entirety of a given table via Open Database(), ignoring all filter functionality) and discarding everything not needed locally. This was easy enough to learn over time, but has its limits. With JMP17 I was finally able to understood -or at least, I like to believe so- the meaning of "pushing queries back to the server". The first query copy-pasted from the JMP Query Builder into a jsl has already saved me a few hours of waiting time.

Also interesting comments by @maurogerber in the second video around 13:20. Can I interpret them as shown below?

New SQL Query(); // which is what I extract from the JMP Query builder and copy to a script

// Question 1: Can the below use the same or similar SQL commands as the above?

// Question 2: If yes, is there a preferred method?

Open Database();
Execute SQL();

Re: Session 8: Working with external files & Databases

jthi — Tue, 01 Aug 2023 20:01:18 GMT

With Open Database() and Execute SQL() you will have to use similar commands as you would use with New SQL Query() when using Custom SQL (so sql queries). To my understanding New SQL Query() is the preferred method (see Brian's answer ) as it will have support in the future.