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!