validating excel data against a mysql db?

[spyrule]

Hello,

I have a constant struggle to validate large amounts of data (specifically phone numbers), against quite a few comparison variables.

So far I have a "scrub" template. I have approximately 10 worksheets, each with very different reasons for seperation, but all of them consisting of the phone number or valid npanxx (area code-exchange). The issue that I am having is that once I do my initial formatting on the raw data, and then copy and paste my formulas the excel sheet eventually times out (too many comparisons I think, taking too much memory).

So I was thinking of off-loading the work to a server, or at least reducing the required "in memory" foot print so that the time-outs stop happening.

I was thinking of loading all the comparison data into a mysql Db (I have one running on a fast access server), and then running an excel macro that compares all the raw data against the comparison data in the Db (usually two specific columns of data, usually 40k rows or more).

However, I'm not even sure if this is possible?

Is it possible to :

1) row by row, compare the value of a specific cell, against multiple tables in
a mysql database, and if so, "mark" another cell with a specified indicator?

Would this be more effecient to use a mysql db? or an access Db (which I
know nothing of how to use.. lol)

Any help, suggestions, comments, sample code would be greatly apreciated.

Spyrule.

[spyrule]

bump..

Anybody with any ideas here?

[excelsolution]

how many rows and columns do you have in your database?

[spyrule]

I currently have 10 worksheets, each with several columns, although I only use one column as the comparison, and each worksheet, has anywhere up to a full 65535 rows of data.
one, actually has 3 columns for a total reference of 140k+ comparisons.

so, approximately... 1/2 to 3/4 of a million rows of data. Now the data is seperated into 2 types of data.

full phone numbers, and npa-nxx (areacode-exchange).

I would build something like this in access, but honestly... I know NOTHING about access (except that its a relational Db).

[excelsolution]

Ok.
I suggest you to use Ms.Access for this. If yo want to use mysql and other database programs (mysql,sql,firebird,db2..) you must use database server. But access dont need extra server.
And I have an access file which have more than 15 columns and about 1.600.000 rows.

if you need help about Access, I can try help you

[spyrule]

Hello,

I actually have a mysql server (it's part of a full server package), and I have full access to the server (I am the admin).

The problem with Access for me, is that I have literally NO experience with it, and I would like to get this tool working sooner then later, and if I did it in access, it would take me weeks just to get to a point where I could START to do what I need to do.

[excelsolution]

ok. I again suggest to use Ms.Access.