Hitting the limits with Excel and need about 1 million rows of data.What is the best option for what


M

Mike S

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)?

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.
 
Ad

Advertisements

G

GS

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)?

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.
How about using a plain text file and manage it via ADODB or normal VBA
I/O functions?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Top