//################################################################################## //### //### 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( <, Add Rows(Page(n),
, Rect(top, left, right, bottom), , ), ...)) | PDF All Tables(< Combine(All | Matching Headers | None)>, , ) | PDF Text(, ) | 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"));
