boogieoompa Posted February 24, 2014 Share Posted February 24, 2014 So I'm writing a script that has to crunch thru 3 excel spreadsheets. One is 98k x 5 the other is 25k x 4 and the last is 90k x 6. Obviously this is tons of data and _excelreadcell is looking like its very, very slow. Does anyone know of a better way to load/analyze these? I'm debating extracting them into a CSV or something and loading them all in memory (I have access to a PC with 16 GB of RAM) but I'm not sure. Does anyone have any experience or tips at chugging thru this beast? Thank you Link to comment Share on other sites More sharing options...
kylomas Posted February 24, 2014 Share Posted February 24, 2014 You might try _excelreadsheettoarray instead. It does not do a cell by cell read. Water also has a new version of the Excel UDF that may have something useful. See his sig. Forum Rules Procedure for posting code "I like pigs. Dogs look up to us. Cats look down on us. Pigs treat us as equals." - Sir Winston Churchill Link to comment Share on other sites More sharing options...
water Posted February 24, 2014 Share Posted February 24, 2014 Yes, please have a look at my rewrite of the Excel UDF. Function _Excel_RangeRead is about 20 to 100 times faster. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
boogieoompa Posted February 24, 2014 Author Share Posted February 24, 2014 WOW, thanks that is a ton faster! Thank you both! Link to comment Share on other sites More sharing options...
water Posted February 24, 2014 Share Posted February 24, 2014 Glad to be of service My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki 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