Table Attributes Add-In

[#1]

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

JMP Attribute Table

Create an attribute table for the current data table.

JMP Attribute Table

Add Attribute Table Script

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

 

 "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 Dialog

Exported Excel Workbook

Import Workbook with Attributes

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

 Import 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)

• 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)

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

Version 1.3 (June 27, 2017)

• 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)

• 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)

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

Version 1.6 (March 12, 2018)

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

Version 1.7 (April 6, 2018)

• Added the data type length to the Data Type column

Version 1.8 (April 10, 2018)

• 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.

Version 2.0 (January 9, 2020)

• Added warning messages for properties that were unable to be imported instead of failing to complete other properties
• Other architecture improvements.

Version 2.1 (January 10, 2020)

• Fix import of empty columns.
• Better warnings when number of columns in the data do not match number of columns listed in the attribute sheet.

Version 2.2 (February 28, 2024)

• Fix error message thrown for some tables on Mac

Version 2.3 (September 4, 2024)

• Fix JMP 17 bug where Excel Wizard incorrectly opens

[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

[Gerulf]

Hi Justin,

Thank you for the add-in. 

I am running into a problem. I can create the attribute table and export to Excel. However, when I want to import back into JMP (all done via the add-in), it creates a table with all the data but formulas for columns are no longer available. I also get this message:

Error Message:
{"could not find column"(1, 2, "Column", Column /*###*/(dt_meta, "Validation"))}

Call Stack:
importWorkbook
importPrompt

Can formulas be preserved? Can you advise?
Thank you.
Gerulf

[Justin_Chilton]

@DanielYar,

My apologies for not seeing your message sooner. Are you still experiencing this error? I am not sure how you would get in a state to see this error because it would mean a data table currently open in JMP has no name.

@Gerulf,

Thanks for reaching out. 

Are you editing the table after it is exported to excel? There should always be a Validation column in the output table and if it was removed, you would see this error. 

-Justin

[Gerulf]

Hi Justin,

Thank you for you reply. I did not alter tables. After running the script,
I can see a Validation column in the Attributes Table in JMP. I can also
see it once the file is exported into Excel (in the Attributes Tab). In
both cases it appears to be made of empty cells.

Once I import the Excel file back into JMP (using the add-in), the
Validation column is gone, plus I receive the error message.

Would you have further advise?

Thank you.

Gerulf

[Justin_Chilton]

Hi @Gerulf,

Could you download install the latest version (v2.0) that I just uploaded? It should do a better job at explaining what properties were not able to be applied. It should also be able to apply all properties it can, instead of stopping after the first property it fails on.

After you try this, could you post the warnings you are seeing, if any?

Thanks,

Justin

[Gerulf]

Thank you Justin,

There is progress. I do get the following error message for all columns:

"Unable to import Validation for 'Column 1' in table 'untitled.jmp' due to
missing attribute column"

This repeats for all other columns.

I do not find an Attribute column anywhere.

None-the-less, this time around I see the Validation column in the
re-imported attributes table, plus the formula in the re-imported data
sheet appears to be intact. So something is happening!

Sincerely,

Gerulf

[Justin_Chilton]

@Gerulf,

Ah, I didn't notice that you were trying to import the exported attribute table. Figuring that out helped me learn that I was not importing empty columns, which caused there to be a mismatch in number of columns when applying the column properties. 

Could you try v2.1 that should fix this issue?

-Justin

[Gerulf]

It works! Thank you so much Justin for all your help!! Gerulf

[CJenvey]

@Justin_Chilton Thanks for this great add-in! It has been working perfectly...until it wasn't.

The first column in my Excel spread sheet is dates set as dd/mm/yyyy, and I have confirmed the format in Excel before importing.

After importing into JMP15, some of the dates in the column are missing periodically e.g. rows 1-40 imported, but rows 41-107 didn't,108-160 did, 161-241 didn't, and so on. There are 487 rows of data. I've included a screenshot of part of my imported table below.

Do you have any ideas as to why some dates are importing and other aren't?

Thanks in advance for your help.

[Djtjhin]

@Justin_Chilton I was importing an excel file with the associated attributes and I got this error below. The JMP data table was still created but the table variables were all missing. Any ideas on what might have caused this ?

Error Message:

{"invalid argument"(1, 2, "Column", Column /*###*/(dt, j))}

Call Stack:

importWorkbook

importPrompt

[m-kennedy0]

Hi @Justin_Chilton

I, too am getting an error.

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

If its helpful, I'm on JMP 16.0.0, M1 Mac, OS 11.3.1

[ruskicar]

Hi Justin

I get the same error:

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

Is there any solution available?

I'm on JMP Pro 16, M1 Mac

[Justin_Chilton]

The error that was throwing on Mac is now fixed in the latest version (v2.2).

[emmablue]

@Justin_Chilton  would it be possible to export JMP into excel with row states like coloring and labeling of individual rows? Thanks!