Sanding our assholes with 150 grit.

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?
Permalink Send private email Tapiwa 
July 4th, 2007 6:33am
What, you hit the millionth row?
Permalink Send private email 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?
Permalink 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

Had to google for it, bcause I had heard someone say there was no row limit.
Permalink Send private email 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
Permalink Send private email 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 ;-)
Permalink $-- 
July 4th, 2007 7:19am
$-- you on facebook?
Permalink Send private email Tapiwa 
July 4th, 2007 7:51am
yeah ... i'll look for you ...
Permalink $-- 
July 4th, 2007 8:04am
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 ??
Permalink $-- 
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.
Permalink Send private email 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.
Permalink Send private email 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
Permalink Send private email 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.
Permalink 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)
Permalink Send private email 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.
Permalink Send private email 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.
Permalink Send private email 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.
Permalink 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")
Permalink Send private email 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)
Permalink Send private email 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.
Permalink 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.
Permalink Send private email Philo 
July 5th, 2007 12:15am
Does Excel need Query still, or does it have ODBC capabilities like Access?
Permalink LeftWingPharisee 
July 5th, 2007 6:40am

This topic is archived. No further replies will be accepted.

Other topics: July, 2007 Other topics: July, 2007 Recent topics Recent topics