mpower Posted March 4, 2015 Share Posted March 4, 2015 (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 March 4, 2015 by mpower Link to comment Share on other sites More sharing options...
water Posted March 4, 2015 Share Posted March 4, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
mpower Posted March 5, 2015 Author Share Posted March 5, 2015 (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 March 5, 2015 by mpower Link to comment Share on other sites More sharing options...
mpower Posted March 5, 2015 Author Share Posted March 5, 2015 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? Link to comment Share on other sites More sharing options...
water Posted March 5, 2015 Share Posted March 5, 2015 (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 March 5, 2015 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
mpower Posted March 5, 2015 Author Share Posted March 5, 2015 (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 March 5, 2015 by mpower Link to comment Share on other sites More sharing options...
water Posted March 5, 2015 Share Posted March 5, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted March 5, 2015 Moderators Share Posted March 5, 2015 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 Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
mpower Posted March 5, 2015 Author Share Posted March 5, 2015 Thanks Melba23 and water !~ you guys are great! Can't wait to see something 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now