Grab a single value from Excel worksheet into a variable?

Question

I have a script that imports measurement files from Excel into a combined file and then pulls part of the file ID into a 'part ID' column, which then has to reference a table with a 'code key' relating the File ID to the actual part ID.

The Excel file has three worksheets, only one that is useful in terms of data.  Importing that is easy.

I would like to directly grab the *real* part ID, which is sitting in cell D7 on the first worksheet.

Is there a way to grab this value from each of an indeterminate number of files and store it in an array or other variable and then dump it into the main file?

gzmorgan0 · Answer

@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, @Mark_Bailey, 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);

Mark_Bailey · Answer

You can treat a Microsoft Excel workbook like a relational database. This way, you can use SQL to specify a query for the value you want. You can interactively perform the entire process to make sure that it works, and then the resulting data table will have a Source table script with the JSL you need.

Start here in the JMP on-line documentation for complete instruction and examples.