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
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
I may well be wrong but I thought this was possible using ODBC.
I tried something like this.
dt1= Data Table("Input");
dt1<<save database("DSN=Excel Files Aman 2;DBQ=C:\Users\b55019\Desktop\Yield.xls;DriverId=790;FIL=excel12.0;MaxBufferSize=2048;PageTimeout=5;","Raw Data");
Seems to give me an ODBC mismatch error
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
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.
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");
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
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
There seems to be Scripting Guide > Data Tables > Basic Data Table Scripting > Create Excel Workbooks (jmp.com) which should be able to do this
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"} );
