Video shows how to update a JMP data table to reflect Excel updates. Because JMP is intended to be hands on & generally doesn’t encourage automation, you go back to JMP to re-run the script again. Note: when you run the script in JMP, it will not copy over the Control Chart script or any other scripts you have saved in the Tables Panel, so you may want to append that to the Source script. A more automated sequence would involve scripting using JSL.
Here are some Scripting Resources:
If you want to update a JMP data table to reflect Excel updates, please check out this wish by @vhuac :
https://community.jmp.com/t5/JMP-Wish-List/Join-Concat-and-Update/idc-p/556341/highlight/true#M2499
Some background :
A quite general definition for update could be:
There is a Jmp data table (with many settings and scrips, added columns, merged tables etc. ) and there is some new data in one of the source tables.
How to get the updated data into the Jmp data table? - i.e. transfer of all the information in the colored blocks - while the data in the gray block is preserved.
- Tables/Update gets really close to what we want (blue and yellow area) - but the green an orange block will be missing.
- delete all entries and Table/Concatenate could solve this issue, but you will lose any additional data (gray area) that was added from other sources
A single additional button in the Tables/Update command should do the job:"append additional rows"
... or for the case of "match columns" enabled: "append non-matched rows"
While these options I am proposing do not include an "Update" button, and without knowing more about your analysis goals, here are some potential solutions that do not include writing JSL. You can always request features by searching for them, give kudos to them and if the feature request is not there, adding your own in the JMP Wishlist.
One way to do this is to use the Enhanced Log (if you have version 16 or newer). Enhanced Log records the tasks at the top of the Log and writes the JSL below.
Another way is to use the Workflow Builder (if you have version 17) which takes the Enhanced Log a bit further. So if the issue is, you do not want to lose any scripts or you don't want to have to repeat the analysis again with the new data because these 3 ways all create a new data table. I would recommend using Workflow Builder that can import the data, create graphs or analysis. It can even save the reports to JPG, PDF or even JMP Live. So you can do all 3 actions: import, create reports, and share, all within the Workflow Builder.
Below is the JSL (that I admit I copied from the Workflow Builder) where I imported data from Excel using Big Class and created . If the Excel file is updated, the new updated data will come into JMP as a new data table:
Names Default To Here( 1 );
//Import Excel file: Big Class.xlsx
Open(
"$DESKTOP/Big Class.xlsx",
Worksheets( "Big Class" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
//Report snapshot: Big Class - Graph Builder
Data Table( "Big Class" ) << Graph Builder(
Size( 522, 452 ),
Show Control Panel( 0 ),
Variables( X( :weight ), Y( :height ) ),
Elements( Points( X, Y, Legend( 3 ) ), Line Of Fit( X, Y, Legend( 5 ) ) )
);
//Save to image: $DESKTOP/Big Class - Graph Builder.pdf
Local( {obj},
obj = (Data Table( "Big Class" ) <<
Graph Builder(
Size( 522, 452 ),
Show Control Panel( 0 ),
Variables( X( :weight ), Y( :height ) ),
Elements( Points( X, Y, Legend( 3 ) ), Line Of Fit( X, Y, Legend( 5 ) ) )
)) << Report;
(obj << Get Picture( Type( "Scalable" ) )) <<
Save Image( "$DESKTOP/Big Class - Graph Builder.pdf", "PDF" );
obj << Close Window;
);
This script imports the data from Excel, creates a Graph Builder report & then saves the report to a PDF file. Each time I run the script, if the Excel file is updated with new data, the JMP data table, graph and PDF will be as well.
I just found this post. The title sounds extremely promising.
But as @kmouloua pointed out, unfortunately, the video doesn't show how to update a data table.
As an add- on to @DonMcCormack's proposal:
One can also use a JMP data table as a source for the Jmp Query Builder
... and this way get a comparable update script (without 64bit Excel & 64 bit Access 
A tiny change in this script will enable it to read the data from a new data table (i.e. the one that @alisa_h_lowery generated by re-running the source script).
On the other hand:
Many users will just import files via Open and won't do the the kind of unnecessary Jmp Query step - just to get an update button.
For this case, a universal update button is still missing.
So, without the additional JSL-Query-step: no easy way to update a JMP data table to reflect Excel updates?
@kmouloua that's a bit trickier since it requires that you
- Have 64-bit Excel
- Have the Microsoft Access driver that allows you to connect to 64-bit Excel as a database.
- Have the administrative privileges on your machine allowing you to create a User or System DSN to connect to the workbook as a dataabase.
If you have these three things, opening an Excel worksheet via the Open Database option will create a table script Update from DB which will update the table in place.
Hello,
I follower your instructions in your video regarding "How to update a JMP data table to reflect Excel updates" subject.
However, instead of updating the existing table, a new table with the updated data is created. How can i make the master table to be updated it without having a duplicate table so that i am forced to use the "Update Table" function available under the Table menu?
Thank you
Karim
