Thoughts on Working with 1 Million Rows?

[drew138]

I'm working on a massive download consisting of 1.2M rows of data. The file is over 110 megabytes. I'm using XP with 4gig of Ram on a dual core 2.3ghz processor. XP only recognized 3.5gigs of ram.

I only need to perform basic manipulations on this file, but it's crashing my system. I can open it. But other than opening it and simple searching, almost any calculation or sort kills it.

I'm considering to upgrade to Win7 and throw 8gigs of ram at it, but I'm not sure this will even work. Does anyone have any thoughts on how to best work with this file? Would using MS Access have better results. I'm not really constrained by budget as this is fairly critical and need to work with this file in its current format?

Any help appreciated!

[Ron Coderre]

Can you describe the "basic manipulations" you do with the data?

[DonkeyOte]

Regardless of which version you're running you can't store 1.2m rows on any given sheet in XL (2003 restricted to 65536 rows, 2007 just over 1m)

Gut instinct... XL is not fit for purpose, certainly not in terms of data storage and yes you should be looking to use a Database.

If you have MS Access then use it if in no other than capacity than as the data warehouse. If needed you can still use XL for analysis using MS Query or ADO to extract data from the db etc... you will with some pretty basic SQL find you can aggregate (ie condense) you data and perhaps circumvent row restrictions ?

You might still want to install XL2007 on your client - though plenty of people would argue that 65536 should be plenty for any XL analysis (ie if you need more you shouldn't be using XL in the first instance)

[browni]

If needed you can still use XL for analysis using MS Query or ADO to extract data from the db etc... you will with some pretty basic SQL find you can aggregate (ie condense) you data and perhaps circumvent row restrictions ?

On an XP machine with Excel 2003, MS Query and an ODBC text driver I've been forced (by management) to analyse tab delimited text files with 20 million rows (approx 5 mins to conduct a simple query), presenting results in pivottables, querytables and ADO extracts (breaking recordsets up into 65k chunks where necessary). Not pretty but sometimes needs must!