topic How to export JMP Data Tables as different Tabs of an Excel Sheet ? in Discussions

How to export JMP Data Tables as different Tabs of an Excel Sheet ?

b55019 — Fri, 10 Jul 2015 16:28:50 GMT

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 ?

briancorcoran — Fri, 10 Jul 2015 17:00:02 GMT

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

Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?

mpb — Fri, 10 Jul 2015 17:26:41 GMT

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 ?

b55019 — Fri, 10 Jul 2015 17:28:45 GMT

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

Re: How to export JMP Data Tables as different Tabs of an Excel Sheet ?

briancorcoran — Fri, 10 Jul 2015 17:30:47 GMT

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 ?

mpb — Fri, 10 Jul 2015 17:32:41 GMT

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 ?

briancorcoran — Fri, 10 Jul 2015 17:35:12 GMT

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 ?

briancorcoran — Fri, 10 Jul 2015 17:42:48 GMT

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 ?

b55019 — Fri, 10 Jul 2015 17:50:31 GMT

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 ?

briancorcoran — Fri, 10 Jul 2015 17:54:47 GMT

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 ?

jimloughlin — Fri, 10 Jul 2015 21:54:34 GMT

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 ?

briancorcoran — Mon, 13 Jul 2015 12:10:44 GMT

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 ?

b55019 — Mon, 13 Jul 2015 18:09:30 GMT

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 ?

mattflynn — Thu, 16 Jun 2022 15:49:55 GMT

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 ?

briancorcoran — Mon, 13 Jul 2015 20:21:06 GMT

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 ?

Isabel26 — Thu, 16 Jun 2022 15:00:31 GMT

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 ?

jthi — Thu, 16 Jun 2022 15:15:55 GMT

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 ?

brady_brady — Thu, 16 Jun 2022 22:31:07 GMT

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"} );