How to write SQL Query that matches data from a .csv file to a table in MySQL?

Question

Is it possible for me to write an SQL query from within PhpMyAdmin that will search for matching records from a .csv file and match them to a table in MySQL?

Basically I want to do a WHERE IN query, but I want the WHERE IN to check records in a .csv file on my local machine, not a column in the database.

Can I do this?

Answer 1

Short answer: no, you can't.

Long answer: you'll need to build a query locally, maybe with a script (Python/PHP) or just uploading the CSV in a table and doing a JOIN query (or just the WHERE x IN(SELECT y FROM mytmmpTABLE...))

Answer 2

I'd load the .csv content into a new table, do the comparison/merge and drop the table again. Loading .csv files into mysql tables is easy:

LOAD DATA INFILE 'path/to/industries.csv' 
INTO TABLE `industries` 
FIELDS TERMINATED BY ';' 
IGNORE 1 LINES (`nogaCode`, `title`);

There are a lot more things you can tell the LOAD command, like what char wraps the entries, etc.

Answer 3

I would do the following:

1. Create a temporary or MEMORY table on the server
2. Copy the CSV file to the server
3. Use the LOAD DATA INFILE command
4. Run your comparison

There is no way to have the CSV file on the client and the table on the server and be able to compare the contents of both using only SQL.