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

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?

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.



"taz" <taz.1rqvuc_1120647907.602@excelforum-nospam.com> schreef in bericht
news:taz.1rqvuc_1120647907.602@excelforum-nospam.com...
>
> 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
> ------------------------------------------------------------------------
> taz's Profile:
> http://www.excelforum.com/member.php...o&userid=24948
> View this thread: http://www.excelforum.com/showthread...hreadid=384823
>

Most elegant? Use a database.

--
HTH

Bob Phillips

"taz" <taz.1rqvuc_1120647907.602@excelforum-nospam.com> wrote in message
news:taz.1rqvuc_1120647907.602@excelforum-nospam.com...
>
> 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
> ------------------------------------------------------------------------
> taz's Profile:
http://www.excelforum.com/member.php...o&userid=24948
> View this thread: http://www.excelforum.com/showthread...hreadid=384823
>

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

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

"taz" <taz.1rqvuc_1120647907.602@excelforum-nospam.com> wrote in message
news:taz.1rqvuc_1120647907.602@excelforum-nospam.com...

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
------------------------------------------------------------------------
taz's Profile:
http://www.excelforum.com/member.php...o&userid=24948
View this thread:
http://www.excelforum.com/showthread...hreadid=384823

"taz" <taz.1rqvuc_1120647907.602@excelforum-nospam.com> wrote in message
news:taz.1rqvuc_1120647907.602@excelforum-nospam.com...
>
> 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.

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

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?

--
HTH

Bob Phillips

"taz" <taz.1rr73i_1120662593.3576@excelforum-nospam.com> wrote in message
news:taz.1rr73i_1120662593.3576@excelforum-nospam.com...
>
> 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
>
>
> --
> taz
> ------------------------------------------------------------------------
> taz's Profile:
http://www.excelforum.com/member.php...o&userid=24948
> View this thread: http://www.excelforum.com/showthread...hreadid=384823
>

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

MS Access back end.
--
PeteCresswell

aaron.kempf@gmail.com 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.

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.

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.

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)..

aaron.kempf@gmail.com 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/...e=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.]

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