How do I automatically update JMP data table while populating Excel

Jeff1

My purpose here is to is to link a JMP data table to an Excel spreadsheet. While Excel users populate the spreadsheet, I would like the data table to automatically re-populate (upon opening or continually while open) with the new data. I want to eliminate the need to recopy the new data each time eliminating any risk of copy errors and ultimately validating the population.  Is this possible and if so, how is this performed?

I am not familiar with scripting; any help scripting or pointing to other blogs would be greatly appreciated.

Best regards,
Jeff

martindemel

To add on what Hadley has written. If I understood your demand, you do want to update the same JMP datatable with the new data in the Excel Sheet and not to just create a new table from the new Excel sheet. If the latter would be true Hadley is right, this would be the easiest way to go, using the automatically generated import script and rerun it everytime you want to get the new data (in a new data table).

 

In case it is what I believe you want to finally achieve, this might help:

Names default to here(1);

// Excel Sheet you get the new data from
excelFile = "Wetterdaten.xlsx";

// current data table - which has data from an earlier time point of the Excel file
cdt = Current Data Table();

// get the name of the JMP table and the current number of rows
cdtName = cdt << get name();
n_Rows = NRows();

// use the import script used when importing the Excel file the first time
// you need to adjust this if the Excel file will be changed in the structure or other columns have been added or placed to other positions.
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
);

// Now just concatenate the JMP Table with the new imported data - but only with the data after the number of rows from the current JMP table.
cdt << Concatenate  (dtHelp, Append to first table);

//  delete helping table
Close(dtHelp);

Attached is a zip file with a full example using some weather data, and an Import data, both ave this script included and you can run the script which will then call the excel file, grab the data, and add it to the open data table. Also there is a document explaining it again (unfortunately in German, but an online translater should do good work, I created this some years ago). 

 

The benefit is that all changes you have made in the data table like quality checks for the "old" data will persist and also new formula columns will work for the new data as well.  

 

Ah, one more comment to the Excel table. If you change in the Excel sheet data which has been imported already (aka after row number = number of rows in the JMP table) these changes will not be updated in the data table as we only import or better said add rows after the current ones.

 

 

UpdateFromExcel.zip

HowToUpdateTableFromExcelData.docx