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();