2

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.

ЯegDwight
  • 24,821
  • 10
  • 45
  • 52
user1678042
  • 45
  • 1
  • 2
  • 6
  • Which DBMS are you using? PostgreSQL? Oracle? DB2? –  Sep 17 '12 at 22:07
  • Don't know what that means for certain but I think the answer to your question is phpmyadmin – user1678042 Sep 17 '12 at 22:12
  • phpmyadmin is a frontend tool, not a DBMS. And it's a tool used for MySQL –  Sep 17 '12 at 22:15
  • 1
    new user tip: You don't need to apologise that you ask your question. Just mark you consider yourself a newbie with the subject and this will do, it's nothing to be shamed of, and you don't need to mention it all the time. This is questions/answers portal, you know :) – Piotr Wadas Sep 18 '12 at 00:13

1 Answers1

1

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

JKirchartz
  • 17,612
  • 7
  • 60
  • 88
  • Ah, I see now about the loading into another table first. I'm guessing you saw my comment before I deleted it (still new here, sorry). So, can this all be done at one time? I can make sure the csv file is always up-to-date via my auto ftp program. Is there some way in one swoop that I can have it create the new table, then update the old table, then maybe delete the records from the "new" table so it's good to go for the next update? – user1678042 Sep 17 '12 at 20:42
  • Thank you so much for your help. I got it working, now I just need a way to automate it. – user1678042 Sep 17 '12 at 22:11