topic Re: Scripting: save multiple data table to multiple sheets in Excel in Discussions

Scripting: save multiple data table to multiple sheets in Excel

GroupSquareWolf — Fri, 09 Jun 2023 16:01:20 GMT

I like to save multiple datatables, each to an individual sheet, in a single Excel file?

What is the script to achieved that?

I tried "append", but it did not work

dt<< Save as("$DESKTOP/Untitled73.xlsx", append);

Thanks!

Re: Scripting: save multiple data table to multiple sheets in Excel

jthi — Sat, 29 Oct 2022 15:35:31 GMT

Create Excel Workbooks

Re: Scripting: save multiple data table to multiple sheets in Excel

txnelson — Sat, 29 Oct 2022 15:48:50 GMT

The answer to your question comes from the Scripting Indes

Here is one of the examples from that entry

Names Default To Here( 1 );
Open( "$SAMPLE_DATA/Big Class.jmp" );
Open( "$SAMPLE_DATA/Abrasion.jmp" );
Create Excel Workbook(
	"$TEMP/MyWorkbook.xlsx",
	{"Big Class", "Abrasion"},
	{"Big", "Abrasive"}
);

Re: Scripting: save multiple data table to multiple sheets in Excel

GroupSquareWolf — Mon, 31 Oct 2022 04:44:31 GMT

Thank you for pointing out the Srcipting index, it is really helpful.

A further question: does the "list of the worksheet names" have to be strings? Can they be string variables?

Here is an example I am struggling with:

The script below creates three datatables based on :Column 2 values, then save three separate sheets in one Excel files. I would like to name each worksheet based on :Column 2 values, but when I put GroupBy variable as the worksheet names (the red script below), it does not assign :Column 2 value to each worksheet.

dt = current datatable();

Summarize (dt, GroupBy = By (:Column 2) );

For ( i=1, i<= N items (GroupBy), i++,

dt<< select where (:Column 2 == GroupBy [i]);

dt2= dt << subset (

	Selected Rows( 1 ),
	Selected columns only( 0 )
);

show (dt2);
show (GroupBy[i]);
Create Excel Workbook ("$DESKTOP/Untitled1.xlsx", {dt2}, GroupBy[i]);
);

Thank you for your help!

Re: Scripting: save multiple data table to multiple sheets in Excel

txnelson — Mon, 31 Oct 2022 05:35:20 GMT

This issue you are having is with 2 of the elements that you are attempting to pass to the Create Excel Workbook

The third element in Create Excell Workbook function, need to be a list. In your case, the specification is not a list.

Element 2 documentation indicates that what is required is "List of open tables". While your specification of

While the returned element from the Summarize() function, GroupBy, is a list, GroupBy[1] is a string variable. You need to do something like

tempList = {};
insert into( tempList, GroupBy[1]);
Create Excel Workbook ("$DESKTOP/Untitled1.xlsx", {dt2}, tempList);