JMP to Excel and back again -- all without losing any table attributes

[#1]

Have you ever needed to share data and collaborate with a colleague who is not a JMP user? Perhaps you export data to Excel for those colleagues, only to lose the JMP formulas, table scripts, and other column properties when you bring the updated data back to JMP. Here's an add-in that solves this problem. It enables you to export a JMP data table to an Excel workbook along with the table and column properties of the original JMP table in a separate worksheet. Your colleague can then update or modify the data to send back to you. When you import the workbook back into JMP, all of your table scripts, column properties, and even column formulas will be automatically restored.

Installing the Add-In

To follow along below, download the Table Attributes Add-In from the File Exchange.

Exporting a JMP Data Table to Excel with Table Attributes

Step One:  Open data table(s) you wish to export to Excel

You can export multiple JMP Data Tables into a single workbook; however, in this example I will be exporting a single Data Table from the JMP sample data library, Catalyst Design. Below I have labeled on the table which properties will be exported and which will not.

Step Two:  Click Add-Ins > Table Attributes > Export Workbook with Attributes

Step Three:  Select the data table(s) you wish to export

You can opt to exclude table variables and scripts in the output. However, they will not be available when imported back.

Additionally, you can also deselect the checkbox to prevent the add-in from opening the workbook after exporting.

Step Four:  Click ‘Save’ and choose an output location

The Excel Workbook has now been exported with three sheets per JMP Data Table (one for the data, one for the column properties, and one for the table variable scripts and variables, unless excluded).

Examining the Exported Excel Workbook

If the ‘Open Excel Workbook’ check box was left checked, the Excel Workbook will open automatically in Excel after being exported. Otherwise, you can go to Windows Explorer/Finder to open the workbook.

Sheet One:  The Data

The first sheet will contain the actual data from the source JMP Data Table.

Sheet Two:  Column Attributes

The second sheet contains the attributes for the source JMP Data Table. Do not edit this sheet as it may result in errors when importing back into JMP.

Sheet Three:  Table Variables and Scripts

The third sheet will contain the table variables and scripts for the source JMP Data Table. Do not edit this sheet as it may result in errors when importing back into JMP.

Importing a Previously Exported Excel Workbook to a JMP Data Table with Table Attributes

Step One:  Click Add-Ins > Table Attributes > Import Workbook with Attributes

 

Step Two:  Select Excel Workbook from Windows Explorer/Finder Prompt

Step Three:  Verify data, attributes, and variable sheets were identified successfully and deselect any unwanted tables

Step Four:  Click ‘OK’ to import the data table

The Result

The imported table looks like the original table, apart from missing column groups and row states. I hope to include column groups in a future version of the add-in. However, I do not plan to export/import row states due to possible changes in the data after being exported to Excel. If you have row states in your table, you may want to consider adding a table script that contains the conditional logic to set the row states for your table.

 

Extras

If you are just interested in viewing all the column properties for one of your JMP Data Tables, this add-in can generate this in data table form using the ‘JMP Data Table’ menu item. The add-in also has the option of adding a table script to a data table that regenerates the attribute table.

I hope you find the Table Attributes Add-In useful!