jmp script to export data sheets to Excel with sheet names longer than 31 characters

[#1]

Hi,

I am trying to export some JMP data tables to Excel (one Excel file with separate tabs for each sheet). However, some of the data sheet names are longer than 31 characters which Excel does not support as tab names. If I try to use the 'Export' option in JMP it works nicely with names longer than 31 characters truncated. But if I try to use 'Create Excel Workbook ()' in a JMP script the software crashes.

Would anyone be able to help me with the script to use to export to Excel while truncating sheet names that are longer than Excel allowed 31 characters please?

Thanks

[#2 · Accepted solution]

You can use Substr to get a part of string and dt_ref << get name to get name of datatable. Below is example which should get you started (requires JMP16 due to Transform Each being used):

Names Default To Here(1);
dt1 = Open("$SAMPLE_DATA/Big Class.jmp");
dt2 = Open("$SAMPLE_DATA/Abrasion.jmp");

dt1 << Set Name("veryveryveryveryveryveryveryveryveryveryveryveryveryveryveryverylongname");
dt2 << Set Name("notsolongname");

dt_list = {dt1, dt2};
dt_names = Transform Each({dt_ref}, dt_list, Substr(dt_ref << get name, 1, 31));
Show(dt_names);

Create Excel Workbook("$TEMP/MyWorkbook.xlsx", dt_list, dt_names);
Close(dt1, no save);
Close(dt2, no save);

[#3]

Many thanks jthi. However, I have JMP 15 and looks like the script cannot run the Transform command. Would you know of an alternative script please?

[#4]

To add to my question, say I have about 100 individual jmp files to export at once. I would not be able to define the names of each and every file seperately on the script. So what I want the script to do is export whatever the jmp files that are open at that moment including the ones with a very long name.

[#5]

TransformEach can be replaced with a for loop, something like this

// dt_names = Transform Each({dt_ref}, dt_list, Substr(dt_ref << get name, 1, 31));
// for every dt_ref in dt_list, make a new list using substr...
dt_names = {};
for( i = 1, i <=nitems(dt_list), i += 1,
    dt_ref = dt_list[i];
    insert into( dt_names, substr( dt_ref << get name, 1, 31 ) );
);

[#6]

Hi DD1,

First, thank you tdhi for the script.  If you don't mind sharing, please submit the script that crashes to JMP Tech Support at support@jmp.com and we'll try to fix the crash behavior for a future release.

Brian Corcoran

JMP Development

[#7]

Sorry, jthi wrote the script.