topic Re: Paste data from JMP to Excel in Discussions

Paste data from JMP to Excel

rinosaur — Wed, 17 Jan 2018 12:41:52 GMT

Hello,

I currently have a script which automatically populates a JMP table with Excel data, then runs a non-linear regression and saves the fitted data. I would then like to copy these new columns back into the same, open, Excel document. Is there a simple way to select the new columns, and then copy the data to the clipboard using JSL so I can paste it back in Excel?

Cheers,

Re: Paste data from JMP to Excel

uday_guntupalli — Wed, 17 Jan 2018 13:03:52 GMT

@rinosaur ,
Hello. The following piece of code might be useful in achieveing what you are after :

// Open Sample Data
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );  

// Specify Output Folder Location - edit per your needs 
OutputFolder = "C:\";

// Get List of Columns 
ColNames = dt << Get Column Names("String"); 

// Specify Columns you desired to copy and paste into excel 
DesColumnsList = {"age","sex"}; 

// Loop through columns to select columns you want 
for(i = 1, i <= N Cols(dt), i++,
		If(Contains(DesColumnsList,ColNames[i]),
				Col = Column(dt,i); // Get Reference for Column 
				Col << Set Selected(1); 
		  );
   );
   
// Subset Selected columns 
dt_New = dt << Subset(Selected Rows(0),Selected Columns(1));  

// Save Desired Table 
Close(dt_New,Save(OutputFolder || "test.xlsx"));

Re: Paste data from JMP to Excel

rinosaur — Wed, 17 Jan 2018 13:09:23 GMT

Hi @uday_guntupalli

This is similar to what I was thinking, making the subset and copying from there. But this would make a new workbook, correct? I am looking to copy the subset to the clipboard and then later on paste into the original workbook if possible.

Re: Paste data from JMP to Excel

uday_guntupalli — Wed, 17 Jan 2018 14:40:36 GMT

@rinosaur,

How about this ?

// Opem Sample Data
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// Write to Excel 
Create Excel Workbook("C:\Test.xlsx",{"Big Class"},{"Big"}); 

// Open Another Sample Data Set 
dt1 =  Open( "$SAMPLE_DATA/Air Traffic.jmp" );

// Write both tables to existing workbook

Create Excel Workbook("C:\Test.xlsx",{"Big Class","Air Traffic"},{"Big","Air"});

However, in your case , you will directly append the data into a new tab in Excel to your workbook using the last step .
So - open you excel workbook as a JMP data table , then have a second data table with the data you want to write and then use Create Excel Workbook as shown above to write to Excel.

If you want all the data in the same tab , just join the JMP data tables and write to Excel . Hope this helps .

Re: Paste data from JMP to Excel

David_Burnham — Wed, 17 Jan 2018 15:53:29 GMT

You need to scan the table and build up a text string that you can put into the clipboard using the Set Clipboard function.

Use the tab escape (\!t) sequence to separate columns and the new line escape sequence (\!N)to create rows

Set Clipboard( "col 1 row 1\!tcol 2 row 1\!tcol 3 row 1\!Ncol 1 row 2\!tcol 2 row 2\!tcol 3 row 2" );