topic Re: jmp script to export data sheets to Excel with sheet names longer than 31 characters in Discussions

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

DD1 — Sat, 10 Jun 2023 23:46:37 GMT

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

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

jthi — Thu, 31 Mar 2022 13:47:11 GMT

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

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

briancorcoran — Thu, 31 Mar 2022 15:01:41 GMT

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

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

briancorcoran — Thu, 31 Mar 2022 15:02:54 GMT

Sorry, jthi wrote the script.

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

DD1 — Fri, 01 Apr 2022 15:51:33 GMT

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?

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

DD1 — Fri, 01 Apr 2022 16:08:57 GMT

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.

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

Craige_Hales — Sat, 30 Apr 2022 22:25:34 GMT

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