JMP 18 new Update Table script overwrites table

[#1]

When importing Excel tables into JMP 18 I get two scripts 'Source' and 'Update Table' script, only difference seems to the an 'Update Table (1)' command.

The second script performs an update from the Excel file, and overwrites any columns created after import. Is there a way to make it leave new columns alone when updating? Did not find anything in the Scripting Index.

[#2 · Accepted solution]

hi @Ake, the idea of update at the moment: replace the existing data with the new data from the input file.

As you describe - this is quite destructive.

The idea behind it might be something like:

One cannot guarantee that existing values in additional columns match the newly imported data - so better delete them. Hm, for columns with formulas, I don't see any reason why such columns should be deleted.

Much better than the Update Script: 

dt = Current Data Table();
dtnew = dt << Run Script( "Source" );
dt << Select All Rows << Delete Rows;
dt << Concatenate( dtnew, Append to first table );
Close( dtnew, NoSave );

It will still delete manually added values, but it keeps formula columns : )

The drawback: it relies on the source script - which is also very fragile :Update & Concatenate: source script collision .

Alternatively, you could have a look at Join, Concat and Update .

[#3]

Thanks! If I just copy the source script into your script  it gets less fragile.

I have been using a more complicated method, adding required number of rows and then updating. Your script is cleaner.

// Compare and add number of rows if needed
nrows1 = N Rows( dt1 );
nrows2 = N Rows( dt2 );
If( nrows2 < nrows1,
	dt2 << Add Rows( nrows1 - nrows2 )
);

// Update dt2
Data Table( dt2 ) << Update( With( Data Table( dt1 ) ) );
Close( dt1 );

dt2 << save();

[#4]

the danger of this script: it's inherited via concatenate:

user A: imports an Excel file

user B: concatenates 2 files

user C: wonders about missing data - and tries "Update Table"

from Disable "Update Table" script? 

please vote here: Option to disable creation of "Update Table" Script 

[#5]

Hello, I like this fix a lot, one question though - when I update table with this script it automatically adds the generic update table script which that can then break the formula columns. Is there a way to disable to automatic generation of the new "update table" script that is added to the jmp table?

[#6]

You can avoid that by deleting or overwriting the "Update Table" script within the script.  Here's one way, if your custom "Update Table" script is named "Update Table."

dt = Current Data Table();
update_script = dt << Get Script( "Update Table" );
dt << Delete Scripts( {"Update Table"} );
dtnew = dt << Run Script( "Source" );
dt << Select All Rows << Delete Rows;
dt << Concatenate( dtnew, Append to first table );
Close( dtnew, NoSave );
Eval(
	Substitute(
			Expr(
				dt << Set Property( "Update Table", __update_script__ )
			),
		Expr( __update_script__ ), Name Expr( update_script )
	)
);

Or you can name your script something else ("Update") and just include a line that removes "Update Table" after the update.

dt = Current Data Table();
dtnew = dt << Run Script( "Source" );
dt << Select All Rows << Delete Rows;
dt << Concatenate( dtnew, Append to first table );
Close( dtnew, NoSave );
dt << Delete Scripts( {"Update Table"} );