898175_en-US

liliconcom

JMP 18.2.2 Excel Import Extremely Slow

I have been using JMP scripts for a number of years to import large batches of excel files in a loop. Ever since the update to JMP 18 this now takes 4 or 5 times longer than it used to. Where opening 100 files may have taken around 5 minutes previously it now takes upwards of 20 minutes. None of my scripts have changed.

 

This is the typical syntax used:

// Open up Sum_LP sheet and copy contents to JMP table called summary
dt = Open(
	::fullfile,
	Worksheets(Sheetname),
	Use for all sheets(0),
	Concatenate Worksheets(0),
	Create Concatenation Column(0),
	Worksheet Settings(
		1,
		Has Column Headers(0),
		Number of Rows in Headers(0),
		Headers Start on Row(0),
		Data Starts on Row(5),
		Data Starts on Column(1),
		Data Ends on Row(54),
		Data Ends on Column(120),
		Replicated Spanned Rows(1),
		Replicated Spanned Headers(0),
		Suppress Hidden Rows(1),
		Suppress Hidden Columns(1),
		Suppress Empty Columns(0),
		Treat as Hierarchy(0),
		Multiple Series Stack(0),
		Import Cell Colors(0),
		Limit Column Detect(0),
		Column Separator String("-")
	),
	Invisible
);

Edit (jthi): added jsl formatting

 

The efficiency of this is critical since it is used multiple times a day. Can anyone help me understand why it is now so slow?

Automation and ScriptingRe: JMP 18.2.2 Excel Import Extremely Slow

This could have something to do with it  JMP Note 655981 - Changes to the import of Microsoft Excel files in JMP® 18  . Also, do you have any merged cells in the excel? They at least used to cause some slowdowns with the excel import?

 

@briancorcoran 

Re: JMP 18.2.2 Excel Import Extremely Slow

Hi jthi,

 

Thanks for the link to the note, I had not seen that.

The excel sheets are really as simple as you can get - there's some cells with a fill colour but there's no merged cells. I may play around with the formatting of the sheets and see what impact it has.

Re: JMP 18.2.2 Excel Import Extremely Slow

No - this is something fundamentally worse about how JMP 18 handles excel sheets because even using the most simplistic excel sheet possible the time is significantly increased.

Re: JMP 18.2.2 Excel Import Extremely Slow

Might MFI, Multiple File Import be used to increase the speed?

Re: JMP 18.2.2 Excel Import Extremely Slow

I deeply feel that the data splicing speed of JMP 18 is far inferior to that of JMP 14

Re: JMP 18.2.2 Excel Import Extremely Slow

Hello,

 

As Jarmo notes, there was an issue with merge cells, but that was fixed in JMP 18.2.  Timings that we run internally at JMP generally show the import as being faster than in JMP 17, although it consumes more memory. If you have a memory constrained machine (e.g. < 16 GB) that could be an issue.  Otherwise, it is likely something specific to your worksheet.  We cannot accept real data, but if you can come up with anonymized worksheet or fake data that replicates the problem, we would appreciate your submitting it to JMP Tech Support (support@jmp.com) for us to examine.

 

Thank you,

 

Brian Corcoran

JMP Development

Re: JMP 18.2.2 Excel Import Extremely Slow

The machines typically running the scripts are 8GB RAM so presumably that is the root cause. However, this was not a problem in JMP 17 and below.

 

I don't believe the recommend RAM has changed from the 4GB stated for JMP 17?

Re: JMP 18.2.2 Excel Import Extremely Slow

I just tried running the scripts on a 32GB of RAM development machine and it was still much slower compared to JMP 17.

I will contact the email address supplied with some fake data.

Re: JMP 18.2.2 Excel Import Extremely Slow

That is correct, JMP will run in 4 GB of RAM.  However, more is always better than JMP since it does everything in memory.  Regarding the Excel import specifically, the new underlying library that is being used for JMP 18 is more memory intensive. Yes it will run in 4 GB, but for a large quantity of files or larger files I would certainly recommend more.  

 

Brian