//##################################################################################
//###
//### 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="">, Add Rows(Page(n), <HEADER rows="">, Rect(top, left, right, bottom), <ROWBORDERS>, <COLUMN borders="">), ...)) |
PDF All Tables(< Combine(All | Matching Headers | None)>, <MINIMUM rows="">, <MINIMUM columns="">) |
PDF Text(<PAGES>, <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"));
</SORT></PAGES></MINIMUM></MINIMUM></COLUMN></ROWBORDERS></HEADER></SET>
