mpower

_Excel_RangeRead with $bForceFunc = True (_ArrayTranspose) very slow

9 posts in this topic

#1 ·  Posted (edited)

Hi guys, I have a large excel document with ~4000 lines and ~14 columns. I am using _Excel_RangeRead to get the whole sheet into an array.

However unless I use $bForceFunc = True it doesnt read the sheet (COM Error= 0x80020005 (-2147352571).

With $bForceFunc = True I understand that the function _ArrayTranspose() is called. This function seems to be very slow.

Is there a quicker way to do _ArrayTranspose() ? At the moment it takes approx 35 seconds for _ArrayTranspose().

Oh and I'm on 3.3.12.0.

Edited by mpower

Share this post


Link to post
Share on other sites



The Excel transpose method has two limitations:

  • It has an undocumented limit on the number of cells or rows it can transpose (depends on the Excel version).
  • It doesn't support cells with more than 255 characters.

In any of this cases you need to use $bForceFunc = True.

Which version of Excel do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

I'm running Excel 2010 and I do think that there are cells with more than 255 chars, hence having to use the Excel transpose method.

I'm not fussed about having to use this method, my only concern is that it is very slow (comparatively).

 

The Excel transpose method has two limitations:

  • It has an undocumented limit on the number of cells or rows it can transpose (depends on the Excel version).
  • It doesn't support cells with more than 255 characters.

In any of this cases you need to use $bForceFunc = True.

Which version of Excel do you run?

Edited by mpower

Share this post


Link to post
Share on other sites

Doing a search on _ArrayTranspose() and _Excel_RangeRead I cant seem to find anyone facing the same 'slowness' issue. Any ideas on why it is so slow?

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

I just tried with 4000 rows and 14 columns each holding a 10-digit number.

Transpose method: 105 ms

Transpose function: 36320 ms

When the Excel range has > 4096 rows or columns another method is used and it then processing time drops to 115 ms.

Seems to be a problem with function _ArrayTranspose whith 2D arrays and both dimensions having < 4096 elements.

I think will talk to the devs.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

Thanks water, its definitely a huge performance drop. I hope the devs can look into it soon :)

Also could you elaborate on what you meant by Transpose method?

 

I just tried with 40000 rows and 14 columns each holding a 10-digit number.

Transpose method: 105 ms

Transpose function: 36320 ms

When the Excel range has > 4096 rows or columns another method is used and it then processing time drops to 115 ms.

Seems to be a problem with function _ArrayTranspose whith 2D arrays and both dimensions having < 4096 elements.

I think will talk to the devs.

Edited by mpower

Share this post


Link to post
Share on other sites

Transpose method means that Excel provides the ability to transpose an array by a builtin "function" which you can call usign COM (as the UDF does).

That's quite fast but has its limitations as described in the help file.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

mpower,

I have been looking at the _ArrayTranspose function and I can confirm water's diagnosis - the algorithm used for 2D arrays when an array has both dimensions of less then 4096 is massively slower than the one used when one dimension is greater than 4096. This is one of the few functions in that library I did not rewrite myself so I will approach the author to see why he made that choice of algorithm. Certainly when I adjust the function code to run just the "big and fast" algorithm for small arrays it seems to work just fine. Watch this space. :)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

Thanks Melba23 and water !~ :) you guys are great!

Can't wait to see something :D

 

mpower,

I have been looking at the _ArrayTranspose function and I can confirm water's diagnosis - the algorithm used for 2D arrays when an array has both dimensions of less then 4096 is massively slower than the one used when one dimension is greater than 4096. This is one of the few functions in that library I did not rewrite myself so I will approach the author to see why he made that choice of algorithm. Certainly when I adjust the function code to run just the "big and fast" algorithm for small arrays it seems to work just fine. Watch this space. :)

M23

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now