Jump to content
Sign in to follow this  
boogieoompa

Massive Data Analysis

Recommended Posts

boogieoompa

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

Share this post


Link to post
Share on other sites
kylomas

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

Share this post


Link to post
Share on other sites
water

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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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
boogieoompa

WOW, thanks that is a ton faster!

Thank you both!

Share this post


Link to post
Share on other sites
water

Glad to be of service :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

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
Sign in to follow this  

×