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

just because it comes from CUSTOMERS doesn't mean it wont fit into a
database.

I have a quite elegant XLS datamart.. it automates importing
information from spreadsheets-- any shape that you choose-- into a
database.

it's not rocket science.

I'm just tired of wading through 100 spreadsheets to find a number.

-aaron

aaron.kempf@gmail.com wrote...
>just because it comes from CUSTOMERS doesn't mean it wont fit into a
>database.

True. But when it comes from customers, it's not in one's own database
to start with. It's a question of where to enter it initially AND how
long to retain it. If it's needed quickly but only for a single quote,
it's pointless to bother entering it into a database.

>I have a quite elegant XLS datamart.. it automates importing
>information from spreadsheets-- any shape that you choose-- into a
>database.

Goody. Does it automate importing from e-mails (HTML and plain text),
PDF files, bitmapped fax images?

Not all outside data is delivered in spreadsheet files.

yeah you're crazy man
so keep it in spreadsheets.. that helps lol

i dont have any clue what you're talking about.. legal restrictions on
shopping cart information? i mean for real

aaron.kempf@gmail.com wrote...
....
>i dont have any clue what you're talking about..

Finally! A wee, tiny ray of light. You may want to generalize that
'don't have a clue' self-assessment.

> . . . legal restrictions on shopping cart information? i mean for real

And if it were shopping cart information, no big deal.

No, I mean nonpublic information that we're not sent until we send our
customers signed nondisclosure agreements. I realize someone in your,
er, position may not understand what this means, and I doubt it'd be
possible to explain it so you could understand.

screw you kid.. go and play with your spreadsheets.

i'm sorry that your job is SOOOOOOOOOOOOOOOOOOO hard that you HAVE TO
USE EXCEL.

Atms dont use Excel. Amazon.com doesnt run on Excel.
Why do you think that your math is so hard?

Excel-- Word-- decentralized documents are a PITA since you have to go
and change things in 100 differet places. It is better, easier and
more productive longterm to store your DATA in a DATABASE and if you're
reporting on DATABASES it is easier to use Crystal Reports or Access to
make reports than Excel

aaron.kempf@gmail.com wrote...
....
>Atms dont use Excel. Amazon.com doesnt run on Excel.
>Why do you think that your math is so hard?

ATMs add and subtract. Not very difficult. Amazon.com's web interface
doesn't run on Excel, but I wonder what their financial reporting
deparment uses to generate the figures in their quarterly and annual
reports?

So I'll conclude from this you have no clue how to respond to my
challenge in

http://groups-beta.google.com/group/...e=source&hl=en

>Excel-- Word-- decentralized documents are a PITA since you have to go
>and change things in 100 differet places. It is better, easier and
>more productive longterm to store your DATA in a DATABASE and if you're
>reporting on DATABASES it is easier to use Crystal Reports or Access to
>make reports than Excel

Excel, Word and decentralization are FLEXIBLE. They work when the
network is fubar. They allow me to work offline and bring work home on
a disk to use my home PC. They allow me to share files with people
outside my company who'd better not have access to my company's
database.

It's clear you have no clue about real world data sharing with people
outside your company.

Now *IF* one needs to generate reports, then I agree Access and other
reporting packages are usually better than Excel. Small, niggling
little points to the contrary would include people who need to generate
reports but who don't have Access or any report generation software but
do have Excel. There's also the occasional report that's nothing more
than a summary of information from hardcopy sources.

Then there are all the other Excel users who aren't getting paid to
produce reports but are getting paid to produce analyses. You wouldn't
understand that. It'd require thinking.

ALL MATH IS A SIMPLE ADD AND SUBTRACT

get off your high horse; your math isn't 'TOO HARD' for a database and
you will be roadkill if you dont expand your horizons

it's time that beancounters are held to the same standard as
developers.. thats' all you dorks do is develop Excel CRAP but you guys
dont push yourselves anywhere near as hard as us IT people

i urge you to learn Crystal or Access.

Excel was passe in 1995.

aaron.kempf@gmail.com wrote...
>ALL MATH IS A SIMPLE ADD AND SUBTRACT

So needless to say you have no clue what EXP, LN and IRR functions do,
just to name 3 that do a bit more than add and subtract.

All math may reduce to arithmetic when calculating cardinal and ordinal
results, but the magic is in the algorithms, data structures and
referencing mechanisms. In databases, there's only one data structure:
tables; and there are only two referencing mechanisms: SELECT queries
with and without joins.

>get off your high horse; your math isn't 'TOO HARD' for a database and
>you will be roadkill if you dont expand your horizons

The calculations take place in the same CPU for Excel and Access, so
they shouldn't differ in 'hardness'.

The problem with databases is the relative inflexibility of tables as
data structures and the awkwardness of queries as the only referencing
mechanism (unless you assume the existence of tons of additional
software to which few if any non-IT business users have access).

We've already seen that you can't come up with anything better for
simple amortization tables than the queries *I* proposed in

http://groups-beta.google.com/group/...e=source&hl=en

which proves nothing more than you're clueless about how to make
amortization tables. The necessary queries are a joke compared to the
simplicity of how to do this in Excel *FROM* *SCRATCH*, to wit,

1. Define R with the periodic interest rate, N as the number of periods
over which loan payments would be made, A as the initial loan amount,
and P as =PMT(R,N,-A). Since you'd also need to specify these in a
database approach, same data entry requirements so far in Excel and
Access.

2. Enter the following column labels in A1:E1.

A1: Month Number
B1: Prior Balance
C1: Interest
D1: Principal
E1: Current Balance

3. Fill in the first record in A2:E2.

A2: 0
B2: 0
C2: 0
D2: 0
E2: =A

4. Fill in the template formulas for the second record in A3:E3.

A3: =A2+1
B3: =E2
C3: =R*B3
D3: =P-C3
E3: =B3-D3

5. Complete the table by selecting A3:E3 and filling down into A4:E362.

Real hard. Oh so much more difficult than 5 obscure SQL queries,
especially since calculating interest portions directly in Excel (prior
balance times periodic rate) is so much more difficult than calculating
the principal portion directly in Access (periodic payment divided by
one plus periodic interest rate raised to N less current month number
plus 1).

You are so clueless.

Then, just speaking for myself, since I'm having to spoon-feed you the
queries needed to perform the tasks *YOU* say are so easy to do in
databases but *YOU* have proven to be incapable of providing, looks
like I have nothing to fear from you if every company I could work for
were to ban Excel tomorrow and mandate Access use only. The difference
between us is that I know how to use the tools you claim are the only
ones anyone needs to use while you can't figure out the other tools I
use.

Who's roadkill?

Well, neither of us. I'll have a job because I'm so clever, and you'll
have a job because database grunts, like septic tank cleaners, will
always be in some demand.

and databases have a LOT more than select, for starters there is
UPDATE, DELETE, INSERT..

the WITH clause (of MDX and CTEs)

I can figure out enough about Excel to say that I'm tired of keeping my
important numbers in 300 different places.

I keep my calculations in a database; and then i can spit them out into
Excel, Crystal, ASP.. i can display numbers in 100 different formats.
10,000 different formats for christ sakes.

And you idiots are still emailign around 100mb XLS files.. I mean..
WAKE UP AND SMELL THE COFFEE; EXCEL WAS PASSE IN 1995.

database grunts.. ****

i just think that it's funny that there isn't certification for Excel
dorks.. i mean.. its obvious that you all collectively aren't smart
enough to have a little bit of standardization.

i think that there should be 10 different flavors of excel
certification; because everywhere I've gone you excel dorks dont know
what the hell you're doing

aaron.kempf@gmail.com wrote...
....
>unless you really think that mortgage companies use EXCEL to calculate
>this stuff.. I'd STFU

Mortgage companies probably use decades-old COBOL programs for this,
accessing data via ISM-VSAM. That's for the back office stuff. For the
point-of-sale tables mortgage brokers give their customers, YES, they
do use spreadsheets to generate sample amortization tables.

How ignorant are you?!

>THIS ARMY HAS TOO MANY SOLDIERS AND NOT ENOUGH TANKS
>
>Databases = Tanks
>Excel = Soldiers
....

Your metaphor.

Used by someone with an ounce of sense, these would be US tanks against
Iraqi soldiers. Used by you, these would be soviet tanks against
Afghani soldiers. Do you know enough recent history to know the
difference?

Continuing this metaphor, tanks ain't all that useful in jungles,
mountains, amphibious operations, air drops. They're really only useful
in flat, open country without mud or with roads. Soldiers are useful
and needed everywhere.

Talk about rhetorical self-immolation. Do you ever stop to think before
writing this drivel? That begs a broader question: do you ever think?!

aaron.kempf@gmail.com wrote...
>and databases have a LOT more than select, for starters there is
>UPDATE, DELETE, INSERT..

*NOT* for referencing existing data they don't. These queries all
CHANGE tables, only SELECT fetches data from tables.

>the WITH clause (of MDX and CTEs)

Irrelevant - again you're assuming the existence of additional
software. If I assumed a full SAS system interfaced with a SQL
database, could I then claim all SAS data step code was just part of
the database interface? Well, if I were you I guess I could, but
thinking people would have a different opinion.

aaron.kempf@gmail.com wrote...
>select is about 100 times more powerful than spreadsheets.

If you want to select data pretty much as it comes from one table or
several joined tables, perhaps. If you want to correspond values from
some field but from different records, it gets difficult very quickly.

Consider another simple example: 5 point moving averages from a table
in which the first field is observation number and the second field is
observation value. In Excel, real difficult: sort the table on
observation number in ascending order, then starting with the 5th
observation, average the first 5 values up to the current record's (5th
observation's) value, then fill that formula down for the remaining
records.

What sort of gyrations does something this simple require in a SELECT
query? Pure SQL, no assuming additional software like Analysis
Services.

>i mean.. storing data in one place and then pointing to it-- it is a
>lot easier than pulling around all your data and emailling all your
>data to your clients

Unless one's IT department won't provide access to anyone outside the
company, in which case, oh great genius, how does one share data with
customers/clients? Hint: not by an ODBC or web database feed.

>databases make the world go around

Excretion is a necessary part of living . . . and an unavoidable aspect
of your postings.

aaron.kempf@gmail.com wrote...
>pulling stuff from multiple tables is EASY
>that is why they make temp tables, derived tables; views.. i mean..
>COME ON

Pulling things from *MULTIPLE* tables is easy. Pulling related
information from the *SAME* table but from different records is
*DIFFICULT*. If I'm wrong, prove it! Show us how to do it elegantly!

You've suggested how to handle moving averages a while ago. Something
like

SELECT (D1.ObsVal+D2.ObsVal+D3.ObsVal+D4.ObsVal+D5.ObsVal)/5 AS MA5PT
FROM (((D AS D1 INNER JOIN D AS D2 ON D1.ObsNum=D2.ObsNum+1)
INNER JOIN D AS D3 ON D2.ObsNum=D3.ObsNum+1)
INNER JOIN D AS D4 ON D3.ObsNum=D4.ObsNum+1)
INNER JOIN D AS D5 ON D4.ObsNum=D5.ObsNum+1;

Maybe that seems clear to you. If there were column headings in A1:B1
and the first record in A2:B2, the Excel formula to calculate this
would be

C6:
=AVERAGE(B2:B6)

Double click the fill handle after entering this formula, and it's
done.

But the real strength of flexible referencing (a la Excel) comes when
you want to vary the number of points in the moving averages. Enter the
formula

C2:
=IF(ROW()>C$1,AVERAGE(OFFSET(B2,1-C$1,0,C$1,1)),"")

and fill it down in column C. Now enter 5 to get 5-point moving
averages, 3 to get 3 point moving averages, 10 to get 10-point moving
averages, etc. You'd need different SELECT queries for each of these,
and you're more deluded than usual if you believe anyone other than
yourself would find the necessary queries anything other than tedious
and repetitious.

>if i want to provide REPORTS to customers I would use the crappiest
>software ever; Adobe Acrobat. I would send them a copy of the REPORT
>instead of a copy of the data.

That's normal practice. It prevents later disputes or MUCHO work trying
to figure out how they derived numbers from raw data. You're just
putting your own ignorance on display again.

>that is my big beef with Excel; it isn't designed for REPORTING but you
>numnuts use it for reporting all the time

No, *YOU* or people *YOU* work with use it for reporting. Others may
also, but I don't.

>you can't export an Excel sheet into a REPORT file and email it around.
>what happens when you DONT want people to change your numbers?
>what happens when you DONT want to email a 100mb spreadsheet?

You send PDF files! Simple, ain't it?!

>Databases trump spreadsheets any day of the week.

For doing mindnumbingly repetitive stuff, which is all that you're
likely allowed to do, you're right.

>1) sorting and averaging; that stuff is OBVIOUSLY easier in a database.
>For starters; when you change the SORT ORDER it doesn't break your
>other reports. Do you seriously propose having multiple copies of the
>DATA in a spreadsheets; sorted in different order? LOL

Sorting is purely subjective. Some like you may prefer query predicates
like 'ORDER BY FOO ASC', others would prefer specifying columns via
dialogs. Agreed that Excel's limitation of just 3 sort columns is
absurd.

>2) starting with the 5th.. come on-- gag me with a spoon.. select top 5
>as a subquery.. that is easy.

You'd need to repeatedly subquery the top 5. If you have 100 records,
you have 96 5-point moving averages.

>3) instead of COPYING FORMULAS all over the world; you can have ONE
>FORMULA in ONE PLACE. I mean.. what happens when you change the
>formula?

Except as pointed out above, you could have one Excel formula capable
of generating N-point moving averages for any positive integer N (well,
up to 65535). You need separate queries for each N in rdbms's.

>(hint, it is easier to change a formula in one place than in 20,000
>rows on 20 different worksheets).

Hint: if you know how to use spreadsheets well, it takes *ONE* formula
to do what it'd take thousands of different queries to do.

you're wrong.. subqueries and temp tables can do anything like that
that you need to... and it's a LOT more scalable than Excel..

you can write views and sprocs-- using drag and drop.. it is just a lot
more practical than Excel.. you keep all your data in one place; and
you dont have to email 200mb spreadsheets.. you can email around small
reports instead

or.. get this.. URLS
if you put your logic into a database then it is easy to build it as a
webpage.. so it's easy to share it between offices for example

-aaron