750097_en-US

liliconcom

Trying to write script that will append results of regression analysis to an existing excel spreadsheet

Hi - I am trying to write a jsl script that will append the results of a regression analysis to an existing excel file.  My current script writes to an excel file but I want to be able to run the script multiple times and append each new result to the existing excel sheet.  (Currently I am appending the new result manually, by hand.)   See attached script. 

 

Thanks in advance!

 

ps  I am using JMP Pro 17

Automation and ScriptingRe: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

Excellent - thank you, Jim!

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

Here is an example script that I hope clears up what you need to do to concatenate new data to a master file 

names default to here(1);
// Create 2 data tables and save them as excel files
// one will be the master and one will play the role of the new data
dtExample = open("$SAMPLE_DATA/big class.jmp");
theTables =dtExample << Subset(
	By( :sex ),
	All rows,
	Selected columns only( 0 ),
	columns( :name, :age, :height, :weight )
);
close( dtExample, nosave );
// Change the names of the first table to Master
theTables[1] << set name("Master");
data table("Master") << bring window to front;
// Add the new columns to the master, like your master file already
// has all of the columns.  In this example, I will only add 1 column
data table("Master")<< new column("Ratio", set each value(:height/:weight));
// Wait 5 seconds so you can observe the master table
wait(5);
// save and close the Master table as an excel table
close( data table("Master"), save("$TEMP/master.xlsx"));

// Change the name of the second table to New Data
theTables[2] << set name("New Data");
data table("New Data") << bring to front;
// wait 5 seconds here too, so you can observe the New Data table
wait(5);
// now save and close the New Data table as an excel table
close( data table("New Data"), save("$TEMP/New Data.xlsx"));

// Now that there are a Master and a New Data table that have been
// created we can now illustrate how to read in some new data,
// add the required new columns, save it, then open the master file,
// and concatenate the new data to the master and then save the updated
// master file

// open the new data table.  I am using the variable dt as the pointer
// to the file, since that is what you are doing in your script
dt = open("$TEMP/New Data.xlsx");
// Add the new columns (in this example, only 1 column) to the data table
// your syntax is
//      new column("ratio", formula(:height/:weight);
// I suggest that you become more precise and point to the specific data table
// by using either
//      dt << New Column(......);
// or
//      data table("New Data") << New Column( ...... );
data table("New Data") << New Column( "Ratio", formula(:height/:weight));

// Now the data can be saved
// use either
//     dt<< save(...........);
// or
//     data table("New Data")<< save(................);
dt << save("$TEMP\test1 n=22.xlsx");

// now open the master table
dtMaster = open("$TEMP\master.xlsx");
// concatenate the new data table to the master
dtMaster << concatenate( dt, append to first table(1));

// now save the master
dtMaster << save("$TEMP\master.xlsx");

// uncomment the below code if you want to close the new data and the master tables
// close( dt, nosave );
// close( dtMaster, nosave );

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

Unfortunately, that didn't help.  The concatenate script is still not working.  (Curious, if the script automates the process, I'm not sure why I would need to click a check box on a popup window each time I run the script.) 

 

Also (probably not related to the concatenate script not working), for some reason a relict (now deleted file) is being opened at the outset of the run, instead of the file that the script says to run.   I can't figure out why that is happening.

 

I then run the regression, add some columns based on the results of the run, and then....

 

 

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

Try selecting the check box labeled  "Append to First Table"

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

This is what opens after the run.  (Curiously, it shows a relict file name (v2n=25....) that my jsl code does NOT use - I've checked and rechecked).  In any case, the following code - shown below - is not producing concatenated rows.

 

 

dt << save("C:\Users\trcampbell\Desktop\MASTER ECOLI\2024\regression analysis\validation runs\testtest2 n=258.xls");

///ADD APPEND SCRIPT

Concatenate("C:\Users\trcampbell\Desktop\MASTER ECOLI\2024\regression analysis\validation runs\masterfile n=258.xls") ;

//SAVE MASTERFILE CONTAINING ROWS (APPENDED) OF ALL RUNS

dt<< save("C:\Users\trcampbell\Desktop\MASTER ECOLI\2024\regression analysis\validation runs\masterfile n=258.xls");

 

Any suggestions would be most appreciated.

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

yes

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

Thanks Jim.  If the script that runs the analysis and adds tables produces a file named X, does X first have to be saved prior to concatenation?   And if so, is the table name within my Concatenate function the file that receives the results of all the runs (i.e. Concatenate (masterfile)?   

 

So if the above is correct, I would say:

save X

concatenate (masterfile)

save masterfile

re-run script 

 

Am I reading this correctly?

 

Re: Trying to write script that will append results of regression analysis to an existing excel spreadsheet

It looks like what you need to do, is that after you have created the new columns in your data table, you need to read in the excel table you want to add the data to, use   Tables=>Concatenate  to add the new table to the table you want to add the data to, and then save that newly concatenated.