Checking an external list for duplicates in MySQL table?

Question

I have a MySQL table and an Excel file. I would like to update the MySQL table to include the information from the Excel file, which is simple -- just write a quick formula for each line that creates the appropriate SQL statement.

But first I'd like to check that all of the data matches properly. So if a given line has WidgetID 1001, I'd like to check if that WidgetID is in the MySQL table. For a given line, that's simple -- just generate a SQL statement

SELECT COUNT(*) FROM mytable WHERE WidgetID = "1001"

and execute it. But how can I check all lines without pasting in the commands one at a time? Essentially, I want something like

If (SELECT COUNT(*) FROM mytable WHERE WidgetID = "1001") = 0 Display "1001\n"

but I'm not sure how to do this. (I don't use SQL much!)

Clarification: I do not want to import the data at this time! That will be a complicated task because the data need to be transformed and entered into a dozen different tables in various ways. Right now I just want to check for non-duplicates (which will be almost entirely errors, I imagine, though some perhaps will be actual new entries).

Answer 1

1. Export Excel to CSV
2. Make sure there is a UNIQUE index created on WidgetID column
3. Use LOAD DATA INFILE with REPLACE or IGNORE option (depending if you want to replace duplicates with new values, or leave the previous values)

Answer 2

You can query both MySQL and Excel at the same time, this is an INSERT, but any query will work:

http://stackoverflow.com/questions/2821718/excel-vba-writing-to-mysql-database/2821911#2821911

Here is another example with SQL Server:

http://stackoverflow.com/questions/3767879/insert-data-from-excel-into-sql-db/3768327#3768327

You can get both OLE DB & ODBC connection strings from: http://www.carlprothman.net/Default.aspx?tabid=81