cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar

Wizard to update Excel files in place (no code solution)

The main data source for new JMP users will always be Excel. These can be messy, and probably used as database with manual input for KPI tracking, for example.

However, neither the Excel add-in, nor the Excel wizard provide a simple way to update file from its source.

This is a common request that can be found in this forum, multiple times, and all of them require scripting (see examples below).

You can see the understandable response of beginners, that are drag into a complex solution to a simple request.

Updating columns, adding new rows, adding new columns, all of these are complex concepts that require several steps and getting into automation via JSL or Automation Builder.

JMP needs to include a wizard to automatically generate a script that reads and updates the current data table in place.

https://community.jmp.com/t5/Discussions/Automatically-update-quot-source-quot-file/td-p/324299

https://community.jmp.com/t5/Virtual-JMP-Users-Group/How-to-update-a-JMP-data-table-to-reflect-Excel...

https://community.jmp.com/t5/Discussions/How-do-I-automatically-update-JMP-data-table-while-populati...

6 Comments
Status changed to: Acknowledged
 
FN
Level VI

JMP 18 has this option now. However, it deletes all columns that the user has created after the first import. 

The update must handle non-existing columns instead of deleting them, only updating those in the original (and formulas referring to those).

 

 

FN
Level VI

@Sarah-Sylvestre, just to make sure this is tracked and shared with the developers.

 

JMP 18 has this option but it is still not a 1-click operation. Now it deletes all columns that the user has created after the first import!

 

At the end, it forces users to learn JSL or perform repetitive tasks manually.

hogi
Level XII

Really interesting how hard jmp developers work to implement an update feature.

 

In our company, we are quite close to a solution - with this Reread 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 );

This short script relies basically on the power of Source scripts, which are automatically generated with every file import.

 

I claim:

It's more useful than many other approaches!

... almost perfect.

 

But:

1) unfortunately, there is a huge (!!) issue with this approach - a "Bug" in JMP:
Update & Concatenate: source script collision 

argh!!!

 

2) less severe:
If a user applies manual steps to the input table which makes it incompatible to the current data table,
... he has to add these processing steps manually to the source script.

For renaming of columns, there is a more clever approach:
🙏 Column Property: Column Name Alias 

 

 

@FN Yes, this feedback has been shared with the developer. Thank you for letting us know the feature was not what you expected. I will let you know if there are any planned updates. I will leave this wish open for now as it seems it has not been completely fulfilled. 

hogi
Level XII

candidates of the Wish List Prioritization Survey - pilot run:
Introducing the JMP Wish List Prioritization Survey!