This tag line reserved for humor.  Once we find some.

### Philo, back to the drawing board for you

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

When is the next one due?
Tapiwa
July 4th, 2007 6:33am
What, you hit the millionth row?
Flasher T
July 4th, 2007 6:36am
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?
LeftWingPharisee
July 4th, 2007 6:40am
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

Tapiwa
July 4th, 2007 6:46am
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
Tapiwa
July 4th, 2007 6:48am
hey tap, where's them pics of the swedish mama's ...? you want to bust my balls, you need proof ;-)
\$--
July 4th, 2007 7:19am
Tapiwa
July 4th, 2007 7:51am
yeah ... i'll look for you ...
\$--
July 4th, 2007 8:04am
\$--
July 4th, 2007 8:09am
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.
Philo
July 4th, 2007 8:30am
>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.
Tapiwa
July 4th, 2007 8:54am
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
Tapiwa
July 4th, 2007 8:58am
"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.
LeftWingPharisee
July 4th, 2007 12:55pm
"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)
Philo
July 4th, 2007 1:23pm
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.
bon vivant
July 4th, 2007 2:52pm
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
July 4th, 2007 3:40pm
Tapiwa, why don't you submit the story to the WTF?  They're running short on good ones lately.
AMerrickanGirl
July 4th, 2007 5:54pm
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")
xampl
July 4th, 2007 8:32pm
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)
xampl
July 4th, 2007 8:34pm
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.
LeftWingPharisee
July 4th, 2007 11:00pm
"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.
Philo
July 5th, 2007 12:15am
Does Excel need Query still, or does it have ODBC capabilities like Access?
LeftWingPharisee
July 5th, 2007 6:40am