Looks like I have already hit the row limit on Excel 2007 :-(

When is the next one due?

What, you hit the millionth row?

There's a row limit in Excel 2007? Shit, I've been talking it up as the ultimate BI tool.

Is there a column limit?

yup, had a document with 2.1 million rows

I too thought there was no limits, but the limit is 2^20 =  1,048,576

Had to google for it, bcause I had heard someone say there was no row limit.

There is a column limit too.

Gone up from 256 (2^8) to 16k (2^14)

This post explains all the limits nicely

http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx

hey tap, where's them pics of the swedish mama's ...? you want to bust my balls, you need proof ;-)

$-- you on facebook?

yeah ... i'll look for you ...

aaargh ... stupid facebook is putting an ad on top of the captcha so I can't add you as a friend. maybe you can add me ??

If you have 2.1M rows, you shouldn't be working in Excel. I'd be interested to talk to you offline about why you can't use a proper OLAP system to do what you need.

>If you have 2.1M rows, you shouldn't be working in Excel
It was the result of a huge fuckup by someone at work.

One of my biggest problems though, is I am regularly playing with 100k row datasets.

Trying to get $$ for analytical software approved at work is harder than extracting blood from a rock. My solution is to pass it through Access, and then split into  two or 3 different sheets because of the 65k limit in 2003.

As it stands, I 'borrowed' the machine used by one of the applications specialists to road test 2007. It was just interesting that there was a row limit, despite most of the 'popular' press saying there wasn't.

I suppose 99.9999% of Excel users will never run into this limit. Not knowing that it is there is a bad thing though.

Oh, btw, the original document was an 800Mb .csv with (calculated) over 2 or 3 million rows.

This is because the normal file is 21Mb or so, with only about 80k rows.

How I ended up receiving an 800Mb csv is a story for the Daily WTF

"If you have 2.1M rows, you shouldn't be working in Excel. I'd be interested to talk to you offline about why you can't use a proper OLAP system to do what you need."

Excel is the ultimate BI tool. Just create a cube using Oracle, SQL Server or the RDB of your choice, output it to Excel using the column headers. The grave character (`) is a much better delimiter than commas, btw.

Once in Excel, use the auto filter, you've got a drill down report.

This gives me a warm and fuzzy feeling because a) Excel's already in house, no requisition required and b) the end users already know how to use Excel. They want to make charts, doo-dads, stop lights, whatever, they don't bother me. G'zinte heit.

Of course, I can't put it on my resume. That sucks.

The only thing holding back Excel was the row/column limits. If you really have > 2.1 million rows, before you buy a new tool, figure out does anybody really need that.

"Excel is the ultimate BI tool. Just create a cube using Oracle, SQL Server or the RDB of your choice"

I think you're not understanding "cube" here. (Mostly because you don't create a cube with an RDBMS)

Usually I'd just write a dozen lines of Perl to scan the 800 Mb csv file and extract the information I'm looking for. Excel is nice for many purposes but sometimes there's no substitute for programming.

Thing was, I kinda knew that the file was a WTF ... when you are expecting 20Mb, and you get 800, you know it is.

Just had to prove it to the local bigwigs in a way they understood.

Tapiwa, why don't you submit the story to the WTF?  They're running short on good ones lately.

Or why not write an Excel add-in that will connect back to the original database, and they can use the custom function you write to bring back relevant data.

=@GoGetSalesData(2007, 03, "California", "MegaWidget2000", "SteveTheSalesGuy")

Once you have the data, you can then chart & graph it in Excel to your heart's content.

(Wrote something that did this at a previous job -- they've now based the company around the technology, as simple as it was)

In Oracle (don't know about S.S, but thought it so), do this:

SELECT ....
FROM ....
GROUP BY CUBE (....)

You get a cube. Lots and lots of rows. Use SQL+ to output it to a delimited file. Done.

"Or why not write an Excel add-in that will connect back to the original database, and they can use the custom function you write to bring back relevant data."

Excel can connect to databases natively.

Does Excel need Query still, or does it have ODBC capabilities like Access?