cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
JMP to Excel and back again -- all without losing any table attributes

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.

Catalyst Design Data Table with Notations.png

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

Select Export Workbook with Attributes.png

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

Export Data Table to Excel with Attributes.png

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.

Data Tab.png

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.

Column Attributes Tab.png

 

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.Table Variables and Scripts Tab.png

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

 Select Import Workbook with Attributes.png

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

Import Excel Workbook with Attributes.png

 

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.

 Imported Table.png

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!

 

 

Last Modified: Jun 23, 2017 3:26 PM
Comments
juna1
Level III

hi

I think it`s great tool.

but i encounter a problem when installing.

It can`t be intalled like other add-ins. when i double click or open it by JMP, no response for this add-in.

Can i ask if this add-in is only for JMP13 becasue i use JMP12 now.

thanks!


Justin_Chilton
Staff

Hi @juna1,

Yes, this add-in is only supported in JMP 13 since it utilizes features in JMP that were not available in JMP 12.

 

XiangCD_MP_User
Level IV
Dear Justin, thank you so much for the beautiful program. May I seek for your advice, is there any method to also export a table which contains figures/ charts to Excel ?
DanielYar
Level I

Dear Justin,

Is it possible to save a Table Script and used it later in order to adjust a new table (with new data) with the same columns in it?

Does the Table Script contain any possible calculated columns and will be able to create these when used to create a new table (with new data but the same initial columns)?

BEST, DANIEL

Marco2020
Level I

Hello Justin,

I am pleased to greet you, I use windows 10, excel 2019 and JMP pro 14, I would like to make predictions with new data entered to models trained in JMP, install the Table Attributes v2.1 add-on to export the JMP trained models to excel and be able to make new ones predictions, but I get an error, below you will see an image with the error, what am I doing wrong? .... greetings!

 

Marco2020_0-1604105082049.png

 

Justin_Chilton
Staff

Hi @Marco2020, would you mind clicking the Copy Details button in the error dialog and paste the results here?

Marco2020
Level I

Hello Justin,

I am pleased to greet you, a query .... can a model trained in JMP be converted to excel to be able to make predictions with new data? What is the procedure?

Cheers,

Marco

Marco1
Level IV

Hello Justin,

Good contribution!!!...Install Table Attributes v2.1, when exporting an NN model trained in JMP to excel, it creates the 3 sheets as you explain, but it does not predict when changing the input data in excel as it does in the JMP data table....what could be missing?
Cheers,

Marco