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.
Create an attribute table for the current data table.
Add a script to the current data table that will create an attribute table.
Export table(s) to an Excel workbook with sheets for column and table attributes.
Import table(s) from an Excel workbook with all column properties and table variables/scripts.
Check out my blog post JMP to Excel and back again -- all without losing any table attributes
This add-in is supported for JMP 13 and higher.
Version 1.1 (June 20, 2017)
Version 1.2 (June 21, 2017)
Version 1.3 (June 27, 2017)
Version 1.4 (November 21, 2017)
Version 1.5 (January 29, 2018)
Version 1.6 (March 12, 2018)
Version 1.7 (April 6, 2018)
Version 1.8 (April 10, 2018)
Version 2.0 (January 9, 2020)
Version 2.1 (January 10, 2020)
Version 2.2 (February 28, 2024)
Version 2.3 (September 4, 2024)
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?
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,
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.
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
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
Thanks, Justin. This worked like a charm.
Amy
Justin,
Can you get numeric datatype column to specify 1-byte, 2-byte and 4-byte integer if those are being used?
John
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
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
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
Works beautifully. Thank you for the really quick update.
Amy
Thanks for all your work on this add-in, Justin!!!
John
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
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
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.
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
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
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
@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.
@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
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
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
The error that was throwing on Mac is now fixed in the latest version (v2.2).
@Justin_Chilton would it be possible to export JMP into excel with row states like coloring and labeling of individual rows? Thanks!