The best elegant solution to override 65k rows limit in a sheet

[taz]

I'd like your opinion/solution to break the limit of 65k lines in a XLS
sheet.

I.E. let's say we can have 4 millions records to treat and have excel
read virtually every record from this db. Is there an elegant and
transparent solution for the users of my company?

 

[moi]

Split the records into multiple files, write an app in C++ that does the
dirty work considering the .xls-file folder as one huge file.
And if one .xls-file is full, just let the app create a new one.
U can also write a module in VBA (Access maybe) but that's not so fast.

 

[Bob Phillips]

Most elegant? Use a database.

 

[Bill Sharpe]

Even Quattro Pro won't help you here. It allows 1,000,000 rows in one
worksheet.

Bob Phillips is right. Use a database. If the company has 4 million
records it should be able to afford database programs for its users.

I expect Aaron to pop in with his usual rant, too.

Bill


I'd like your opinion/solution to break the limit of 65k lines in a XLS
sheet.

I.E. let's say we can have 4 millions records to treat and have excel
read virtually every record from this db. Is there an elegant and
transparent solution for the users of my company?

 

[taz]

Let's assume that we must use EXCEL as a reporting engine / designer
that pull the data from a dbms (like SQL & Oracle) but has to eat big
chunks of data at a time (more than 65k rows obviously).

I insist on excel because many people know that tool and do not depend
on IT department for many aspects

Let's think about excel like a heart that pumps in/out the data after
the right transformations/calculations

 

[Gordon]

I'd like your opinion/solution to break the limit of 65k lines in a XLS
sheet.

I.E. let's say we can have 4 millions records to treat and have excel
read virtually every record from this db. Is there an elegant and
transparent solution for the users of my company?

Access.

 

[aaron.kempf]

yep here i am

Excel is for babies
use a database.

i reccommend MDB for newbies; and ADP for poeple with decent SQL skillz

-aaron

 

[Bob Phillips]

You should still be able to do most of any math, aggregations that you need
on the DB and pull back much less data. How many times do you need to look
at a million rows?

 

[Harlan Grove]

(e-mail address removed) wrote...
....

Excel is for babies

Here you're only parading your ignorance.

use a database.

Here you're right. Anything truly needing more than even 5K rows should
be in a database.

 

[Harlan Grove]

moi wrote...

Split the records into multiple files, write an app in C++ that does the
dirty work considering the .xls-file folder as one huge file.
And if one .xls-file is full, just let the app create a new one.
U can also write a module in VBA (Access maybe) but that's not so fast.

....

Well if fast is the key, use Perl.

 

[Harlan Grove]

Bill Sharpe wrote...
....

Bob Phillips is right. Use a database. If the company has 4 million
records it should be able to afford database programs for its users.

Doesn't need more than ODBC connections to a central dbms. For those
things Excel can do that databases can't, Excel can't handle even 65K
data points (e.g., LINEST and LOGEST would choke on worksheet-size data
sets). For everything else, use dbms views and queries to categorize
and aggregate.

I expect Aaron to pop in with his usual rant, too.

In this case Aaron would be dead right. Only a moron would try to
process +4M records in a spreadsheet.

 

[aaron.kempf]

just for the record, ACCESS shouldn't be a product of your IT
department. It is a LOT more fun that Excel.. since you can make a
report and print if weekly; instead of make a report and make it
weekly.

I mean-- it's like the 20th century instead of the 18th century (using
Excel).
(Just for the record, I claim that Analysis Services, MDX, etc is 21st
century technology even though they shipped with SQL 7 in ~98-ish)

Access isn't that difficult.

Learn to write queries-- If you cant do it; then quit your excel job
and go to work at McDonalds. 8 years ago, I worked as a tester; and 30
of us testers were taught how to write Access queries in an hour.

I mean seriously-- with a couple of queries; a couple of macros and a
little bit of time-- there ins't anything in the world that you can't
calculate with Microsoft access.

I know that it's quite easy to learn. Just take baby steps and don't
get frustrated.

If you _REALLLLLLLLLLLLLLLLLLLLLLLLLLY_ want to process more than 64k
rows in Excel; you can actually use the Office Web Componenets
Spreadsheet control; I think that it is about 60% of the functionality
of Excel; but it actually has a limit of like ~250,000 rows.

You'd have to do some programming (VB is fun for the whole family.. i
mean... PERL??? wtf this is excel and access not rocket science)..

 

[Harlan Grove]

(e-mail address removed) wrote...
....

I mean seriously-- with a couple of queries; a couple of macros and a
little bit of time-- there ins't anything in the world that you can't
calculate with Microsoft access.

....

Gosh, with a few formulas, a couple of macros and a bit of time I could
do anything in Excel you could do in Access *WITH* *SMALL* *DATASETS*.
I could even think of a few things that would be much easier in Excel.
(I already have - have you made any improvements to the queries I
proposed for amotization tables in

http://groups-beta.google.com/group/microsoft.public.excel/msg/ee7524741faf6518?dmode=source&hl=en

or http://makeashorterlink.com/?S1402436B ?)

Next, without using Excel directly, calculate the 90th pecentile from
the Beta distribution with alpha = 0.5 and beta = 2.5. [Though some
versions of Excel don't do such a wonderful job with stuff like this.]

 

[aaron.kempf]

harlan

you're friggin crazy.
get off your high horse.. small sums and easy math like that-- it's
easy to do on the database side.

and then you can centralize your logic in one place; and when you need
to change your calculation; you can do it all in one place.. instead of
wading through 1000 different XLS formulas.. I mean..

GAG ME WITH A SPOON

aren't you tired of having numbers that dont match?
the root of the problem with Excel is that you have different formulas
in each cell-- in Access you have a different formula for each COLUMN
(or field.. whatever nomenclature you choose)

I just know that there is a better way for all you guys to spend your
workdays.

Get this-- you can build a report and then run it each week
or you can build a report and then rebuild it each week.

Access allows you to go to huge datasets without a problem.
it just takes a little bit of work to get to know queries.

BFD, Excel was passe in 1992-- I mean-- it's time to lose the training
wheels kids

-aaron

 

[aaron.kempf]

and just for the record, Access ships with MSDE.. keeping your data in
a real database-- like MSDE means that you can grow up and use MDX when
you're ready. MDX is the 'spreadsheet language of the database world'

SQL Server Books Online (again-- Access ships with freeware SQL Server
engine)
Using WITH to Create Calculated Members mentions percentile

And here you go; these functions (except the ones with a *) are
supported in Analysis Services
Excel Functions
Microsoft® SQL Server™ 2000 Analysis Services supports many
functions in the Microsoft Excel worksheet library, which is
automatically registered if installed on the computer with Analysis
Services. Functions not supported in this release are marked by an
asterisk in this table.

Acos Acosh And *Application
Asc Asin Asinh Atan2
Atanh AveDev Average BetaDist
BetaInv BinomDist Ceiling ChiDist
ChiInv ChiTest Choose Clean
Combin Confidence Correl Cosh
Count CountA *CountBlank *CountIf
Covar *Creator CritBinom *DAverage
Days360 Db Dbcs *DCount
*DCountA Ddb Degrees DevSq
*DGet *DMax *DMin Dollar
*DProduct *DStDev *DStDevP *DSum
*DVar *DVarP Even ExponDist
Fact FDist Find FindB
FInv Fisher FisherInv Fixed
Floor Forecast *Frequency FTest
Fv GammaDist GammaInv GammaLn
GeoMean *Growth HarMean *HLookup
HypGeomDist *Index Intercept Ipmt
Irr IsErr IsError IsLogical
IsNA IsNonText IsNumber Ispmt
IsText Kurt Large *LinEst
Ln Log Log10 *LogEst
LogInv LogNormDist *Lookup Match
Max *MDeterm Median Min
*MInverse MIrr *MMult Mode
NegBinomDist NormDist NormInv NormSDist
NormSInv NPer Npv Odd
Or *Parent Pearson Percentile
PercentRank Permut Pi Pmt
Poisson Power Ppmt Prob
Product Proper Pv Quartile
Radians *Rank Rate Replace
ReplaceB Rept Roman Round
RoundDown RoundUp RSq Search
SearchB Sinh Skew Sln
Slope Small Standardize StDev
StDevP StEyx Substitute *Subtotal
Sum *SumIf SumProduct SumSq
SumX2MY2 SumX2PY2 SumXMY2 Syd
Tanh TDist Text TInv
Transpose *Trend Trim TrimMean
TTest USDollar Var VarP
Vdb *VLookup Weekday Weibull
ZTest

In other words-- you guys are roadkill since I can do all of this
'crazy analytical math' (gag me with a spoon; you guys can't even ADD--
you guys can't JOIN-- you can't PRINT A REPORT-- you have to email a
10mb spreadsheets

I can do all of this on the db server side of the equation-- so I can
do it faster than you can do it on the desktop.. and I can do it
against BILLIONS of records with a sub-second response time. (if you
know what you're doing-- like I do)

-aaron

 

[(PeteCresswell)]

Per taz:

I'd like your opinion/solution to break the limit of 65k lines in a XLS
sheet.

MS Access back end.

 

[Harlan Grove]

(e-mail address removed) wrote...

and just for the record, Access ships with MSDE.. keeping your data in
a real database-- like MSDE means that you can grow up and use MDX when
you're ready. MDX is the 'spreadsheet language of the database world'

Good for those with Office Pro. Not useful for those with other
versions of Office that don't come with Access. And MDX is yet another
product, and one that few if any business users outside IT departments
would have.

SQL Server Books Online (again-- Access ships with freeware SQL Server
engine)
Using WITH to Create Calculated Members mentions percentile

You don't understand. The percentile of a Beta distribution
corresponding to probability p given parameters alpha and beta is given
by

BETAINV(p,alpha,beta)

And here you go; these functions (except the ones with a *) are
supported in Analysis Services
Excel Functions

....

Yet more software the typical users won't have!

Winnowing the chaff,

*Application
*CountBlank
*CountIf
*Creator
*DAverage
*DCount
*DCountA
*DGet
*DMax
*DMin
*DProduct
*DStDev
*DStDevP
*DSum
*DVar
*DVarP
*Frequency
*Growth
*HLookup
*Index
*LinEst
*LogEst
*Lookup
*MDeterm
*MInverse
*MMult
*Parent
*Rank
*SumIf
*Trend
*VLookup

No big surprise the so-called database functions, {|H|V}LOOKUP,
COUNTBLANK, COUNTIF and SUMIF aren't provided since there are already
better ways to achieve their results through queries. Odd that MATCH is
included since it's pointless without INDEX, which isn't included, but
there's probably some form of arbitrary indexing provided. No big deal
about RANK since it could be implemented via queries.

FREQUENCY wouldn't be very much fun to implement in queries, but it
would be doable.

Lack of multiple regression functions would make it rather difficult to
general linear modeling.

Still, not bad. Now if it were free and could be used with other dbms's
than SQLServer . . .

In other words-- you guys are roadkill since I can do all of this
'crazy analytical math' (gag me with a spoon; you guys can't even ADD--
you guys can't JOIN-- you can't PRINT A REPORT-- you have to email a
10mb spreadsheets

And you can't pitch a deal to customers. So we still make the big
bucks, and you're permanently parked in IT-land. Think of it this way:
the people who hire your boss's boss know spreadsheets, not databases.

I can do all of this on the db server side of the equation-- so I can
do it faster than you can do it on the desktop.. and I can do it
against BILLIONS of records with a sub-second response time. (if you
know what you're doing-- like I do)

You're full of it. You'd get quick responses either because your dbms
is spitting back cached results pulled in previously run queries, or
you're benefitting from indexing used to pull the thousands of records
you're actually selecting from the billions of records you claim to be
processing.

And again you're failing to understand that few business users outside
IT departments have access to those billion records as opposed to a few
views (just read-only access) with a few thousand records each that
their IT departments provide (and they were forced, kicking &
screaming, to provide even that).

If a user lacks rights to create even temporary tables on server-side,
just how much can that user really do?

 

[Harlan Grove]

(e-mail address removed) wrote...
....

and then you can centralize your logic in one place; and when you need
to change your calculation; you can do it all in one place.. instead of
wading through 1000 different XLS formulas.. I mean..

You don't get it. Either the formulas and workbook templates don't
change, only the data entered changes (and IMO manual data entry is
slightly easier in Excel than Access, though there's something to be
said for input masks in Access; copy & paste entry from non-XLS,
non-tabular files is much easier in Excel than Access), in which case
don't-reinvent-the-wheel is operative; or the formulas aren't the same,
in which case saved previous business logic isn't useful.

aren't you tired of having numbers that dont match?

And you've never come across situations in which two derived dbms
tables that should be showing the same results differ in some records?

Reconcilliation nightmares don't entirely disappear when one uses
dbms's.

the root of the problem with Excel is that you have different formulas
in each cell-- in Access you have a different formula for each COLUMN
(or field.. whatever nomenclature you choose)

As I mentioned before, there have been lots of software products that
provided multidimensional database-like functionality, starting with
Lotus Improv (actually starting with the spreadsheet VP-Planner, but
its multidimensional database was rather limited). All were going to
kill spreadsheets as we know them. Most are not extinct and the
spreadsheet lives on & on. Rats outlived dinosaurs, and cockroaches
will likely outlive rats. Feel free to consider spreadsheets the
cockroaches of the software world.

There's a perception that the flexibility provided by spreadsheets,
with a single formula per cell, is worth it. Having a rough idea what
it took to tweak individual 'cell' results in Improv (it required
creating another dimension for exceptions, then incorporating the
exception dimension into the formulas), I can understand why such
programs have never sold well for general use.

I just know that there is a better way for all you guys to spend your
workdays.

....

You have no idea how we spend our workdays. You believe we spend our
time in the same report creation cesspool you do. Ain't so.

 

[taz]

Harlan I fully agree with you, and that was the driver that made me ask
the initial question.

Everyone has to remember that the relational paradigm in DMBS is far
older than spreadsheets, and it's adn will remain an IT-Related issue
for most of the cases.

The relational model can't represent reality and we see it every day
through heavy customization of business apps (i mention SAP, Oracle,
Peoplesoft etc...). Customers mostly aren't happy with them, so we have
to switch to another paradigm closer and closer to customers needs.

Now i ask you a question... What if excel would support billions of
records???

 

[aaron.kempf]

you dipshit

ACCESS isn't a real database. it is a desktop database for excel dorks
like you.

grow up and lose the training wheels.

ACCESS ins't an IT function; it is an end-user function.

OHHHH.. IS THE EXCEL DORK SCARED TO LEARN A REAL REPORTING PROGRAM?

If Excel would support Billions of records.. I would say

***** **** Excel in the MOUTH *****

if Excel stored everything in SQL Server-- then it might be worthwhile.
As it is; your XML and flaky import/export routines make it a total
waste of time.

-Aaron