Subscribe Bookmark RSS Feed
briancorcoran

Joined:

Jun 23, 2011

Combining JMP tables into an Excel Workbook

Microsoft Excel has the notion of collecting sheets of data (Worksheets) into a single book (Workbook). JMP has historically not had this notion of a collecting data tables.

A request that I have heard fairly frequently is that it would be nice to be able to save a collection of JMP data tables in a single Excel workbook. JMP 13 provides this new feature.

The first thing that you will need to do is to make sure that all of the tables that you wish to save are open within JMP. Once you have done that, you can select “View -> Generate Excel Workbook”.  This is the same for both JMP for Windows and JMP for Mac.

You will be presented with the dialog that allows you to customize the Workbook that you will create:

Excel_Select_Tables_1.png

In this example, I have some JMP tables that were created by importing some movie data that we use in our Query Builder demonstration. By default, all of the visible data tables will be included in the list. If you wish to exclude a table from the Workbook, just uncheck the leftmost check box. 

I’m not really thrilled with the prefixes on the names in this case. So, to change the name that will be used for the Excel worksheet, just click in the Worksheet Name edit field and change it:Excel_Select_Tables_2.png

I’m also not happy with the Workbook name in this case, so I select the edit box under "Workbook Name:" and replace the provided name. By default, JMP will use the name of the current, topmost data table. In this case that is g6_Rentals. 

I also don’t want to save to the Documents directory, so I can use the Choose button to bring up a directory selection dialog:Excel_Select_Tables_3.png
Now I’m ready to save out my Workbook. At this point, I can just press the Save button, and the Excel Workbook will be generated with all of my customizations. One important point to note is that only Excel 2003+ format (.xlsx) is supported for export by this feature:

Excel_Select_Tables_4.png

There is also a JSL interface for this feature. The example below generates the same table that we created above, except to the desktop of my Windows machine. Once again, the tables to save must already be open. In this example, I have created the lists of tables and sheet names prior to the “Generate” call, but this is not required.

tableList = {"g6_Movies", "g6_Rentals", "g6_Customers"};
sheetList = {"Movie Titles", "Rental Data", "Customer Information"};

Generate Excel Workbook("$DESKTOP\MovieRentalInformation.xlsx", tableList, sheetList);

 

2 Comments
Community Trekker

Good afternoon.  I am having trouble getting this feature to work at all, and wondered if you had any insight.

I am running 64-bit JMP13.2.1 on a Windows 7 machine.  Please see copy of log text below from my attempts to run the following script:

names default to here(1);
clear log();
close all(data tables, no save);

dt1 = new table("thing1",
	new column("This","character","nominal")
);

dt2 = new table("thing2",
	new column("That","character","nominal")
);

Generate Excel Workbook ("$Desktop\things1&2.xlsx", {"thing1","thing2"}, {"thing1","thing2"});

Name Unresolved: Generate Excel Workbook in access or evaluation of 'Generate Excel Workbook' , Generate Excel Workbook(
    "$Desktop\things1&2.xlsx",
    {"thing1", "thing2"},
    {"thing1", "thing2"}
) /*###*/

In the following script, error marked by /*###*/
Names Default To Here( 1 );
Clear Log();
Close All( data tables, no save );
dt1 = New Table( "thing1", New Column( "This", "character", "nominal" ) );
dt2 = New Table( "thing2", New Column( "That", "character", "nominal" ) );
Generate Excel Workbook(
    "$Desktop\things1&2.xlsx",
    {"thing1", "thing2"},
    {"thing1", "thing2"}
) /*###*/;

 

Unfortunately I am discovering this problem after writing a much larger script whose final step is to export multiple tables into Excel.  I am getting the same error message with the other script.

Any help is appreciated.

Community Trekker

Solved my own problem: the command in my version of JMP is "Create Excel Workbook".

Article Tags