@andersonmj2,
If you do not have ODBC set up, your system files set up, there might be a bit of a learning curve, especially if you have 32 bit Office installed on your PC.
Mark, @markbailey, as of JMP 13 and early JMP14, the Microsoft Office instructions to use the SYSWOW were not very reliable; the instructions would work for some PCs and not for others. Has JMP done something since early JMP 14 to set up DBase and Text and Excel ODBC? If yes, I am interested. If not, a PDF was written for JSL Companion with detailed instructions that seem to be reliable, but has several steps. I think it is worth the set up effort to be able to query different file types using SQL.
So the script below is to demonstrate an alternative using the JMP Excel Wizard. There is no guarantee for performance. This script shows the JSL forr one file, the script would have to create a loop to address all files.
See the Log for the result cid = "70.gg.7";
Note the 8th value is used since Excel column headers (A, B, ...) respresent 1 row.
Names Default to Here(1);
//create Excel workbook
dt1_1 = New Table("Dummy",
New Column("A", Character, Values({"red", "white", "green", "blue", "yellow", "orange", "black", "magenta"})),
New Column("B", Numeric, Values({1,2,3,4,5,6,7,8})),
New Column("C", Numeric, Values({10,20,30,40,50,60,70,80})),
New Column("D", Character, Values({"10.aa1","20.bb.2", "30.cc.3", "40.dd.4", "50.ee.5","60.ff.6", "70.gg.7","80.hh.8"})),
New Column("E", Numeric, Values({101,102,103,104,105,106,107,108}))
);
wait(0);
dt1_2 = Open("$sample_data/Big Class.jmp");
dt1_3 = Open("$sample_data/Iris.jmp");
Create Excel Workbook("c:/temp/Test_GetExcelValue.xlsx", {"Dummy", "Big Class", "Iris"}, {"Sheet1", "Sheet2", "Sheet3"});
wait(0);
Close(dt1_1,Nosave);
Close(dt1_2,Nosave);
Close(dt1_3,Nosave);
//========================================================================
xl_dt3 = Open( "c:/temp/Test_GetExcelValue.xlsx", Invisible,
Worksheets("Sheet1"),
Worksheet Settings(1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 8 ),
Data Starts on Column( 4 ),
Data Ends on Row(8), //0 >> not set, read to end of rows
Data Ends on Column(4), //0 >> not set, read to end of cols
)
);
cid = xl_dt3[1,1];
close(xl_dt3,NoSave);
show(cid);
... View more
