?
Solved

More than 65536 Lines in Excel 2000?

Posted on 2003-03-19
13
Medium Priority
?
456 Views
Last Modified: 2012-05-05
Hi,

I use Excel 2000 to manage detailed data requests from our database of 120,000 records.  The problem ive encountered is that a lot of the CSV files i get from our database exceed 65536 lines of information, meaning its getting more and more hard to be able to run queries and data requests and be able to manipulate that data in excel 2000.  Therefore I was wondering whether there is a similar microsoft product which could handle more than 65536 lines of data as i would prefer to stay with microsoft as our lengthy procedures are tailored to Excel and rely on processes such as 'sort', 'filter', subtotal etc.....

So i guess what im saying is that im sure other spreadsheets probably have similar functions but im not sure my boss will let the procedures change as she relies on Excel a lot too and i don't think would want to change- therefore i will have to split my reports over mulitple workbooks and merge later- which as you can guess is very messy and far from ideal given the complexty of some of the requests i get and the timespan i often have.

Ive heard that there is something that exists in Web Tools in Windows 2000 that is essentially a spreadsheet that can handle many more lines of data but between me and one of our tech guys have not be able to locate this tool, which i was told is simply called 'spreadsheet'.

Can anybody help?

Greg

0
Comment
Question by:Dubs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 1

Expert Comment

by:thedevilinthebelfry
ID: 8167730
MS Access - esceeds the 65536 record barrier
0
 
LVL 1

Expert Comment

by:thedevilinthebelfry
ID: 8167741
And you can do most(all?) of the things you do in excel with Access.
0
 
LVL 1

Expert Comment

by:thedevilinthebelfry
ID: 8167787
Also - depending on what this other database is - you may be able to link to the tables in it with Access - not necessarily a solution depending on what you want to do (or not do, more precisely)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:ElectroNitz
ID: 8168135
What about using Excel's "External Data" or ODBC to query your database directly instead of using CSV files? As long as the end result of your SQL query is <65536 rows, it will work. If you must use CSV files, and must use Excel, what you can do is link the CSV files to an MS Access Database, and then use Excel to query the Access Database
0
 
LVL 1

Expert Comment

by:thedevilinthebelfry
ID: 8168706
ElectroNitz is correct - both ways - as long as your queries stay within the limits of Excel.

I misread your problem... thought it was your queries that were exceeding the limits.
0
 

Author Comment

by:Dubs
ID: 8172405
Thanks for the reply's- the problem is that often my queries will come out over 65536 rows- as we have at least 90,000 live records on your database which is growing everyday and some of the data requests are large and complex.  Although ive used Access i would prefer not to for this as I would like to just keep using two bits of software, our database has a direct link into Excel and this makes the processes a little more stream-lined, bringing Access into the equation will cause extra training and a change in the way we work- which we would like to avoid as we are always against the clock.

Does anybody know about this 'Spreadsheet' found in the web tools of Windows 2000?

cheers

Greg
0
 
LVL 1

Expert Comment

by:thedevilinthebelfry
ID: 8173406
>>'Spreadsheet' found in the web tools of Windows 2000?

Never heard of it - does not mean it does not exist - though... maybe somebody else. Plus if it does exhist, it probably does not have all of the same abilities as Excel - at least if it is put out by MS. Why would they "give away" a product that is superior (same functions more records) to their commercial product?.

If your queries are consistently going over the limit though, I still stand by MS Access ... if you wish to stay within the MS line of products.

>>database has a direct link into Excel

You can do that with Access as well; and, IMHO, I also feel that Access gives the user more functionality... and I don't think that your average user would need too much training to perform queries or manipulate them (sorting, filtering, subtotalling).
0
 
LVL 4

Expert Comment

by:ElectroNitz
ID: 8173885
I also could not find anything on "Webtools Spreadsheet".

I know you stated that you don't want to get away from MS, but I saw through a google news search that Quatro Pro limit is 1,000,000 rows, and Lotus 1-2-3 limit is 400,000.

If it helps, there are two ways to  automatically create multiple sheet workbooks.

XL: Importing Text Files Larger Than 16384 Rows (also works for 65536 by changing one number)
http://support.microsoft.com/default.aspx?scid=kb;en-us;120596

If you were using XL 2002, you could use the XML file format instead, and it can create multiple sheets.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;285891

0
 

Author Comment

by:Dubs
ID: 8173919
Thanks for your replies-

I have been looking at Quatro Pro 9 as it looks v good- with a million lines limit i can't see myself exceeding that.  Problem its part of the office package with no option to try before you buy...

Ill check out those links and let you know how i get on.

Regards

Greg
0
 

Author Comment

by:Dubs
ID: 8174620
I have been looking at other packages since im not au fait enough to try the macro option and think that if im gonna buy Excel 2002 i might as well spend the money on a superior spreadsheet provided they do similar things.  Quattro Pro looks like the champ but having no way to trial these applications im dubious to take the plunge and buy it outright.

So i have two options- buy 2002 and work out how to use xml, this isn't ideal as i will still be working with multiple sheets, or take the plunge with either quattro or lotus, unfortunatly on each respective site i can't find any free trials.

I must say Microsoft have dissapointed me here as the new excel looks ideal, i just can't believe they haven't made it bigger.

Cheers

Greg

0
 
LVL 1

Assisted Solution

by:thedevilinthebelfry
thedevilinthebelfry earned 90 total points
ID: 8175153
>>Microsoft have dissapointed me here

they do that sometimes...

since you are now leaning away from MS now - I would go with Quatro - like ElectroNitz said...

Good luck
0
 
LVL 4

Accepted Solution

by:
ElectroNitz earned 135 total points
ID: 8175865
Downloads - WordPerfect Office 2002 Trial Version

The trial version of WordPerfect Office 2002 installs the following core applications:

WordPerfect® 10
Quattro® Pro 10
Corel® Presentations™ 10
CorelCENTRAL™ 10
Paradox® 10

http://www.corel.com/servlet/Satellite?pagename=Corel/Downloads/Details&id=1047021685911

0
 
LVL 15

Expert Comment

by:dbase118
ID: 10062806
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Points split between ElectroNitz and thedevilinthebelfry
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

dbase118
EE Cleanup Volunteer
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question