Hitting the limits with large table in Excel containing 1 millionrecords. What is the best option fo

Discussion in 'Access General' started by Dave K, Apr 27, 2014.

  1. Dave K

    Dave K Guest

    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). Ithen 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 likelyto 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 Excel2013 possibly solve this? Or Should I use Access? Or the MySQL community datbase? Any suggestions/perspective would be greatly appreciated.
    Dave K, Apr 27, 2014
    1. Advertisements

  2. 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:

    Jeff Conrad's resources page:

    The Access Web resources page:

    Roger Carlson's tutorials, samples and tips:

    A free tutorial written by Crystal:

    A video how-to series by Crystal:

    MVP Allen Browne's 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.

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:

    and see also http://www.utteraccess.com
    John W. Vinson, Apr 28, 2014
    1. Advertisements

    dan.ashley@abc com, Apr 29, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.