BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
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);

 

4 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".

Community Trekker

@briancorcoran , @Dahlia_Watkins
       2 Questions . 
1. The argument table list - it looks like the argument actually cannot accomplish anything . Please see the example below , JMP creates a workbook with 3 tabs and all the open tables ignoring that I only passed the function a list of 2 tables . Is there a way to bypass this functionality and limit the creation to only the tables that are passed as argument .

dt1 =  Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 =  Open( "$SAMPLE_DATA/Air Traffic.jmp" ); 

openDTs = list(); 
for(i = 1 , i <= N Table(), i++, 
		Insert Into(openDTs,Data Table(i)<< Get Name)
   );

dt3 =  Open( "$SAMPLE_DATA/Animals.jmp" ); 

sheetList = {"BigSample", "Airsample"};

Create Excel Workbook("C:\MyWorkbook.xlsx",openDTs,sheetList);

Close All(Data Tables,"No Save");

2 . Is there any way to expand this beyond data tables ?  Is there a way to write lists / matrices as well with this function or any other function in JMP 13 ? 
   - If the answer is no , I would request that this be added as a feature in JMP 14 . 

Staff

Hi Uday,

 

Please contact Tech Support about this, rather than commenting in a blog post.  Make sure to let them know what version of JMP you are using, as well as the operating system that you are using.  Mac and Windows can behave differently.

 

Thank you for the code snippet.  It works properly on my installation of JMP 13.2.1 on Windows, which is why I think Tech Support is the way to go.

 

Regards,

Brian Corcoran

JMP Development

Article Tags
Labels