Update fields in SQL database with data from CSV file
First, I apologize if this has been answered. I've seen many similar questions but not one that answered mine in full.
First off, I am a SQL novice at best. I've learned quite a bit since setting up my online store, but I'm still nowhere near knowledgeable on much of any of it.
Okay, I have a database on my web server for my online store. I sell things that can also be drop-shipped. I can download an updated inventory file in CSV format from my distributor every few minutes. This file contains two columns that do not have field names in the first row. The table I want to update is zen_products. The first column in the CSV file corresponds to column products_model and is unique. The second column in the CSV file corresponds to column products_quantity. There will be records in the CSV file that do not exist in the SQL table. I do not want to create new records; I only want to update the products_quantity column with the value from the CSV file.
Okay, so as it stands right now I can do this with an add-on through the admin section of my shopping cart software but I have to edit the CSV file to input specific field names. This isn't a problem as it is very easy, but it does take a few minutes to edit the file, log in, upload, and process it. It's actually quite simple; however I would like to find a way to completely automate this process so I can have this done on its own every 15-30 minutes if possible. As you can imaging, something that takes 5 minutes is fine, but if it takes 5 minutes every 15 minutes that's a lot of time throughout the day. And it would be nice to be able to allow it to happen even while I am busy or away.
If necessary, I am also willing to pay a reasonable amount for a working, completely automated solution. Also, the CSV file is only accessible via FTP. And FYI I use Zen Cart as my shopping cart.
Any help would be greatly appreciated. Thanks.
Update: based on the help from JKirchartz (thank you and you rock by the way), I have this which appears to work every time I run it:
DELETE FROM zen_temp;
load data local infile '/home/somedude/public_html/somefolder/somefile.csv' into table zen_temp
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(products_model, products_quantity);
UPDATE zen_products SET `products_quantity` = ( SELECT products_quantity FROM zen_temp WHERE zen_products.products_model = zen_temp.products_model )
WHERE EXISTS ( SELECT products_model FROM zen_temp WHERE zen_products.products_model = zen_temp.products_model );
Now my question is, how can I turn this into something that I can easily do with very little input? Is there a way I can schedule it or run it from anywhere easily? Thanks again.
Answer
You can import CSV's directly with:
load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)
to update, you'd have to LOAD DATA LOCAL INFILE into another table, then use a stored procedure or query to INSERT and UPDATE (as opposed to REPLACE) your desired table