How to export JMP Data Tables as different Tabs of an Excel Sheet ?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Hi,
I have a couple of JMP Data Tables open and would like to export these as different tabs of one excel sheet.
How can I do this in JMP script?
Thanks,
Aman
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?Hello,
The function for this is Create Excel Workbook(a, b, c), where:
a = path/name of Excel file to be created or modified,
b = a list of data tables to export, and
c = list of desired tab names.
Cheers,
Brady
Names Default To Here( 1 );
dt1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Abrasion.jmp" );
Create Excel Workbook( "$TEMP/MyWorkbook.xlsx", {dt1, dt2}, {"Big", "Abrasive"} );Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?There seems to be Scripting Guide > Data Tables > Basic Data Table Scripting > Create Excel Workbooks (jmp.com) which should be able to do this
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I also need to export files into different tabs in excel. I saw the 'no solution' solution is back to 2015, but do JMP add this function now 2022? Thanks
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I would contact Tech Support. They can make sure that your drivers are matching up with Excel and JMP properly.
Brian
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?Certainly there are _many_ ways to do this - One way is via R and the very handy RDCOMClient package.
// muitileExcelSheets.jsl
// adapting one example available here:
// https://learnr.wordpress.com/2009/10/06/export-data-frames-to-multi-worksheet-excel-file/
R Term();
R Init();
# send JMP data tables to R
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
R Send( dt );
Close( dt );
R Submit( "dt" );
dt2 = open("$SAMPLE_DATA/Tiretread.jmp");
R Send( dt2 );
Close( dt2 );
R Submit( "dt2" );
// http://www.omegahat.org/RDCOMClient/
RDCOMClient = R Submit("library(RDCOMClient)");
if(RDCOMClient==-1, throw("This Add-in requires the RDCOMClient R package."));
# kick them to Excel on separate sheets
R Submit("\[
library(plyr)
testlist <- dlply(iris, .(Species))
d_ply(iris, .(Species), function(df) {
assign(as.character(df$Species[1]), df,
envir = .GlobalEnv)
})
testchar <- as.character(unique(iris$Species))
testchar1 <- paste(testchar, collapse = ',')
testchar; testchar1;
library(RDCOMClient)
source('http://www.omegahat.org/RDCOMClient/examples/excelUtils3.R')
xls <- COMCreate('Excel.Application')
xls[['Visible']] <- TRUE
wb = xls[['Workbooks']]$Add(1)
# export one dataframe as multiple sheets (breaking on a column value)
rdcomexport <- function(x) {
sh = wb[['Worksheets']]$Add()
sh[['Name']] <- as.character(x$Species[1])
exportDataFrame(x, at = sh$Range('A1'))
}
d_ply(iris, .(Species), rdcomexport)
# exporting individual data frames converted from JMP tables above.
sh = wb[['Worksheets']]$Add()
sh[['Name']] <- as.character('Big Class')
exportDataFrame(dt, at = sh$Range('A1'))
sh = wb[['Worksheets']]$Add()
sh[['Name']] <- as.character('TireTread')
exportDataFrame(dt2, at = sh$Range('A1'))
]\");
// R Term();
Best regards,
-Matt
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I tried to do this on a windows machine . I am still getting the same error.
I wonder if there is a way in VB to do it .
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?Did you see open data tables? This error would typically be caused by dt and dt2 not opening or going out of scope. You may need to fix up the paths to the tables. Also, you must have a DSN registered that is called "Excel Files".
Brian
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I get the following error:
Send Expects Scriptable Object in access or evaluation of 'Send' , dt << Save database( "DSN=Excel Files;DBQ=C:\jimmy\html\jmp\Bigc.xlsb;", "BigC" )
In the following script, error marked by /*###*/
dt << /*###*/Save database(
"DSN=Excel Files;DBQ=C:\jimmy\html\jmp\Bigc.xlsb;",
"BigC"
) /*###*/
{}
I am running JMP10.0.2 32-bit and ms office 32-bit.
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I don't have 64-bit Excel available, so I can't try out that combination. You might want to try creating a new DSN with the 64-bit ODBC Driver Manager (the one in System->Administrative Tools->Data Sources (ODBC) . This is assuming you are using Windows. If you are on the Mac, I'm not sure what the issue might be.
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I have a 64 bit JMP and 64 bit Excel combination . The command doesn't seem to work .
Gives me the same ODBC mismatch error .
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?If you have the 32-bit Excel/32-bit JMP combination, something like the following will work:
dt = open("$SAMPLE_DATA/Big Class.jmp");
dt2 = open("$SAMPLE_DATA/Tiretread.jmp");
dt << Save database("DSN=Excel Files;DBQ=C:/Bigc.xlsb;", "BigC");
dt2 << Save database("DSN=Excel Files;DBQ=C:/Bigc.xlsb;", "Tires");
Brian
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I have both 32-bit and 64-bit JMP, but if you do not have 32-bit JMP then it is not going to work for you. Microsoft installers will not allow you to install 64-bit Excel ODBC drivers if you have 32-bit Office already installed.
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?Are your JMP and Excel(or ODBC?) both 32 bit or both 64 bit? Mine aren't so I can't explore this. For ODBC access to work you can't have one 64 bit and the other 32 bit.
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?Sorry, I thought you were referring to the normal File->Save As export mechanism. You could probably do it through ODBC, but if you have 32-bit Microsoft Office than you will need 32-bit JMP. Otherwise, the ODBC driver will not work.
Brian
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I tried something like this.
dt1= Data Table("Input");
dt1<
Seems to give me an ODBC mismatch error
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?I may well be wrong but I thought this was possible using ODBC.
Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?Hello Aman,
JMP does not currently support saving different data tables as worksheets within a single workbook. The exception is on Windows when a table exceeds the limit for the number of rows in an Excel sheet, it will try to open multiple sheets to accommodate all of the data. Sorry.
Brian Corcoran
JMP Development
