cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Jeff1
Level II

How do I automatically update JMP data table while populating Excel

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How do I automatically update JMP data table while populating Excel

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.

 

 

/****NeverStopLearning****/

View solution in original post

4 REPLIES 4

Re: How do I automatically update JMP data table while populating Excel

Hi,

 

The easiest and simplest way to do this might be to drag your excel file into an open JMP window, which will open the Excel Import Wizard.  From there, you can adjust the import settings and import the table in the correct format, which you can save.  The table will have a "source" script, which, when pressed, will re-import the table updated with the new data.  Does that help to achieve what you're trying to do?

 

 

Re: How do I automatically update JMP data table while populating Excel

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.

 

 

/****NeverStopLearning****/
Jeff1
Level II

Re: How do I automatically update JMP data table while populating Excel

This is exactly what I would like to perform.  I am not familiar with scripting.  Can we talk this through?

 

Thank you,

Jeff

Jeff1
Level II

Re: How do I automatically update JMP data table while populating Excel

Hi Martin,
Would you mind contacting me? I am almost there, but having difficulty completing the concatenation.

Best regards,
jeff_beckvermit@agilent.com