Is there a refresh option to update data table in JMP 13.2.1

[#1]

Hi,

I imported some data from Excel into a data table in JMP 13.2.1. Now, some of the cells have been updated in the Excel file and I wonder if it is possible to import the data from the Excel file which now contains updated cells (something like refereshing the data in the JMP data table). Note that the number of rows or columns nor the column headings have been changed; just the numbers are different.

Thanks

[#2]

Hi,
If you open the Excel file from JMP using the Excel Import Wizard you get a source script in the data table. Try that. It might help.
Regards,
Phil

[#3]

I right clicked on the 'Source' in the JMP data table and chose 'Run', it created a new data table which did not have the settings I had created in the first data table (row colours by column and data type etc). 

[#4]

Yes, I would expect that. The source script repeats the import into a JMP table.

I think you would have to script the further data pre-process steps. E.g:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
Wait( 2 );
dt:Age << Data Type( "Character" );
dt:Height << Data Type( Numeric, 2 );

 Also:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Color or Mark by Column( :Age );

[#5 · Accepted solution]

I would take Phil's advice but go in a slightly different direction.

Lets say you already have the first table (dt1) all formatted and set up.

The you can use the Source script that pulls a new "fresh" copy of the raw excel table (dt2)

Now update table 1 with table 2.  (tables menu, Update)  or use a script like this.

note: you can use multiple columns to define a uniqe identifier for each row.

dt1 << Update(
	With(dt2 ),
	Match Columns( :Unique = :Unique )
);

[#6]

Thanks Byron. Table > Update menu sounds promising. When I clicked on the Table > Update menu in the dt to be updated. It opened the 'Update' dialogue box, however in the 'Update dt.. with data from' section of the dialogue box, JMP showed some file (data tables?) names which did not have relevance to the current dt (not sure how/why JMP chose those files to be shown). I couldn't find a way to locate the updated Excel file to pull the updated data from. Any idea how to choose the updated Excel file to pull the data from, please? Thanks

[#7]

OK, so when you click Tables/Update, you see a list of all the current tables that are open.

Update is essentially an inner join. 

What ever is the current active table is the table that you can update with a selected table.

If no other tables are open. 

Next, I open the excel table in JMP, add scripts, color the rows, and do anything except change the column names. 

This is my main table. 

Second, I open the new version of the excel table. Outside of JMP this table has been edited or changed or updated somehow.

To do the Tables/Update operation:

Select the first table (remember there are only two tables open at this point, the main table and the new copy)

Tables/update.

Select the new copy in the dialog.

Pick the columns to match.

Click OK.

Now the data from the new table will over write the data in the Main table.

The new copy does not need to be a complete table. It might only contain a few rows, or only a few columns. Either way it updates to the Main table.

[#8]

Thanks for the detailed reply Byron.

The problem is that I cannot see the Excel file listed in the Update Table dialogue box, even when only the JMP dt and the Excel (updated) file are open. JMP still shows 3 file names with no relevance + they are not opened on the computer right now (please see the enclosed screenshot). Thanks

[#9]

Hi,
It is not clear to me if you have the Excel file open as a JMP table. It will need to be open as a JMP table for you to select it in the Update Table dialogue.
Regards,
Phil

[#10]

Thanks Phil. It works now. Previously, I missed the point that the Excel sheet needs to be opened as a JMP dt.