Subscribe Bookmark RSS Feed

Updating JMP Data Tables

Steven_Moore

Super User

Joined:

Jun 4, 2014

I have an Excel table that I use to update a JMP data table 2-3 times per week.  However, after retrieving the Excel file, the source script gets changed when I use the Tables platform to perform the update.  The source script changes to the steps done in the Tables update platform.  Why is this happening?  Am I missing a step?

Steve
9 REPLIES
Steven_Moore

Super User

Joined:

Jun 4, 2014

Anyone....anyone....anyone?

I did some experimenting with the update function.  It appears that JMP overwrites the script saved as "Source" in the data table.  The FIRST time you update, everything works fine.  HOWEVER, when the update is envoked, the script saved as "Source" is immediately overwritten by JMP with the script to update the original table from the new table.  The next time you try to update from the "Source" script, nothing happens.  So, I tried this:  I renamed the "Source" script in the data table to "Update" and tried again.  This time, the "Update" script was not changed, but a new script called "Source" was added to the data table.  This "work-around" is obviously NOT efficient and should not be necessary.

Steve
saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Could you supply with Excel/jmp/jsl files to understand better? I am also curious

Steven_Moore

Super User

Joined:

Jun 4, 2014

saitcopuroglu,  I ended up calling JMP Tech Support.  It turns out, the "workaround" I outlined above is the only solution right now for the situation of updating from an Excel spreadsheet.  I was told the update function is designed for updating from one JMP data table to another.  However, the issue will be submitted as an idea for a future version of JMP.

Steve
mdawson69

Community Trekker

Joined:

Aug 26, 2015

Based on your first two posts in this thread, I had no idea that you were referring to the table Update feature in JMP. I have actually been using Update quite a bit recently and I have never had the impression that it would work with anything other than JMP data tables; it is in fact accessed either via the (Data) Table toolbar or the (Data) Table menu. Also, your original posts did not provide a detailed description of what you were doing, hence the reason no one responded.

In your situation where you routinely update a table in JMP with new data from Excel, I would write a script that imports data from the Excel workbook into JMP then update your maintained data table with the data table created from the Excel workbook. That stated, having the ability to do joins—which is what the Join, Update, and Concatenate functions perform—with external data sources may be an interesting feature to add. JMP already has this capability with external databases with its SQL support, but that only works with SQL databases. Excel is not a database, let alone an SQL database, so SQL querying cannot be exploited in your case. JMP definitely has no current one-click feature to directly query worksheets in an Excel workbook for a data join.

DVLuna

Occasional Contributor

Joined:

Jun 10, 2017

I see that this is an old thread, but it appears to be the only one I can find related to the issue I am encountering.

 

I am a new JMP use and have 13.1.  As I am learning how to use JMP, I have been importing datasets that I use for metrics tracking and reporting.  I can import the excel files and create scripts to perform the analysis.  Works great!

 

Now the issue:

 

The source data for this effort is updated anywhere from weekly to monthly.  My goal is to setup JMP so that I perform the same initial analysis on the data each month, but after updating the JMP table to the latest excel data.  I have tried using the "source" script, but that just creates a new JMP table that does not contain the scripts I save to the original JMP table.

 

I have read about the "update" function, but that appears to be a function to update the table if data within the table has changed.  Is the "update" funciton the only available method to "re-sync" to or "import" and update/growing over time source data set?

 

Any help would be greatly appreciated.

 

Dave

txnelson

Super User

Joined:

Jun 22, 2012

Dave,

Depending on what your specific needs are, the Update platform may be the solution.  However, JSL will allow you to do just about anything you need, and then end up with those original scripts in your reformed data table.  It would be a fairly simple piece of code, that copies the embedded scripts from your original table into you newly formed table.

Jim
ian_jmp

Staff

Joined:

Jun 23, 2011

Since you have JMP 13, you could also do it like this:

NamesDefaultToHere(1);

// Make some 'new data' (imported from Excel)
dt2 = New Table( "New Data",
					Add Rows( 10 ),
					New Column( "x",
						Numeric,
						"Continuous",
						Formula( Random Normal() )
					),
					New Column( "y",
						Numeric,
						"Continuous",
						Formula( Random Integer(10, 20 ) )
					)
				);
dt2 << runFormulas;
Column(dt2, "x") << deleteFormula;
Column(dt2, "y") << deleteFormula;

// Make some 'old data' containing a saved script (stored as a JMP table)
dt1 = New Table( "Old Data",
					Add Rows( 20 ),
					New Script(
						"y vs. x",
						Graph Builder(
							Variables( X( :x ), Y( :y ) ),
							Elements( Points( X, Y, Legend( 5 ) ), Smoother( X, Y, Legend( 6 ) ) )
						)
					),
					New Column( "x",
						Numeric,
						"Continuous",
						Formula( Random Normal() )
					),
					New Column( "y",
						Numeric,
						"Continuous",
						Formula( Random Integer(1, 10 ) )
					)
				);
dt1 << runFormulas;
Column(dt1, "x") << deleteFormula;
Column(dt1, "y") << deleteFormula;

// Update dt1 from dt2 using data table subscripting (requires JMP 13 or later)
Wait(3);
firstNewRow = NRows(dt1) + 1;
dt1 << addRows(NRows(dt2));
dt1[firstNewRow::NRow(dt1), 0] = dt2[0, 0];
DVLuna

Occasional Contributor

Joined:

Jun 10, 2017

OK,

 

Thank you for the quick responses. My problem is a little different than the concept in the samples you provided, but you have given me the insight I needed to move significantly forward.

 

In your solution above, I can now ADD the complete contents of the new data file to the old datafile, BUT, my issue is related to "moving" to a leter version of updated data in that my NEW file simply has additional data rows beyond the first.  It is not a matter of connecting/correlation of two seperate data sets.

 

Your sample did give me the understanding that when I execute the "Source" script in the original table (which is actually pointed at the new/updated source data file of the same name and location as the original data) I get a new data table with only the "Source" script present.  I was lost when I now had a new table with the current/updated data as desired, but none of my other scripts.

 

In your sample, you ended the data merge with the "now copy your scripts from the old table to the new table".

 

Light bulb moment....you can do that?

 

So, searching the forums I found some examples of listing the scripts in one table and inserting them into another.  At some point I will figure out how to link to other discussion so I will be able to point to references such as this, but for now, here is the code in the sample:

 

for a single script:

 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
scriptName = "Oneway";
scriptDef = dt << get table variable( scriptName );
dt2 = New Table( "little class" );
Eval(
Parse(
Eval Insert(
"
dt2 << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);

 

for all scripts in a table:

 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = New Table( "little class" );
Wait( 3 );
lstScriptNames = dt << Get Table Script Names;
Wait( 3 );
j = N Items( lstScriptNames );
For( i = 1, i <= N Items( lstScriptNames ), i++,
// scriptName = lstScriptNames;
scriptName = lstScriptNames( i );
scriptDef = dt << get table variable( scriptName );
Eval(
Parse(
Eval Insert( "dt2 << New Script( \!"^scriptName^\!", ^scriptDef^ )" )
)
);
);

 

Now - I made a copy of the "Source" script, added asignments of the tables to dt variables  and then attempted to follow the open command with the loop to copy all scripts. I was unable to get the scriptName and scriptDef values to load correctly.  scriptName appears to load the correct list of scripts in the current table, but the scriptDef call does not pull in the defintions.

 

I therefore manually added each of my test scripts with repeated manual single copies which worked wonderfully.  Here is my resuilting code:

 

dtCurrent = Current Data Table();
dtUpdate = Open(
"Z:\JMP Data Analysis\SLOC Metrics\Data Update Testing\Project_Metrics.xlsx",
Worksheets( "Code_Change_Metrics" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
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 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
scriptName = "Mod vs Unmod Code Change Sample";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "Individual & Moving Range chart of % Code Change";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "Total Lines vs. Asset / Code Analyzed";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "% Code Change vs. Asset / Code Analyzed";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "UpdateDataTable";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "TestingTableUpdateScript";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "CopySingleScriptFromOldTable";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);
scriptName = "CopyAllScriptsFromOldTable";
scriptDef = dtCurrent << get table variable( scriptName );
Eval(
Parse(
Eval Insert(
"
dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )
"
)
)
);

 With my original JMP table file open (created with the last version of the source data file) I can now execute this script which creates a NEW TABLE from the updated source data file and then copies all of the scripts over to the new table. I then have TWO JMP tables open, both with the same titles, both with the same scripts, but one with the old data and one with the new data (more data).  I now close the old table and SAVE the new table right over it in the same location.

 

BOOM!  Updated data with all of the scripts available for execution. Same file location and name so the process can repeat with each weekly or monthly report update as needed.

 

I would really like to get the automated loop for loading all scripts dynamically working so I can use the same script on other datasets without having to manage script names in the actual code.  For right now I am moving forward with the manual version until I figure out the issue with the loop.

 

Thank you so much for your quick response and samples which lead me to a solution in the end.

DVLuna

Occasional Contributor

Joined:

Jun 10, 2017

I figured out the issue with the loop to automate the process of pulling the script names from the list and it now will dynamically create the updated table and move whatever script are contained in the original table into the new table.

 

I have commented the final version and have included it below if anyone else needs to use this solution.

 

Thanks for all your help.

 

Dave

//
// The purpose of this script is to create an updated JMP data table based on the same source
//  data file used to create the JMP data table currently open.
//
// This script is used when you have created a JMP data table from a source data file that is 
//  updated over time and will continue to "grow" with new data as time progresses. Examples of 
//  this type of file is a monthly time accounting record or a periodic production report.
//
// This script is focused on source data that is "added to" as time progresses and is not intended
//  for source data that contains only data for the given period with no historical data retained.
//
// The script utilzes the original "Open" command found within the "Source" script of the JMP data table
//  and then copies over all of the scripts from the original data table into the new "updated" data table.

dtCurrent = Current Data Table();

// copy the Open command from the "Source" script of the main data table
// this is only required once as long as the name and location of the source data file remains unchanged
dtUpdate = Open(
	"Z:\JMP Data Analysis\SLOC Metrics\Data Update Testing\Project_Metrics.xlsx",
	Worksheets( "Code_Change_Metrics" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	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 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

// end of the Open command from the "Source" script of the main data table
// don't forget to add the semicolon after the closing ")" above since we are added more commands below


// Get the list of scripts that are contained within the main data table
lstScriptNames = dtCurrent << Get Table Script Names;

// Loop through all the script names and move them to the updated dat table
For( i = 1, i <= N Items( lstScriptNames ), i++,
	
	// pull each script name from the list of scripts
	scriptName = lstScriptNames[ i ];
	
	// By opening the updated data file with the copied Open command, we already created the "Source" script
	IF(scriptName != "Source",
		
		// Get the "contents" of this script from the main data table
		scriptDef = dtCurrent << get table variable( scriptName );
		// Insert this script into the updated data table
		Eval(
			Parse(
				Eval Insert( "dtUpdate << New Script( \!"^scriptName^\!", ^scriptDef^ )" )
			)  // end Parse statement
		);  // end Eval statement
	)  // end IF statement
	
);  // continue For Loop until all scripts in the main data table have been copied


// At the completion of this script, you will have two data tables open with exactly the same name. 
//
//  The first table is the "main" table that contains the old data and all the source scripts. 
//  When you attempt to close this table you will NOT be prompted to "save" the table since we made 
//  no changes to it.  The "main" table must be closed prior to closing and saving the "updated" table since
//  we are going to save the "updated" table in the same location with the same name as the original "main"
//  table since we are "replacing" the old table with the update version.
//
//  The second table is the "updated" table which we created with the above script. 
//  This table has been loaded with the "updated" data file found in the "Source" script. It now also
//  contains all of the scripts that were contained in the "main" data table for continued use with the
//  updated source data. You will note that is has exactly the same table name as the "main" table, but if you
//  inspect the rows of data you will find the only difference between the two table is that the "updated" table
//  contains the additional rows of data from the updated source data file.
//
//  The objective is to close the "main" table which should not prompt for a save, then close the "updated" table 
//  save it at the prompt over the original "main" table.  When closing the "updated" table, you do have the option
//  save it to any location you desire with any name you desire.  My goal for creating this script was to replace
//  the original table with the updated table.
// 
//	I will note here that I assumed I could not add the close of the main table and the save
//  of the updated table to this script since by closing the main table (which must be done first)
//  I would be closing the table in which this executing script is located. As such I assume the close action 
//  would stop the script.