topic Re: Is there a refresh option to update data table in JMP 13.2.1 in Discussions

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

abdul_saqib — Thu, 30 Nov 2017 15:02:21 GMT

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

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

Phil_Kay — Thu, 30 Nov 2017 15:14:31 GMT

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

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

abdul_saqib — Thu, 30 Nov 2017 15:48:16 GMT

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).

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

Phil_Kay — Thu, 30 Nov 2017 15:59:08 GMT

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

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

Byron_JMP — Fri, 01 Dec 2017 19:18:15 GMT

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

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

abdul_saqib — Mon, 04 Dec 2017 09:35:41 GMT

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

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

Byron_JMP — Mon, 04 Dec 2017 20:31:04 GMT

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.

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

abdul_saqib — Tue, 05 Dec 2017 14:11:56 GMT

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

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

Phil_Kay — Tue, 05 Dec 2017 09:46:07 GMT

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

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

abdul_saqib — Tue, 05 Dec 2017 10:46:22 GMT

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

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

martindemel — Thu, 04 Apr 2019 14:33:25 GMT

This script will help in a slightly different scenario (need to change "Wetterdaten.jmp" with the appropriate one as well the settings, best use the source script for the initial report):

If you update your table like Byron suggested you will keep the table properties but you will overwrite any data cleaning steps, like recoded (overwritten) columns, or filled missing values, .... They will be overwritten by the Update table.

Getting first the number of rows of the current table, and delete them from the imported one, then concatenate the final new table will avoid that painful awakening "all your work has been destroyed through the update.

// Define current data table (current data)
dt1 = Current Data Table();

// Check number of rows in actual data table (current recorded and cleaned data)
n_rows = NRows();

// Open Excel file to read new data -> will open a new data table with the same name and all the previous and new data
dt2 = Open("Wetterdaten.xlsx",
	Worksheets( {"Import Data"} ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 1 ),
	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 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 )
	)
);

// Delete in dt2 all previous worked on data using the n_rows information
dt2 << DeleteRows(1::n_rows);

// Concatenate tables: dt2 appended to dt1, so you avoid creating a new table
dt1 << Concatenate  (dt2, Append to first table);

// Close support table dt2
Close(dt2);

Of course you can feel free to further customize this script. E.g. I would go for a general import routine, where I chenge the setting for the Excel Import to the original number + the current number of rows already beeing imported. With this you can directly go to concatenate without the need for deleting rows. And I do not like hardcoded tablenames, so I would first check the table name and use this as table name in the Excel Import.

Just few thoughts ... :)

excelFile = "Wetterdaten.xlsx";

cdt = Current Data Table();
cdtName = cdt << get name();
n_Rows = NRows();

dtHelp = Open(
	excelFile,
	Worksheets( {eval(cdtName)} ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 1 ),
	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+ n_Rows ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 )
	), Invisible
);

cdt << Concatenate  (dtHelp, Append to first table);

Close(dtHelp);