BookmarkSubscribe
Choose Language Hide Translation Bar

Table Attributes Add-In

Description

 

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.  This add-in 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.  This Add-In will also allow you to generate an attribute table for any JMP data table.

Menu Items

Menu Items2.png

 

JMP Attribute Table

Create an attribute table for the current data table.

Attribute Table.pngJMP Attribute Table

Add Attribute Table Script

Add a script to the current data table that will create an attribute table.

 Table Script.png

 Table Script.gif"Create Attribute Table" Table Script

Export Workbook with Attributes

Export table(s) to an Excel workbook with sheets for column and table attributes.

 

 Export Workbook.PNGExport Workbook Dialog

Excel Workbook.pngExported Excel Workbook

Import Workbook with Attributes

Import table(s) from an Excel workbook with all column properties and table variables/scripts.

 Import Workbook.PNGImport Workbook Dialog

 Check out my blog post JMP to Excel and back again -- all without losing any table attributes

 

System Requirements

This add-in is supported for JMP 13 and higher.

Updates

Version 1.1 (June 20, 2017)

Changelog:

  • Fixed issue where you get an error when canceling the selection of a data table.
  • Fixed issue where character columns with a formula caused an extra column to be created in the attribute table
  • Fixed issue where validation column properties caused an error when importing the a table.

Version 1.2 (June 21, 2017)

Changelog:

  • Added support for Mac operating systems.
  • Added a prompt when file being overwritten is locked by another process.

Version 1.3 (June 27, 2017)

Changelog:

  • Made a change so that progress bar appears when export process takes a while. Also, an alert message now appears when there are more rows in the data table than Excel supports within a single sheet.

Version 1.4 (November 21, 2017)

Changelog:

  • Added support for all** column properties.
    **Let me know if there's one that does not work, as I did not test them all.

Version 1.5 (January 29, 2018)

Changelog:

  • Added support for maintaining column groups when exporting/importing. 

Version 1.6 (March 12, 2018)

Changelog:

  • Fixed rare issue causing column names not to be imported properly.

Version 1.7 (April 6, 2018)

Changelog:

  • Added the data type length to the Data Type column

Version 1.8 (April 10, 2018)

Changelog:

  • Fixes some cases where formats were not applied correctly when importing from Excel workbook.
  • Removed extra Source script that was not in the original table when importing from Excel workbook.
Comments
jenkins_macedo

 What do you do in the event that the number of rows of the original JMP Data Table exceeds the maximum allowable number of rows that an Excel spreadsheet can take this is usually around 1 million and everything else gets dump into another Excel Spreadsheet. if that happens how is that going to affect your analysis?

Justin_Chilton

Hi @jenkins_macedo,

This add-in uses the Create Excel Workbook() JSL function to export to Excel. This is the same JMP functionality that can be found by clicking View > Create Excel Workbook. This function actually causes the data to be truncated in the output workbook.

In the previous version of this add-in (v1.2), there is a message written in the log that explains that the data has been truncated. I have updated the add-in file on this post so this message is now exposed in a JMP window.

I will look into adding the capability to split this data into multiple sheets in a future version.

Thanks,

phillips_ad_1

Justin - Is there a way to export what columns are grouped together in the columns window?  That is another area where I spend time.  I will organize the columns into section - particularly for files that have a lot of columns.  It would be nice to be able to keep that information when exporting and then importing using this add-in.

Justin_Chilton

Hi @phillips_ad_1,

Thanks for the good suggestion! I think that is definitely do-able. Let me take a look and I will get back to you when I have something for you to try.

-Justin

Justin_Chilton

Hi @phillips_ad_1,

Can you try out the new version (v1.5) I just uploaded? After exporting with this new version, the column groups will be maintained when importing back into JMP.

Let me know if you have any questions.

Regards,

Justin

phillips_ad_1

Thanks, Justin.  This worked like a charm.

 

Amy

john_madden

Justin,

Can you get numeric datatype column to specify 1-byte, 2-byte and 4-byte integer if those are being used?

John

Justin_Chilton

Hi @john_madden,

I just uploaded v1.7 of the add-in which adds the data type length to the data type column. For example, a 2-byte integer column will be represented by Numeric( 2 ). Unfortunately, the round-trip does not work for setting the data type length when importing from Excel. We hope to have this working in a future release of JMP.

Thanks,
Justin

phillips_ad_1

Justin,

 

I'm trying to import a column that is formatted with the date and time.  I'm able to export the column from JMP and I can see that it has the data format specified as "m/d/y h:m".  It even displays in excel in that format.  However when I import the data back into JMP it doesn't bring the formatting back in.  Is this related to your post last Friday?  You wrote that the round trip for the data type length doesn't work when importing from excel.  Is this also true for the data format?

 

Amy 

Justin_Chilton

Hi @phillips_ad_1,

Thanks for pointing this out. 

This issue is not related to the data type length. The formats were actually applied correctly, but in some cases they were overwritten after setting another property of the column. This should be done in the correct order now in v1.8 that I just uploaded.

Let me know if this works for you.

Thanks,
Justin

phillips_ad_1

Works beautifully.  Thank you for the really quick update.

 

Amy

john_madden

Thanks for all your work on this add-in, Justin!!!

John

DanielYar

Hi Justin,

Trying to export the JMP table to Excel I recieve an error.

Error Message: {"argument should be character"(1, 2, "Length", Bad Argument(.), Length /*###*/(openTables[i]))} Call Stack: tableSelectPrompt

 

However, creating an Attribution Table works fine.

Best, Daniel

Article Labels
Article Tags
Contributors