//##################################################################################
//###
//### 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"));
//################################################## #################################### //#### //### 8. 使用外部文件和数据库 //### //##################################### ############################################## // 在这个文件中我们将介绍： // - 会话 7 的简短回顾（范围/命名空间和报告层） // - .xlsx // - 文本文件 // - xml 文件 // - json 文件 // - GET 请求 // - DLL (请参阅第 5 节） // - 批处理文件（请参阅第 5 节） // - 数据库（使用 .sqlite 数据库） // - 图片 // - pdf //################ #################################################### ############### //### 会话 7 回顾（范围属性和报告层） //
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 //### #################################################### ############################ // 作用域和命名空间 // 使用 Names Default To Here(1) 启动脚本； // 启用 Names Default To Here 模式会将名为 Here 的范围与正在执行的脚本关联起来 // 不要将以下内容留在生产代码中，因为它们会破坏其他脚本 Clear Symbols();清除全局变量();删除符号();删除全局变量(); // 要查看哪个消息对象有，您可以使用 Show Properties(obj); // 报表层 // 要探索报表层，请使用 obj << Show Properties;和 obj << 显示树结构； (obj << Get XML;) // 使用rep = obj << Report; 获取对报表层的访问权限或报告（obj）； // 报告下标（返回第一个匹配项） param_tratio2 = biv_report[Outline Box("Parameter Estimates"), Number Col Box("t Ratio")]; // 使用 obj << 获取 XML;在使用 XPath 之前查看 XML 的外观 // XPath（返回匹配列表并且您可以将消息发送到列表） biv_report << XPath("//OutlineBox[contains(text(), 'Bivariate Normal Ellipse')]" ）； //################################################## ################################## //### //### 使用外部文件 // ### //############################################## #################################### //############ #################################################### #################### //### JMP 数据表提醒（参见第 6 节） //############# #################################################### ################### dt = Open("$SAMPLE_DATA/Big Class.jmp"); // 不可见，私有 m_num = dt << 获取为矩阵；名称 = Column(dt, "名称") << 获取值; all_vals = dt[0, 0]; // 列表的列表 dt[0, {"height", "weight"}]; // 矩阵，因为列是数字 Close(dt, no save); //################################################## ################################## //### Excel 文件 //####### #################################################### ######################## // 以交互方式打开 首先打开以获取基本设置并根据需要进行修改 // 或查看脚本索引中的语法和希望最好的 dt = Open( "$SAMPLE_IMPORT_DATA/Team Results.xlsx", Worksheets("Ungrouped Team Results"), Worksheet Settings(标题从行 (3) 开始，数据从行 (4) 开始) ); // 将jmp数据表保存为excel工作簿 dt = Open("$SAMPLE_DATA/Big Class.jmp",无形); dt << 保存(获取默认目录() || "BigClass.xlsx"); // 保存到与此脚本保存相同的目录 // 创建为 Excel 工作簿 dt2 = Open("$SAMPLE_DATA/Abrasion.jmp",无形);创建 Excel 工作簿(获取默认目录() || "MyWorkbook.xlsx", {dt, dt2}, {"Big", "Abrasive"});关闭（dt，不保存）；关闭（dt2，不保存）；打开（获取默认目录（））；尝试（删除文件（获取默认目录（）||“BigClass.xlsx”））；尝试（删除文件（获取默认目录（）||“MyWorkbook.xlsx”））； //################################################## ################################## //### 文本文件 //####### #################################################### ######################## txt1 = 加载文本文件("$SAMPLE_IMPORT_DATA/Animals.txt"); txt2 = 加载文本文件("$SAMPLE_IMPORT_DATA/Animals_L.txt"); dt_txt = 打开("$SAMPLE_IMPORT_DATA/Animals_L.txt"); // 如果数据是干净格式的 Open() 可能会工作 Save Text File(Get Default Directory() || "example.txt", "line1\!Nline2\!N", mode("replace"));保存文本文件(获取默认目录() || "example.txt", "line3\!Nline4\!N", mode("append")); txt4 = 加载文本文件(获取默认目录() || "example.txt");尝试（删除文件（获取默认目录（）||“example.txt”））； // 要逐行读取文本，请使用带有正确分隔符的 Words() // 一些可能的分隔符，\!N、\!r、\!n all_lines = Words(txt2, "\!N"); For Each({line}, all_lines, show(line); //Show(Words(line, "\!t")); ); // 以脚本形式打开并使用 ScriptBox 的一些技巧 my_scriptwindow = Open("$SAMPLE_IMPORT_DATA/Animals_L.txt", "script"); my_scriptwindow << 显示窗口(0); myfile_lines = my_scriptwindow[scriptBox(1)] << getLines(); my_scriptwindow << 关闭窗口；等待（0）；显示（所有行，myscript_lines）； txt3_line3 = 加载文本文件("$SAMPLE_IMPORT_DATA/Animals_line3.txt"); // 数据从第 3 行开始 // 根据您想要执行的操作，您可以删除不必要的行（1 和 2）lines = Words(txt3_line3, "\!N"); // 请小心 Words()，因为它会合并分隔符 Remove From(lines, 1, 1); // 然后保存它，重新打开或通过循环构建表或仅使用 Blob 打开 // 高级...您可以将 Blob 与 Open 一起使用。
@Craige_Hales dt = Open(Char To Blob(Concat Items(lines, "\!N")), "text"); 在社区的某个地方有解释。 //此处必须为“文本” //######################################## ############################################ //### XML 文件 / /################################################## ################################ // 我很少使用这个，但是这个脚本可以提供一个想法 / / 解析 XML、XML Attr 和 XML 文本 file_contents = 加载文本文件("$SAMPLE_IMPORT_DATA/BigclassExcel.xml");解析 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, // 假设第一行是列名 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)), // 假设第一行有列名 Column(col)[row - 1] = data//其他行有数据 ); ) ) ); // 使用交互式 XML 导出打开。 可能是让这个工作的方法 dt = Open( "$SAMPLE_IMPORT_DATA/BigclassExcel.xml", XML Settings( Stack(0), Row("/Workbook/Worksheet/Table/Row/Cell/Data"), Col( "/工作簿/工作表/表格/行/单元格/数据”、列名称(“数据”)、填充(“使用一次”)、类型(“字符”)、格式({“最佳”})、建模类型(“连续” ") ) ), XML Wizard(1) // XML Wizard(0) 禁用 ui ); //################################################## ################################## //### DLL 和批处理文件（参见第 5 节（加载 DLL并运行程序()) //############################################ ########################################## // DLL // 参见 https:// docs.microsoft.com/en-us/windows/win32/api/ F_getScreenSize = Function({}, {默认本地}, secureDll = 加载 DLL("user32.dll", autoDeclare(false)); securDll << DeclareFunction( “GetSystemMetrics”，Arg（Int32），返回（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(); // 批处理文件 (.bat) // 关于批处理文件的快速说明 // - 没有尾随空格 // - 必须添加'NewLine' mybat_string = " echo off \!n cls \!n echo Hello World \!n echo 时间是 " || Char(MDYHMS(Today())) || " \!n "; mybat_path = 获取默认目录() || "testbatch.bat"; 保存文本文件(mybat_path, mybat_string, Mode("replace")); // 如果我们需要返回结果，可以使用 Run Program bat_path = Convert File Path(mybat_path, windows); rp1 = 运行程序（可执行文件（bat_path），ReadFunction（“文本”））； Show(目录存在("C:\SCRIPTERSCLUBTEST")); mybat_string2 = "\[mkdir "C:\SCRIPTERSCLUBTEST\" \!n]\"; mybat_path = 获取默认目录() || “测试批次.bat”； mybat_path = 转换文件路径(mybat_path, windows);保存文本文件(mybat_path, mybat_string2, Mode("replace"));打开（mybat_path）； // 这将运行 bat Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));等待（3）； // 我们需要等待 Show(Directory Exists("C:\SCRIPTERSCLUBTEST")); // 或者使用 DLL Try(Delete Directory("C:\SCRIPTERSCLUBTEST"));等待（0）； Show(目录存在("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(目录存在("C:\SCRIPTERSCLUBTEST"));等待（3）； // 我们需要等待。 时间取决于 .bat ran Show(Directory Exists("C:\SCRIPTERSCLUBTEST"));尝试（删除文件（mybat_path））；尝试(删除目录(“C:\Users\SCRIPTERSCLUBTEST”)); //################################################## ################################## //### JSON 文件 //### 有很多不同的函数，搜索“JSON”的脚本索引 //######################################## ############################################ json1 = 加载文本文件(获取默认目录() ||"Big Class_column.json", "JSON"); // 关联数组 json2 = 加载文本文件(Get Default Directory() ||"Big Class_row.json"); // 字符串 // 解析 JSON(json2); // 使用交互式打开和预览 dt = Open( "/H:/JSL Course/session 8/Big Class_row.json", JSON Settings( Stack(0), Row("/root"), Col("/root/ name", 列名("name"), Fill("使用一次"), 类型("字符"), 格式({"最佳"}), 建模类型("连续")), Col("/root/年龄", 列名("年龄"), 填充("使用一次"), 类型("数字"), 格式({"最佳"}), 建模类型("连续")), Col("/root/ sex", 列名("sex"), 填充("使用一次"), 类型("字符"), 格式({"最佳"}), 建模类型("连续")), Col("/root/高度", 列名称("高度"), 填充("使用一次"), 类型("数字"), 格式({"最佳"}), 建模类型("连续")), Col("/root/重量”、列名称(“重量”)、填充(“使用一次”)、类型(“数字”)、格式({“最佳”})、建模类型(“连续”)))、JSON 向导(0) ）； // 如果 JSON 是 JMP 易于使用的格式，则只需打开即可工作 dt2 = Open(Get Default Directory() ||"Big Class_row.json"); // 有时使用 Open(Blob()) 技巧可能会很有用 dt3 = Open(Char To Blob(json2), "json"); // 打开文本到表 Try(Close(dt, no save));尝试（关闭（dt2，不保存））；尝试（关闭（dt3，不保存））； // JSON 到 AA 以及 AA 到 JSON my_json_str = "{ \!"myChar\!": \!"角色价值\!", \!"myNum\!": 12345}"; aa_json = 解析 JSON(my_json_str); json_str = As JSON Expr(aa_json); // 返回 JSON // aa_json2 = 解析 JSON(json_str); //############ #################################################### ################### //### HTTP 请求 (GET) //################## #################################################### ############## // 可用于测试简单请求
https://httpbin.org/ request = New HTTP Request( URL("
https://httpbin.org//get "), Method("GET"), Timeout(5) // 默认值为 60。 我建议根据需要并根据服务器进行修改）；数据 = 请求 << 发送； // 请参阅脚本索引以获取与新 HTTP 请求相关的更多消息 // request << Get Last URL; // 请求 << 获取状态; aa_json = 解析 JSON(数据); //################################################## ################################## //### 使用数据库 (sqlite select) // https: //community.jmp.com/t5/Discussions/Which-way-to-go-query-database/mp/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# //######## #################################################### ######################## // 连接字符串的一般格式（据我所知）是 // connection_str ="DSN=MYDSN_NAME;UID=USERNAME; PWD=密码”； // dsn_string = "Driver={ODBC DRIVER};Dbq=DBDSNNAME;UID=DBUSERNAME;PWD=DBPASSWORD;"; （驱动程序部分并不总是需要） // 当使用新的 SQL 查询时，您需要添加 ODBC 前缀： //dsn_string = "ODBC:Driver={ODBC DRIVER};Dbq=DBDSNNAME;UID=DBUSERNAME;PWD=DBPASSWORD; ”; // 如果您能够使用 JMP 的查询生成器对数据库构建查询，您可以从那里获取连接字符串 // 它可能包含更多信息，但您很可能可以简化它 // 这些取决于您的数据库 // IT 部门也许能够帮助解决这些问题 //
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-使用-a-Connection-String // 共享具有由数据库查询创建的表脚本的表时也要小心，因为这些表可能包含密码 // 隐藏 ODBC 连接字符串首选项可能对此有所帮助（
https://community.jmp.com/ t5/JMP-Knowledge-Base/Database-table-scripts-Update-from-Database-Modify-etc-or/ta-p/621050) sqlite_path = 获取默认目录() || “QCdata.sqlite”； connection_str = Eval Insert("SQLite:Database=^sqlite_path^"); sql_str = "从批次中选择*"; // 打开数据库 dt = 打开数据库(connection_Str, sql_str, "NewTable"); // 创建数据库连接，执行SQL，关闭数据库连接 dbc = 创建数据库连接(connection_str); dt = 执行 SQL(dbc, sql_str, "NewTable");关闭数据库连接(dbc); // 新的 SQL 查询（据我所知，这是首选方法） sql_obj = New SQL Query(Connection(connection_str), Custom SQL(sql_str)); dt = sql_obj << 运行； // 使用用户 jmp 首选项 dt = sql_obj << 运行后台; // 让 JMP 工作 dt = sql_obj << Run Foreground; // 将阻止 JMP 执行，直到查询完成 // 您可以将 OnRunComplete 脚本设置为新 SQL 查询（请参阅脚本索引） sql_obj << Run Foreground(OnRunComplete(Write("查询结果中的行数: ", N Rows(queryResult) ）））； // 不会打印到嵌入日志 // 还有 OnRunCanceled 和 OnError // 很少的 SQL“提示” // 使用 connection_str 和 sql_template 模式（您甚至可以将它们保存到简单的 .jsl 文件中） dsn_string = Eval Insert("SQLite:数据库=^sqlite_path^"); sql_template = "\[从批次中选择 * (¤lotnr¤)]\"; lotnr = "'API00017'"; sql_str = Eval Insert(sql_template, "¤"); // 将值插入模板 sql_obj = New SQL Query(Connection(dsn_string), Custom SQL(sql_str)); dt = sql_obj << 运行前台； Lotnr = "'API00017', 'API00058'"; sql_str = Eval Insert(sql_template, "¤"); sql_obj = 新 SQL 查询(连接(dsn_string), 自定义 SQL(sql_str)); dt = sql_obj << 运行前台； // 如果数据库查询不会抛出错误，您可以尝试使用 Log Capture() 捕获问题 sql_error = Log Capture(dt = Open Database(connection_Str, sql_str, "NewTable"));显示（sql_error）； If(Contains(sql_error, "[ODBC 驱动程序管理器]"), Throw(sql_error); ); // 检查 dt 是否为空或没有行可能是个好主意 Is Empty(dt10); N 行(dt)； // 如果 dt 不存在，则会出错 // 使用 Query 对 OPEN JMP 数据表执行 SQL 查询 dt = Open("$SAMPLE_DATA/Big Class.jmp", Invisible); dt_result = Query(dt, "从'大班'中选择姓名、年龄、身高，其中年龄 > 14;" ); // 使用别名执行连接 dtSAT = Open("$SAMPLE_DATA/SATByYear.jmp", Invisible); dtUS = Open("$SAMPLE_DATA/US Demographics.jmp", 不可见); dt_result2 = Query( Table(dtSAT, "t1"), Table(dtUS, "t2"), "\[SELECT t1.State, t1."SAT 数学", t2."大学学位", t2."八年级数学" FROM t1 LEFT OUTER JOIN t2 ON t1.State = t2.State WHERE t1.'SAT Math' > 550; ]\" ); // 返回标量值的查询 retval1 = Query(Scalar, dt, "SELECT AVG(height) from 'Big Class';"); // 没有表的查询 retval2 = Query(Scalar, "SELECT SQRT(152399025);"); // 无需 sql 即可完成，但最好有选项 avg_height = Col Mean(Column(dt, "height")); sqrt_val = Sqrt(152399025); // 可用函数
https://www.jmp.com/support/help/en/16.2/#page/jmp/sql-functions-available-for-jmp-queries.shtml#ww998370 // 有些任务有时是使用 SQL（如果您可以编写查询）比使用 JMP dt = Open("$SAMPLE_DATA/Big Class.jmp", Invisible); 更容易执行// 一个示例可以是按组将值串联到单列 // 这在汇总或拆分之前很有用，例如可以避免丢失数据 dt_result = Query(dt, "\[SELECTage, group_concat(name, ', ')姓名来自‘大班’按年龄分组]\”）； //################################################## ################################## //### zip 文件（更多脚本索引） // https ://
www.jmp.com/support/help/en/16.2/#page/jmp/zip-archives.shtml?os=win&source=application#1931138 //############# #################################################### ################## za = Open(获取默认目录() || "folder1.zip", zip); // 作为 zip 存档对象打开 dirlist = za << dir; // 返回列表中的成员列表 za << Write("newfile.txt", "this is newfiledata"); // 写入新文件 dirlist = za << dir; // 文本 = za << Read(dirlist[2]); // 从 zip 存档中读取 // 将文件夹压缩为 Zip 文件 (@Justin_Chilton) //
https://community.jmp.com/t5/JSL-Cookbook-Archived/compress-a-Folder-into-a-Zip- file/ta-p/48878 //########################################### ######################################## //### 多个文件导入 ( MFI) //
https://www.jmp.com/support/help/en/16.2/#page/jmp/utility-functions.shtml?os=win&source=application#ww7168938 //### 当您例如，有多个外观相似的 .csv 文件 //### 首先尝试交互，然后复制脚本 //### 您还可以使用 Files In Directory() 获取文件列表并循环访问它们，但是 //# ## MFI 有附加功能 //############################################ ########################################## mfi = 多个文件导入( <<设置文件夹("$SAMPLE_IMPORT_DATA"), <
、添加行（页（n）、<标题行（n）>、矩形（上、左、右、下）、、<列边框（n） , ....)>), ...)) | PDF 所有表格(< 合并(全部 | 匹配标题 | 无)>, <最小行数 (n)>, <最小列数 (n)>) | PDF 文本(<页面(n, ...)>, <排序>) | PDF 向导 );*/ dt = Open("$SAMPLE_DATA\Big Class.jmp"); w = 新窗口("测试", 数据表框(dt)); w << 保存图片(获取默认目录() || "test.pdf", pdf); w << 关闭窗口；关闭（dt，不保存）； pdftable = Open(获取默认目录() || "test.pdf", PDF 所有表格(合并(全部))); // 仅部分行 pdftable2 = Open(获取默认目录() || "test.pdf", PDF Tables(Table(表名称("test"), Add Rows(Page(1), Rect(0, 0)） , 5, 3)))));关闭（pdftable，不保存）；关闭（pdftable2，不保存）；尝试（删除文件（获取默认目录（）||“test.pdf”））； 
这篇帖子最初是用 English (US) 书写的，已做计算机翻译处理。当您回复时，文字也会被翻译成 English (US)。