Hitting the limits with large table in Excel containing 1 million records. What is the best option for what I do?

[Dave K]

For any of you that happen to also be knowledgeable about databases, I am wondering what is the best option for me, in terms of building and maintaining a relatively simple database, if I am working with about 1 million records (containing about 50 columns)? Am wondering if MySQL or Access is the best option for me...someone who is very familiar with Excel, but not familiar with MySQL or Access.

Basically, I need to constantly do Vlookups on a few tables every day (or on one very large table). I then need to filter down to about 100,000 items based on those 1 million records (currently containing about 50 columns). I then simply need to paste the output of those 100,000 items into CSV files, and then upload them to a few websites.

So doing "Vlookups" regularly and a building a few basic Reports (i.e., filters) are really all I need with this table (it is simply a list of Books with information such as ISBN, Title, and Author Names etc).

Right now I just have one large flat table, which has actually worked well when the file was around 300,000 rows. But I need to expand it, to 1 millionish rows, so I am hitting the limits that Excel can handle. The current size of the main table (in Excel) I am using is about 220 MB and it is likely to reach around 300MB.

What is generally the best option to migrate to, in this situation? Can PowerPivot meet this need? Or does the new Relational Database option in Excel 2013 possibly solve this? Or Should I use Access? Or the MySQL community datbase? Any suggestions/perspective would be greatly appreciated.

[John W. Vinson]

Post by Dave K
For any of you that happen to also be knowledgeable about databases, I am wondering what is the best option for me, in terms of building and maintaining a relatively simple database, if I am working with about 1 million records (containing about 50 columns)? Am wondering if MySQL or Access is the best option for me...someone who is very familiar with Excel, but not familiar with MySQL or Access.
Basically, I need to constantly do Vlookups on a few tables every day (or on one very large table). I then need to filter down to about 100,000 items based on those 1 million records (currently containing about 50 columns). I then simply need to paste the output of those 100,000 items into CSV files, and then upload them to a few websites.
So doing "Vlookups" regularly and a building a few basic Reports (i.e., filters) are really all I need with this table (it is simply a list of Books with information such as ISBN, Title, and Author Names etc).
Right now I just have one large flat table, which has actually worked well when the file was around 300,000 rows. But I need to expand it, to 1 millionish rows, so I am hitting the limits that Excel can handle. The current size of the main table (in Excel) I am using is about 220 MB and it is likely to reach around 300MB.
What is generally the best option to migrate to, in this situation? Can PowerPivot meet this need? Or does the new Relational Database option in Excel 2013 possibly solve this? Or Should I use Access? Or the MySQL community datbase? Any suggestions/perspective would be greatly appreciated.

I would do this in a relational database; I'm most familiar with Access but
MySQL, SQL/Server Express or others could also do this task very simply.
However, a spreadsheet is NOT a database, and a database is NOT a spreadsheet;
they are different, and require a different approach and logic. Instead of
VLookUp you would use one or probably several related tables (e.g. a table of
Authors, a table of Publishers, a table of Publications with appropriate
relationships). In Access you could very easily create Queries joining the
tables to select the fields and rows needed for your export, and use either
the builtin External Data... Export facility, or VBA code using the
TransferText method to export text files.

Bear in mind that relational databases ARE DIFFERENT; expertise in Excel can
be valuable but can also mislead you into "committing spreadsheet" and
designing less-than-optimal databases! You may have to "unlearn" some things
(such as VLookUp) and learn new ways of doing tasks. To get you started, if
you're interested, here's some useful links:

Utter Access discussion forum and resources:
http://www.utteraccess.com

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Do note that this Usenet newsgroup (microsoft.public.access) is no longer
hosted or supported by Microsoft. My .sig lists some newer forums that get a
lot more traffic and may be better places than this to get support.

[dan.ashley@abc com]

Post by Dave K
For any of you that happen to also be knowledgeable about databases, I am wondering what is the best option for me, in terms of building and maintaining a relatively simple database, if I am working with about 1 million records (containing about 50 columns)? Am wondering if MySQL or Access is the best option for me...someone who is very familiar with Excel, but not familiar with MySQL or Access.
Basically, I need to constantly do Vlookups on a few tables every day (or on one very large table). I then need to filter down to about 100,000 items based on those 1 million records (currently containing about 50 columns). I then simply need to paste the output of those 100,000 items into CSV files, and then upload them to a few websites.
So doing "Vlookups" regularly and a building a few basic Reports (i.e., filters) are really all I need with this table (it is simply a list of Books with information such as ISBN, Title, and Author Names etc).
Right now I just have one large flat table, which has actually worked well when the file was around 300,000 rows. But I need to expand it, to 1 millionish rows, so I am hitting the limits that Excel can handle. The current size of the main table (in Excel) I am using is about 220 MB and it is likely to reach around 300MB.
What is generally the best option to migrate to, in this situation? Can PowerPivot meet this need? Or does the new Relational Database option in Excel 2013 possibly solve this? Or Should I use Access? Or the MySQL community datbase? Any suggestions/perspective would be greatly appreciated.