Excel to MySQL export - every week updates and constraint problems

Question

Hi guys,

Every monday end user opens Excel 'prices.csv' and does insert/update/delete on products and their new fresh prices.

After that he performs export of 'prices.csv' to MySQL DB into table called PRODUCTS. I've seen here many solution using CSV or using commercial solutions for this.

I understand that this can be easily done first time, when initial creation od table PRODUCTS is done.

Next monday end user wants to export new fresh prices list into PRODUCTS table from last week. But what about insert/update/delete and referential integrity now ???

Question 1: Is this same logic that has to be done when performing 3 steps "delta tables" (delete, modify annd add in old_table from new_table with same DDL)

Question 2: Is there a workaround for importing data without violating the constraints?

Question 3: Can this be done with some external tool, so NO programming (no PHP no stored procedures...) This is most important here !

Question 4: How smart is this because end user can make a mess in Excel so export in MySQL can be really painful?

Thank you all very much for your help in advance.

Answer 1

If you don't need to keep the old prices: why not just empty the PRODUCTS table (either delete all of the records or drop the table and recreate it) and then fill it with the updated list of products and prices?