HELP!!!

[Anonymous]

Is there any way to get 68,000 lines of data into an Excel spreadsheet? It's only allowing me a little over 65,000.

[cubedbee]

You're out of luck. Excel can only accomodate 65,536 rows. At least this is true of Excel 97.

[glenn]

Nope. 65536 if I had to guess.

You may have better success at www.openoffice.org. Their spreadsheet works remarkably like Excel and is mostly compatible. You can just download and install it (it's free). Cho-da/Obi-wan or some digging at the openoffice site may tell you whether openoffice has the 65000 line problem, though I would guess that it doesn't.

[TwistedMentat]

Why do you want to put that much data into Excel. You may be better off using a small-scale database like MSAccess.

[Dr T Non-Fan]

TM's right. There's no reason for that much data.

Choices:

1. Parse it by historical marks, or state, or more-specific products.
2. Use less history.
3. Use MS Access to summarize further, before heading to Excel.
4. Ask whoever gave you the data to summarize it in the step before it gets to you. If that's you, then same applies.
5. Cut out a too-varied field. Or summarize field value possibilities to only those needed for the task at hand.
6. Generalize insignificant field values as "other" if complete data for reconciliation are requred.

[glenn]

The reason to use Excel or knockoffs instead of MS Access is that there is no learning curve to do most of the data manipulation they're going to want to do. If they want to do function XYZ, it may take three times as long in Excel and be kludgy but at least they know how to do it.

With computers doing all the work, who really cares if it takes 5 minutes instead of 3 to kick-start a big calc, if you've saved two days of not having to figure out Access. I've used Excel for grossly large amounts of data myself, for exactly this reason.

<aside - if that is the reason you want to do this in excel, then definitely download the openoffice stuff and see if it will handle it. It's close enough to Excel that you can jump right into it. Some of the functions have mildly different structures, but help or the equation builder makes short work of that problem. FWIW, I had some larger spreadsheets run faster in openoffice than Excel.).
<further warning - if you're importing text into the spreadsheet, make sure the file type you choose is text-CSV, not just text. Otherwise it will import the data into the word processor).

[Dr T Non-Fan]

If I put my ideas in order of preference, using MS-Access would be last. Especially if I didn't know how to format properly the data to be imported into MS-Access. (I do, and I still don't like to do it.)

Yo, glenn: regarding your latest pitch, are you making it up in volume? Just wondering.

[Cho Da]

OpenOffice (http://OpenOffice.org) spread sheets only allow 32000 rows. Even Excel is unweildy at that size. Spreadheets that ¿require? that many row really do belong in a small database.

[StephenLL]

I believe in excel you can use pivot tables to link to an external datasource (ie text file).

The pivot table will read in the data into the excel file (not in a particular spreadsheet) and then you can perform calculations on it as a pivot table.

- stephen

[Anonymous]

Thanks for the advice. I'll do it in Access.