topic Re: jmp tabulate to excel in Discussions

jmp tabulate to excel

joann — Wed, 03 Oct 2018 17:38:15 GMT

Is there an add-in that can export tabulate to excel?

I only found add-in that can export data table to excel.

Thanks in advance!

Re: jmp tabulate to excel

briancorcoran — Wed, 03 Oct 2018 18:27:17 GMT

On the Tabulate red triangle menu you can select "Make Into Data Table". Then, in the resulting table you can do File->Save As and select Save As Type "Excel Workbook". You didn't say what version you are running, or under what operating system, but this should be similar if you are using a Mac. You would just need to use Export instead of File Save As.

Brian Corcoran

JMP Development

Re: jmp tabulate to excel

joann — Wed, 03 Oct 2018 18:36:42 GMT

Thank you Brian. Though if we do data table first, then we'll lost the tabulate format. Is there a way to export the tabulate as is?

Thanks,
Joann

Re: jmp tabulate to excel

briancorcoran — Wed, 03 Oct 2018 18:41:33 GMT

I'm afraid there is not.
Brian

Re: jmp tabulate to excel

txnelson — Wed, 03 Oct 2018 19:17:55 GMT

You can save the Tabulate output to a text file, which you can then bring into Excel. Here is a simple illustration of saving the output to text

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
tab = tb = Tabulate(
	Show Control Panel( 0 ),
	Add Table( Row Table( Grouping Columns( :age, :name ) ) ),
	Display Column Width( Row Label( "name" ), 70 )
);
Report( tb )[tabulatebox( 1 )] << save text( "$TEMP/tab.txt" );

Re: jmp tabulate to excel

joann — Wed, 03 Oct 2018 19:35:42 GMT

Thank you Jim.
This might be a stupid question, but how do I open the txt correctly in excel with the original tabulate format? I opened it with excel but it appeared in only the first row, no the right format.

Thanks,
Joann

Re: jmp tabulate to excel

txnelson — Wed, 03 Oct 2018 19:51:56 GMT

I opened Excel, then went to File==>Open, and browsed to the text file. Selected it and when the file was attempting to open, it brought up the Text Impor Wizard.

and it then opened the file

Re: jmp tabulate to excel

Peter_Bartell — Wed, 03 Oct 2018 19:57:43 GMT

Can I ask why you want to export a Tabulate result in JMP to Excel? Reason I'm asking if it's just so a non JMP user can 'see' the tabulate report there are variety of means to accomplish this. Copy/paste to the destination Microsoft application can work. You can also get more elegant and publish a Web Report...so lots of ways to share your JMP discoveries with others...if that's the primary reason for getting JMP created content into other applications or file structures.

Re: jmp tabulate to excel

joann — Wed, 03 Oct 2018 20:03:08 GMT

Hi Jim,

I opened the excel wizard, but mine looks different from yours as attached. And I don't have the 437 origin like you did. However I managed to paste it to Notes first and then paste it to excel and it worked. Thank you very much!

Joann

Re: jmp tabulate to excel

joann — Wed, 03 Oct 2018 20:06:12 GMT

Right. My work requires me to provide analysis in excel by tabs as a routine. So I'm trying to find a way to automate it.

Re: jmp tabulate to excel

Peter_Bartell — Wed, 03 Oct 2018 20:21:10 GMT

It's still not 100% clear what your end goal is with respect to the JMP output. When you say you need to produce the report as tabs in Excel, I'm interpreting that as you need to show the JMP Tabulate report as content in a seperate and distinct Excel tab. If my interpretation is correct, one way you could do this besides those I've mentioned above is to save the JMP Tabulate report as an Interactive or static HTML object. Then you can embed each object within a tab in Excel...then all someone needs is a web browser to open each object and what the Excel user will 'see' is the Tabulate report output.

Re: jmp tabulate to excel

uday_guntupalli — Wed, 03 Oct 2018 20:54:47 GMT

@joann,
If all you wanted is the result of JMP tabulation in Excel repeatedly, would something like this work ?

dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );
dt1 = (dt << Tabulate(
	Add Table(
		Column Table( Grouping Columns( :sex, :marital status ) ),
		Row Table( Grouping Columns( :country, :size ) )
	)
)) << Make Into Data Table;
dt1 << Set Name("TabulateResults");

Create Excel Workbook( "c:\MyWorkbook.xlsx", {"TabulateResults"} );

Re: jmp tabulate to excel

joann — Wed, 03 Oct 2018 20:56:01 GMT

Right the HTML looks good, but sometimes my colleagues will like to make some more metric out of it, meaning my output usually might not be the last stop.
You mentioned copy/paste to excel, however once I do that it will end up all in one column, not the right format. Though if I pasted it to Notes first, and then pasted it to excel, I do get the correct format, just without the frames.

Thanks,
Joann

Re: jmp tabulate to excel

joann — Wed, 03 Oct 2018 21:00:46 GMT

@uday_guntupalli

Thank you for the reply.

However with the script you provide the output looks like the below, first screenshot; while I'm looking to output the table in the second screenshot.

Thanks,

Joann

Re: jmp tabulate to excel

txnelson — Wed, 03 Oct 2018 21:40:08 GMT

That would require the generation of a full Excel formatted file, since it includes spanning columns, etc.

Re: jmp tabulate to excel

joann — Thu, 04 Oct 2018 15:51:38 GMT

Thank you Jim! So this is not possible in JMP?

Re: jmp tabulate to excel

gzmorgan0 — Fri, 05 Oct 2018 07:16:22 GMT

Since you did not specify, which summary statistics that you are using or your JMP version, this might not work for you. If your JMP includes the Categorical platform, and you can create your tabulate table using Categorical, there is an option to Save Excel File(). An example script, the JMP output and the Excel file are displayed below. The shading is not saved, and if labels are long they are rotated, but the structure remains.

This is just an FYI for a possible alternative. I have not used Categorical much, I know N, Means, Std Dev, % Response are available. However, I have not investigated quantiles and other statistics.

Names default to here(1);
dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );
obj = dt << Categorical(
	Structured( :sex * :marital status, :country * :size ),
	Share Of Responses( 0 ),
	Share Chart( 0 ),
	Legend( 0 ),
	Total Responses( 0 )
);
obj << Save Excel File(
	"c:/temp/jmp_to_excel_categorical.xlsx",
	Separate Rows for Each Cell Statistic( 0 )
);

JMP Categorical ReportSaved Excel File

Re: jmp tabulate to excel

gzmorgan0 — Sat, 06 Oct 2018 10:33:37 GMT

This is an extension of Peter Bartell's suggestion: save the journal as HTML. This script adds the JSL that uses Run Program() with wscript (that runs vbscript) to import the saved HTML file and do some formatting. It is a modification of example #3 in the script 9_RunProgram.jsl from JSL Companion, Applications of the JMP Scripting Language, 2nd edition.

The script is attached.

Names default to here(1);

dt = Open( "$SAMPLE_DATA/Car Poll.jmp" );
obj = dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table( Grouping Columns( :sex, :marital status ) ),
		Row Table( Grouping Columns( :country, :size ) )
	)
);

dt1 = obj << Make into Data Table;
nc = ncol(dt1);
Close(dt1,NoSave);


//===== the code below will import the saved html into Excel =====

//assume < 26*26 columns
_alph = Words("ABCDEFGHIJKLMNOPQRSTUVWXYZ","");
_lc = if(nc<27 , _alph[nc],
       _alph[floor((nc-1)/26)] || if(mod(nc,26)==0,"Z", _alph[mod(nc,26)])
      ); 
new window("Tabulate", <<Journal);
obj << Journal();

_htmfid = "C:\TEMP\car_tabulate.htm";
_xlsfid = "C:\TEMP\car_tabulate.xlsx";

current journal() << Save HTML(_htmfid);

cmdList={ 
"On Error Goto 0  'stop the error handler",
"Dim xlApp",
"\[Set xlApp = CreateObject("Excel.Application")]\",
"xlApp.Visible=True",
"\[xlApp.Workbooks.Open ("^_htmfid^")]\",
"xlApp.Rows.WrapText = False",
"xlApp.Wait Now + #0:00:03#",
"\[xlApp.Columns("A:^_lc^").Select]\",
"xlApp.Selection.Columns.Autofit",
"xlApp.Cells(1, 1).Select",
"\[xlApp.ActiveWorkbook.SaveAs ("^_xlsfid^"), 51]\",
"xlApp.Quit"
};

vbsTxt = concat items(cmdList, "\!N"); //add new lines aka CR 
vbsTxt = EvalInsert(vbsTxt); //insert the values for _lc, _htmfid, _xlsfid
Save Text File( "c:\temp\jj1.vbs" , vbsTxt);

rp3 = RunProgram(Executable("wscript"),
	   Options("c:\temp\jj1.vbs"),
	   ReadFunction("text")
);

Here is a screen shot of the Excel file. It is a standard Excel file with merged cells for row and column headers and values in cells.