Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.soft-sys.math.mathematica > #15848 > unrolled thread

Excel XLS Import slowdown in version 8

Started byChris Chiasson <chris.chiasson@gmail.com>
First post2011-02-04 06:43 +0000
Last post2011-02-08 10:08 +0000
Articles 5 — 4 participants

Back to article view | Back to comp.soft-sys.math.mathematica


Contents

  Excel XLS Import slowdown in version 8 Chris Chiasson <chris.chiasson@gmail.com> - 2011-02-04 06:43 +0000
    Re: Excel XLS Import slowdown in version 8 fonseca <public@fonseca.info> - 2011-02-08 10:08 +0000
    Re: Excel XLS Import slowdown in version 8 Lou <lvveen32@xs4all.nl> - 2011-02-09 07:08 +0000
    Re: Excel XLS Import slowdown in version 8 Albert Retey <awnl@gmx-topmail.de> - 2011-02-04 11:58 +0000
      Re: Excel XLS Import slowdown in version 8 Lou <lvveen32@xs4all.nl> - 2011-02-08 10:08 +0000

#15848 — Excel XLS Import slowdown in version 8

FromChris Chiasson <chris.chiasson@gmail.com>
Date2011-02-04 06:43 +0000
SubjectExcel XLS Import slowdown in version 8
Message-ID<iig75v$rkm$1@smc.vnet.net>
I have noticed in Mathematica version 8 that Excel (.xls, not the new
format) importing is about 3.5 times slower than in version 7. I
noticed this because I have a script that reads two sheets from many
Excel files that used to take 1.5 seconds per file that now takes
around 5.18 seconds per file (AbsoluteTiming). Nothing else is done in
that part of the script; it is just a map of an Import command over a
list of Excel files. Is anyone else experiencing this slowdown? What
is the cause? Are there any remedies?

[toc] | [next] | [standalone]


#15889

Fromfonseca <public@fonseca.info>
Date2011-02-08 10:08 +0000
Message-ID<iir4nf$6q2$1@smc.vnet.net>
In reply to#15848
Hi,

I experienced a problem with Mathematica 8 when importing very large
Excel files (above 40 mb).

These same files used to work fine with Mathematica 7, but with
Mathematica 8, after around 10 seconds, I got the message:
Import::fmterr: Cannot import data as XLS format. 

Pedro

[toc] | [prev] | [next] | [standalone]


#15924

FromLou <lvveen32@xs4all.nl>
Date2011-02-09 07:08 +0000
Message-ID<iitei5$jgr$1@smc.vnet.net>
In reply to#15848
On 4 feb, 12:58, Albert Retey <a...@gmx-topmail.de> wrote:
> Hi,
>
> > I have noticed in Mathematica version 8 that Excel (.xls, not the new
> > format) importing is about 3.5 times slower than in version 7. I
> > noticed this because I have a script that reads two sheets from many
> > Excel files that used to take 1.5 seconds per file that now takes
> > around 5.18 seconds per file (AbsoluteTiming). Nothing else is done in
> > that part of the script; it is just a map of an Import command over a
> > list of Excel files. Is anyone else experiencing this slowdown? What
> > is the cause? Are there any remedies?
>
> We have also noticed that the Import of xls-files can be much slower in
> Version 8 than it was in Version 7. Especially if there are plots within
> those files, the import in 8 can be horrible slow, in some cases Import
> becomes completely useless. It looks like it has to do something with
> the plots, but so far I have not found a way to avoid that, e.g. by just
> importing the "Data" element. What has helped in most cases is to delete
> all plots in the xls-files before importing, but of course that is just
> a workaround and might not be a solution if you have many files, as
> seems the case for you...
>
> I would be very interested to hear if anyone has found ways to improve
> the import speed of xls-files in version 8 or switch back to the version
> 7 behaviour...
>
> hth,
>
> albert

Hi,
My last obeservations on this subject. It seems that updating the
variables and retrying different settings will not result in a
repeatable problem.
So the code already posted suddenly worked fine (but still slow).
But still it seems that java plays an important role and one test is
repeatable.
Using Mathematica 8 with a cold start with following settings results in an
error and reading the file before the error is generated really takes
almost 8 minutes:
writexls = Table[Range[Random[], 255], {x, 1, 10000}];


Export["xl.XLS", writexls]

Export::fmterr: Invalid XLS format. >>

$Failed

readxl = Import["xl.XLS"];

Import::fmterr: Cannot import data as XLS format. >>

Again coldstart Mathematica 8 and with Java parameter settings (David Bailey
suggestion on the web)
This works.
In[1]:= Needs["JLink`"]

ReinstallJava[CommandLine -> "javaw.exe -XX:NewSize=800m"];


In[3]:= writexls = Table[Range[Random[], 255], {x, 1, 10000}];


In[4]:= Export["xl.XLS", writexls]

Out[4]= "xl.XLS"

In[5]:= readxl = Import["xl.XLS"];

I don't understand the error message in combination with the 'solution/
workaround' but it works.
Kind regards,
Lou
Laurent

[toc] | [prev] | [next] | [standalone]


#15955

FromAlbert Retey <awnl@gmx-topmail.de>
Date2011-02-04 11:58 +0000
Message-ID<iigpkd$212$1@smc.vnet.net>
In reply to#15848
Hi,
> I have noticed in Mathematica version 8 that Excel (.xls, not the new
> format) importing is about 3.5 times slower than in version 7. I
> noticed this because I have a script that reads two sheets from many
> Excel files that used to take 1.5 seconds per file that now takes
> around 5.18 seconds per file (AbsoluteTiming). Nothing else is done in
> that part of the script; it is just a map of an Import command over a
> list of Excel files. Is anyone else experiencing this slowdown? What
> is the cause? Are there any remedies?

We have also noticed that the Import of xls-files can be much slower in
Version 8 than it was in Version 7. Especially if there are plots within
those files, the import in 8 can be horrible slow, in some cases Import
becomes completely useless. It looks like it has to do something with
the plots, but so far I have not found a way to avoid that, e.g. by just
importing the "Data" element. What has helped in most cases is to delete
all plots in the xls-files before importing, but of course that is just
a workaround and might not be a solution if you have many files, as
seems the case for you...

I would be very interested to hear if anyone has found ways to improve
the import speed of xls-files in version 8 or switch back to the version
7 behaviour...

hth,

albert

[toc] | [prev] | [next] | [standalone]


#15973

FromLou <lvveen32@xs4all.nl>
Date2011-02-08 10:08 +0000
Message-ID<iir4n3$6pq$1@smc.vnet.net>
In reply to#15955
On 4 feb, 12:58, Albert Retey <a...@gmx-topmail.de> wrote:
> Hi,
>
> > I have noticed in Mathematica version 8 that Excel (.xls, not the new
> > format) importing is about 3.5 times slower than in version 7. I
> > noticed this because I have a script that reads two sheets from many
> > Excel files that used to take 1.5 seconds per file that now takes
> > around 5.18 seconds per file (AbsoluteTiming). Nothing else is done in
> > that part of the script; it is just a map of an Import command over a
> > list of Excel files. Is anyone else experiencing this slowdown? What
> > is the cause? Are there any remedies?
>
> We have also noticed that the Import of xls-files can be much slower in
> Version 8 than it was in Version 7. Especially if there are plots within
> those files, the import in 8 can be horrible slow, in some cases Import
> becomes completely useless. It looks like it has to do something with
> the plots, but so far I have not found a way to avoid that, e.g. by just
> importing the "Data" element. What has helped in most cases is to delete
> all plots in the xls-files before importing, but of course that is just
> a workaround and might not be a solution if you have many files, as
> seems the case for you...
>
> I would be very interested to hear if anyone has found ways to improve
> the import speed of xls-files in version 8 or switch back to the version
> 7 behaviour...
>
> hth,
>
> albert

Hi my previous post was a bit odd because I noticed I generated lots
of sheets instead of all data in one sheet as I intended.
I was put off by this due to the fact that with a large number which I
started with Mathematica 8 claimed wrong file format.
So I changed to extra {} and the message disappaered but I didn't
check in the file itself.
So this test is/was  not representative although still interesting.
So creating one sheet with lots of data also has some limits which
seem strange.
In[60]:= writexls = Table[Range[Random[], 255], {x, 1, 5980}];


In[61]:= Export["xl.XLS", writexls]

Out[61]= "xl.XLS"

In[62]:= readxl = Import["xl.XLS"];

will work within 1 minute

changing 5980 to 5981 results in an error:

In[63]:= writexls = Table[Range[Random[], 255], {x, 1, 5981}];


In[64]:= Export["xl.XLS", writexls]

During evaluation of In[64]:= Export::fmterr: Invalid XLS format. >>

Out[64]= $Failed

So now I open XLS and add one row to have the 5981 rows.
Reading the file works.. No error there.

Anyway Mathematica 8 is slower with XLS then Mathematica 7 and the behaviour is different.
The above worked with Mathematica 7
Kind regards,
Lou

[toc] | [prev] | [standalone]


Back to top | Article view | comp.soft-sys.math.mathematica


csiph-web